Query Execution Order
Most people would write their SQL queries starting from
SELECT part, because it’s more intuitive and close to our natural language. But actually that’s not the way that SQL queries been executed in query engine.
Below is the execution order of a SQL query:
JOIN. Tables are joined to get the base data.
WHERE. The base data is filtered.
GROUP BY. The filtered based data is grouped.
HAVING. The grouped base data is filtered.
SELECT. The final data is returned.
DISTINCT. The final data is duplicated.
ORDER BY. The final data is sorted.
OFFSET. The final data is limited to row count.
Image your are the Query Engine component for a database now, here are the phrases of how you will interpret SQL Query:
- Phrase 1 - Row Filtering I. This will be done via
- Phrase 2 - Column Filtering. This will be done from
- Phrase 3 - Row Filtering II. This will be done by
- Xinran Waibel: Crack SQL Interviews
- StackOverflow: How does a SQL Query Work
- Scaler: SQL Query Execution Order
A self join is a regular join operation, but the table is joined with itself. Sometimes, all your needed information are stored in one table already, and you just need to do self-join.
For instance, below is a section from the Customers table,
|1||Alfreds Futterkiste||Maria Anders||Obere Str. 57||Berlin||12209||Germany|
|2||Ana Trujillo Emparedados y helados||Ana Trujillo||Avda. de la Constitución 2222||México D.F.||05021||Mexico|
|3||Antonio Moreno Taquería||Antonio Moreno||Mataderos 2312||México D.F.||05023||Mexico|
Your task is to find customers who are from the same city.
Here is a sample solution