mysqlsqlinner-join

Is there any specific order of execution in SQL query?


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.


Solution

  • 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.

    1. FROM

    2. ON

    3. OUTER

    4. WHERE

    5. GROUP BY

    6. CUBE | ROLLUP (these are not present in MySQL but are in some other SQL dialects)

    7. HAVING

    8. OVER (window functions)

    9. SELECT

    10. DISTINCT

    11. ORDER BY

    12. LIMIT (or, in MSSQL, TOP)

    See the Microsoft documentation (see "Logical Processing Order of the SELECT statement") for more information on this.