Advanced SQL Concepts

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:

1. FROM, JOIN. Tables are joined to get the base data.
2. WHERE. The base data is filtered.
3. GROUP BY. The filtered based data is grouped.
4. HAVING. The grouped base data is filtered.
5. SELECT. The final data is returned.
6. DISTINCT. The final data is duplicated.
7. ORDER BY. The final data is sorted.
8. LIMIT, 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:

1. Phrase 1 - Row Filtering I. This will be done via FROM, WHERE, GROUP BY, HAVING clause.
2. Phrase 2 - Column Filtering. This will be done from SELECT clause.
3. Phrase 3 - Row Filtering II. This will be done by DISTINCT, ORDER BY, LIMIT, OFFSET clause.

Reference

• Xinran Waibel: Crack SQL Interviews
• StackOverflow: How does a SQL Query Work
• Scaler: SQL Query Execution Order

Query Optimisation

Reference

• Wikipedia: Query Optimisation
• Anoop Kumar: Understanding a SQL Server Query Execution Plan

Query Processing

Reference

• Microsoft: Query Processing Architecture Guide
• Oracle: SQL Processing

Rank

Rolling Total

Self-Join

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,

Your task is to find customers who are from the same city.

Here is a sample solution

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
  c1.CustomerID as c1_id,
  c1.CustomerName as c1_name,
  c2.CustomerID as c2_id,
  c2.CustomerName as c2_name,
  c1.City
FROM Customers c1, Customers c2
WHERE 1=1
  AND c1.CustomerID <> c2.CustomerID
  AND c1.City = c2.City
ORDER BY c1.City
;

Reference

• Gian Panacioni: 7 Advanced SQL Concepts You Need to Know!