Other than data sent over the wire, from a performance perspective, does using a WHERE clause in SQL query improve the performance or it makes no difference.
Curious on how database works internally and whether WHERE clause is just a scan + filter operation? Or is there a way to improve WHERE clause performance?
In the comments you are referred to "Order Of Execution of the SQL query" and you are well advised to read the excellent answers to that question. In one of those it states:
The optimizer is free to choose any order it feels appropriate to produce the best execution time.
Keep this true statement in mind, because we tend to think that WHERE follows FROM because that's how we write out the query; but WHERE can be coupled (or perhaps better described as "moved around" a bit) in the early stages of the query execution.
e,g. we might write a query like this:
SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2023-01-01';
but the actual execution might folow something more like this:
FROM (SELECT * FROM orders WHERE order_date >= '2023-01-01') o
JOIN customers c ON o.customer_id = c.customer_id;
SELECT o.order_id, o.order_date, c.customer_name
because filtering the orders table before joining it processes a smaller set of data which could lead to improved query performance.
This isn't a trivial topic, and this tiny example is wholly inadequate to describe the inner complexities of how SQL queries are executed. What I would simply like to impart is that, yes, WHERE predicates filter the data, but don't get into the mindset that assumes WHERE sequentially follows FROM because it does not have to be.
Similarly, because WHERE does filtering, and is an intimate partner of the FROM clause, it does have a massive influence on query performance.
