SELECT statement execution order

-- Written order:           Execution order:
-- SELECT                   1. FROM + JOINs
-- FROM                     2. WHERE
-- WHERE                    3. GROUP BY
-- GROUP BY                 4. HAVING
-- HAVING                   5. SELECT
-- ORDER BY                 6. DISTINCT
-- LIMIT                    7. ORDER BY
--                          8. LIMIT/OFFSET

-- Full example
SELECT
    d.DeptName,
    COUNT(s.StudentID) AS StudentCount,
    AVG(s.GPA)         AS AvgGPA,
    MAX(s.GPA)         AS TopGPA
FROM Student s
INNER JOIN Department d ON s.DeptID = d.DeptID
WHERE s.Status = 'Active'
  AND s.EnrollDate > '2022-01-01'
GROUP BY d.DeptName
HAVING AVG(s.GPA) > 3.0
ORDER BY AvgGPA DESC
LIMIT 5;

JOIN types — all six with examples

-- Student(StudentID, Name, DeptID)  Department(DeptID, DeptName)
-- Some students have DeptID=NULL, some departments have no students

-- INNER: only students WITH a department
SELECT s.Name, d.DeptName
FROM Student s INNER JOIN Department d ON s.DeptID = d.DeptID;

-- LEFT: all students, NULL DeptName if no department
SELECT s.Name, d.DeptName
FROM Student s LEFT JOIN Department d ON s.DeptID = d.DeptID;

-- CROSS: every student-department combination
-- 5 students × 3 departments = 15 rows
SELECT s.Name, d.DeptName FROM Student s CROSS JOIN Department d;

-- SELF: find students in the same department
SELECT s1.Name AS S1, s2.Name AS S2, d.DeptName
FROM Student s1
JOIN Student s2 ON s1.DeptID = s2.DeptID AND s1.StudentID < s2.StudentID
JOIN Department d ON s1.DeptID = d.DeptID;

The ON vs WHERE trap that silently converts your LEFT JOIN

This is the highest-frequency SQL bug in production reporting, and it produces no error — just wrong numbers. With an INNER JOIN, a filter in ON and the same filter in WHERE give identical results. With a LEFT JOIN they do not, because ON is applied while matching, but WHERE is applied after the NULL-padded rows already exist:

-- GOAL: every customer, plus their 2026 orders (customers with none
-- should still appear, with NULLs).

-- ✅ CORRECT — filter lives in ON, applied during matching
SELECT c.Name, o.OrderID
FROM Customer c
LEFT JOIN Orders o
       ON o.CustomerID = c.CustomerID
      AND o.Year = 2026;
-- Customers with no 2026 orders → one row, OrderID = NULL. All customers kept.

-- ❌ WRONG — filter in WHERE runs AFTER the join
SELECT c.Name, o.OrderID
FROM Customer c
LEFT JOIN Orders o ON o.CustomerID = c.CustomerID
WHERE o.Year = 2026;
-- The NULL-padded rows have o.Year = NULL.
-- NULL = 2026 evaluates to UNKNOWN → row filtered out.
-- Result: the LEFT JOIN silently behaves as an INNER JOIN.

-- ✅ The one legitimate WHERE-on-right-table pattern: anti-join
SELECT c.Name
FROM Customer c
LEFT JOIN Orders o ON o.CustomerID = c.CustomerID
WHERE o.CustomerID IS NULL;   -- customers with NO orders at all

The rule in one line: In a LEFT JOIN, conditions about the right table belong in ON; conditions about the left table belong in WHERE. The only exception is the deliberate anti-join (WHERE right.key IS NULL), which uses the outer-join-then-filter behavior on purpose to find non-matching rows. If a LEFT JOIN report is mysteriously missing rows, check the WHERE clause first — this is almost always the cause.

Join result sizes and the accidental fan-out

A join does not just filter — it can multiply. If the join key is not unique on the right side, each left row is duplicated once per match. This "fan-out" is why totals suddenly inflate after someone adds a join to a working report:

Concretely: joining Orders (1,000 rows) to OrderItems (5,000 rows, ~5 per order) yields 5,000 rows — so SUM(o.OrderTotal) now counts each order five times. The fix is to aggregate before joining (join to a pre-aggregated subquery), or to sum a de-duplicated expression. Whenever a number inflates after adding a join, suspect fan-out before suspecting the data.

