I am confused about the execution order of SQL queries.
For example, (Inner join
in MySQL
in the code below), between WHERE
clause and SELECT * FROM
clause, which one gets to be interpreted and executed first?
That is to say, does the query below bring *
(all) of the tables data
first then find the cases that match with WHERE
condition? or Do they just find the list of data
that match with WHERE
condition and then SELECT * FROM
from the WHERE
result?
SELECT * FROM customers, orders
WHERE customers.id = orders.customer_id;
As above case, I am wondering how the SQL queries are executed in general.
There is a logical order to evaluation of the query text, but the database engine can choose what order execute the query components based upon what is most optimal. The logical text parsing ordering is listed below. That is, for example, why you can't use an alias from SELECT
clause in a WHERE
clause. As far as the query parsing process is concerned, the alias doesn't exist yet.
FROM
ON
OUTER
WHERE
GROUP BY
CUBE | ROLLUP (these are not present in MySQL but are in some other SQL dialects)
HAVING
OVER (window functions)
SELECT
DISTINCT
ORDER BY
LIMIT (or, in MSSQL, TOP)
See the Microsoft documentation (see "Logical Processing Order of the SELECT statement") for more information on this.