Master SQL Execution Order, Joins, CTEs & Window Functions

Introduction

For backend developers, data engineers, and AI/ML practitioners, SQL remains a fundamental skill. This article presents a detailed overview of SQL, from basic concepts to advanced topics.

Topics Covered

SQL query execution order

Core commands and clauses

SQL join operations

Common Table Expressions (CTEs)

Recursive CTEs

Window functions

Table scans, indexes, and query optimization

2.1 SQL Query Execution Order

Although SQL statements are written top‑to‑bottom, the database processes them in a logical order. Understanding this separation is crucial for controlling WHERE filters, GROUP BY grouping, and sub‑query evaluation timing.

FROM / JOIN : Determine data sources and table joins.

WHERE : Row‑level filtering.

GROUP BY : Group rows by specified columns.

HAVING : Filter grouped results.

SELECT : Extract columns and compute expressions.

ORDER BY : Sort results; aliases from SELECT can be used.

LIMIT / OFFSET : Restrict the final result set.

2.2 Core Commands and Clauses

Basic SELECT, FROM, and WHERE syntax: SELECT name, age FROM t_user WHERE age > 21; ORDER BY and LIMIT example:

SELECT * FROM sales ORDER BY revenue DESC LIMIT 5;

GROUP BY and HAVING example:

SELECT department, COUNT(*) AS total FROM employees GROUP BY department HAVING COUNT(*) > 10;

2.3 SQL Join Operations

INNER JOIN returns rows that match in both tables.

SELECT x.name, y.amount FROM t_user x INNER JOIN t_order y ON x.id = y.user_id;

LEFT JOIN returns all rows from the left table and matching rows from the right table; non‑matching rows are filled with NULL.

SELECT x.name, y.amount FROM t_user x LEFT JOIN t_order y ON x.id = y.user_id;

RIGHT JOIN returns all rows from the right table and matching rows from the left table.

SELECT x.name, y.amount FROM t_user x RIGHT JOIN t_order y ON x.id = y.user_id;

FULL OUTER JOIN returns all rows from both tables, filling missing matches with NULL.

SELECT x.name, y.amount FROM t_user x FULL OUTER JOIN t_order y ON x.id = y.user_id;

2.4 Common Table Expressions (CTEs)

CTEs are temporary named result sets defined with a WITH clause, improving readability and maintainability of complex queries.

-- Define CTE: calculate total spend and order count per user
WITH user_order_stats AS (
  SELECT u.id AS user_id,
         u.name AS user_name,
         COUNT(o.id) AS order_count,
         SUM(o.amount) AS total_spent
  FROM users u
  LEFT JOIN orders o ON u.id = o.user_id
  GROUP BY u.id, u.name
),
high_value_users AS (
  SELECT user_id, user_name, total_spent, order_count
  FROM user_order_stats
  WHERE total_spent > 1000 OR order_count > 5
)
SELECT user_id, user_name, total_spent, order_count
FROM high_value_users;

2.5 Recursive CTEs

Recursive CTEs enable querying hierarchical or tree‑structured data.

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN org_chart o ON e.manager_id = o.id
)
SELECT * FROM org_chart;

2.6 Window Functions

Window functions perform calculations across a set of rows related to the current row without collapsing the result set, useful for ranking, cumulative sums, moving averages, etc.

SELECT name,
       department,
       salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

Common window functions include:

ROW_NUMBER(): assign a unique sequential number to each row.

RANK(): rank rows with gaps for ties.

DENSE_RANK(): rank rows without gaps.

LAG() / LEAD(): access preceding or following row values.

SUM() OVER() / AVG() OVER(): compute cumulative or average values within the window.

2.7 Table Scans, Indexes, and Query Optimization

Table Scan : Full table scan occurs when no suitable index exists, leading to lower performance.

Index Scan : Uses an index to quickly locate rows, significantly improving query speed. CREATE INDEX idx_users_age ON users(age); Use EXPLAIN or EXPLAIN ANALYZE to view the execution plan and identify whether the optimizer chooses an index scan or a table scan.

EXPLAIN SELECT * FROM users WHERE age > 30;