How joins are actually executed: The optimizer picks one of three physical strategies, and knowing them explains most performance surprises. Nested loop: for each outer row, probe the inner table — great when one side is tiny and the join key is indexed, O(n·m) without an index. Hash join: build an in-memory hash table on the smaller side, then stream the larger — the workhorse for large equality joins, but it needs memory and cannot serve inequality conditions. Merge join: sort both inputs and walk them in lockstep — cheap when the inputs are already sorted (e.g., by index order). A missing index on a foreign key is the usual reason a query degrades into a nested loop over millions of rows.

Subqueries and CTEs

-- 1. Uncorrelated subquery (runs once)
SELECT Name, GPA FROM Student
WHERE GPA > (SELECT AVG(GPA) FROM Student);

-- 2. Correlated subquery (runs once per row of outer query)
-- Students with GPA above their department average
SELECT s.Name, s.GPA FROM Student s
WHERE s.GPA > (
    SELECT AVG(GPA) FROM Student WHERE DeptID = s.DeptID
);

-- 3. EXISTS (preferred over IN for large tables — short-circuits)
SELECT Name FROM Student s
WHERE EXISTS (
    SELECT 1 FROM Department d
    WHERE d.DeptID = s.DeptID AND d.Building = 'Tech Block'
);

-- 4. NOT EXISTS: students with no enrollment
SELECT Name FROM Student s
WHERE NOT EXISTS (SELECT 1 FROM Enrollment e WHERE e.StudentID = s.StudentID);

-- 5. CTE (Common Table Expression) — readable named subquery
WITH DeptAvg AS (
    SELECT DeptID, AVG(GPA) AS AvgGPA
    FROM Student WHERE Status = 'Active'
    GROUP BY DeptID
)
SELECT s.Name, s.GPA, d.DeptName
FROM Student s
JOIN Department d ON s.DeptID = d.DeptID
JOIN DeptAvg da ON s.DeptID = da.DeptID
WHERE s.GPA > da.AvgGPA
ORDER BY s.GPA DESC;

-- 6. Recursive CTE: org chart traversal
WITH RECURSIVE OrgChart AS (
    SELECT EmpID, Name, ManagerID, 1 AS Level
    FROM Employee WHERE ManagerID IS NULL          -- root (top manager)
    UNION ALL
    SELECT e.EmpID, e.Name, e.ManagerID, oc.Level + 1
    FROM Employee e JOIN OrgChart oc ON e.ManagerID = oc.EmpID
)
SELECT * FROM OrgChart ORDER BY Level, Name;

Practice questions

1. Difference between WHERE and HAVING? (Answer: WHERE filters individual rows BEFORE grouping. HAVING filters groups AFTER GROUP BY. Aggregate functions (SUM, COUNT, AVG) cannot go in WHERE.)
2. LEFT JOIN returns 100 rows, INNER JOIN returns 60 rows. What does this tell you? (Answer: 40 rows in the left table have no matching rows in the right table — those appear with NULLs in the LEFT JOIN only.)
3. Is a correlated subquery more or less efficient than uncorrelated? (Answer: Less efficient — runs once per row of outer query. For 10,000 outer rows, runs 10,000 times. Use EXISTS or JOIN as alternatives.)
4. CROSS JOIN between tables with 4 and 6 rows produces how many rows? (Answer: 4 × 6 = 24 rows — Cartesian product.)
5. Find the second highest salary in SQL. (Answer: SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee). Or: SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1.)
6. A LEFT JOIN with WHERE right_table.status = 'active' returns fewer rows than expected. Why? (Answer: Rows with no match are NULL-padded, so right_table.status IS NULL; the comparison NULL = 'active' evaluates to UNKNOWN and WHERE discards the row — silently converting the LEFT JOIN into an INNER JOIN. Move the condition into the ON clause: LEFT JOIN r ON r.id = l.id AND r.status = 'active'.)
7. Joining Orders (1,000 rows) to OrderItems (5,000 rows, ~5 per order), SUM(o.OrderTotal) is 5× too large. What happened? (Answer: Fan-out. The join key is not unique on the right side, so each order row is duplicated once per matching item, and its total is summed five times. Fix by aggregating OrderItems in a subquery before joining, or by summing over a de-duplicated set (e.g., SUM(DISTINCT ...) is not generally safe — pre-aggregation is.)
8. When would an optimizer choose a hash join over a nested loop join? (Answer: Hash join wins for large equality joins where no useful index exists — it builds a hash table on the smaller input (O(n+m)) instead of probing repeatedly (O(n·m)). Nested loop wins when one side is very small or the inner join key is indexed, making each probe cheap. Hash joins require memory and only work for equality conditions; inequality joins fall back to nested loops or merge joins.)