Part III — SQL: Structured Query Language

Chapter 13. Advanced SQL

Chapters 10–12 built the query toolkit; this chapter completes it. The headline feature is the window function — SQL:2003's addition that ranks, offsets, and aggregates across rows without collapsing them — which quietly changed how analysts write SQL and how interviewers test it. Around it sit the set operations, views and materialized views, conditional aggregation, pivoting, grouping sets, and a portability checklist that turns dialect awareness into a deliverable.

Everything runs on the canonical university database, as always with predicted-and-verified outputs.

After studying this chapter you will be able to:

• Apply UNION, UNION ALL, INTERSECT, and EXCEPT with union compatibility and dedup rules.
• Create and refresh views and materialized views, and know each platform's support.
• Write window functions with OVER, PARTITION BY, ORDER BY, and frames.
• Use ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, and LEAD correctly.
• Factor long queries with chained and recursive CTEs.
• Build cross-tabulations with conditional aggregation (FILTER and SUM(CASE)).
• Pivot and unpivot data portably.
• Apply advanced techniques — anti-joins, LATERAL, ROLLUP — and write portable SQL deliberately.

13.1 Set operations: UNION, UNION ALL, INTERSECT, and EXCEPT

Chapter 3's set algebra, as SQL. All four combine compatible SELECT results — same column count, compatible types — column by position:

SELECT s.full_name FROM student s
JOIN  enrollment e ON e.student_id = s.student_id
WHERE e.section_id = 8
UNION
SELECT s.full_name FROM student s
JOIN  enrollment e ON e.student_id = s.student_id
WHERE e.section_id = 1
ORDER BY full_name;

   full_name
---------------
 Arif Mahmud
 Farhan Akter
 Nusrat Jahan
 Rakib Hasan
 Sadia Afrin

UNION deduplicates (hence five students, not six — Nusrat Jahan and Rakib Hasan are in both sections); UNION ALL keeps every row and is therefore cheaper when duplicates are impossible or meaningful. The same two populations with the other operations:

INTERSECT            → Nusrat Jahan, Rakib Hasan        (in both sections)
EXCEPT (8 then 1)    → Sadia Afrin, Farhan Akter        (section 8 only)
EXCEPT (1 then 8)    → Arif Mahmud                      (section 1 only)

Order names attach to the whole result; column names come from the first SELECT. Platform note: MySQL gained INTERSECT and EXCEPT only in 8.0.31 — on earlier 8.0 releases, INTERSECT is an inner join and EXCEPT is NOT EXISTS/NOT IN (Chapter 12), equivalences worth knowing even on current versions since so much MySQL code in the wild still uses them.

13.2 Views and materialized views

Chapter 9 introduced views as stored queries; two advanced uses complete the picture. Updatable views: a simple view over one table (no aggregation, no DISTINCT) accepts INSERT/UPDATE/DELETE in both platforms, with WITH CHECK OPTION preventing changes that would leave the view's scope (updating a cse_student row's major to 2 would silently vanish from the view — CHECK OPTION makes the DBMS refuse it instead).

Materialized views store the result and are refreshed on demand — the bridge between transactional tables and expensive reports:

CREATE MATERIALIZED VIEW dept_gpa_stats AS
SELECT major_dept_id, COUNT(*) AS students, AVG(gpa) AS avg_gpa
FROM   student
GROUP  BY major_dept_id;

REFRESH MATERIALIZED VIEW dept_gpa_stats;

The view answers instantly between refreshes and can carry indexes of its own — at the price of staleness, which the explicit REFRESH makes stated rather than accidental. PostgreSQL implements materialized views natively; MySQL has no materialized view — the standard emulation is a summary table kept current by triggers (Chapter 22) or refreshed by a scheduled event, a pattern Chapter 25's warehouse uses heavily. Views and materialized views together are the external schema of Chapter 4 made concrete: one always-fresh and virtual, one fast and explicitly refreshed.

13.3 Window functions

A window function runs over a window of rows defined per result row — OVER (...) — and returns a value without collapsing rows, which is exactly what GROUP BY cannot do:

SELECT full_name, major_dept_id, gpa,
       ROUND(AVG(gpa) OVER (PARTITION BY major_dept_id), 2) AS dept_avg
FROM   student
WHERE  gpa IS NOT NULL
ORDER  BY major_dept_id, gpa DESC;

     full_name      | major_dept_id | gpa  | dept_avg
--------------------+---------------+------+----------
 Arif Mahmud        |             1 | 3.90 |     3.66
 Nusrat Jahan       |             1 | 3.75 |     3.66
 Tanvir Alam        |             1 | 3.55 |     3.66
 Rakib Hasan        |             1 | 3.42 |     3.66
 Sadia Afrin        |             2 | 3.88 |     3.61
 Mehjabin Chowdhury |             2 | 3.70 |     3.61
 Shahriar Islam     |             2 | 3.25 |     3.61
 Imran Hossain      |             3 | 3.15 |     3.10
 Nabil Khan         |             3 | 3.05 |     3.10
 Farhan Akter       |             4 | 2.98 |     2.98
 Sumaiya Tabassum   |             5 | 3.60 |     3.60

Every student row survives, each carrying its department's average — the honor-roll query of Chapter 12 without any join or subquery. The grammar: OVER (PARTITION BY ... ORDER BY ... frame) — PARTITION BY resets the window per group (the department), ORDER BY orders rows within the partition (enabling ranking and running values), and the frame (default with ORDER BY: from partition start to current row) selects which ordered rows the function sees. A running total makes the frame visible:

SELECT grade, COUNT(*) AS awarded,
       SUM(COUNT(*)) OVER (ORDER BY grade) AS running_total
FROM   enrollment
WHERE  grade IS NOT NULL
GROUP  BY grade
ORDER  BY grade;

 grade | awarded | running_total
-------+---------+---------------
 A     |       7 |             7
 A-    |       4 |            11
 B     |       4 |            15
 B+    |       4 |            19
 C+    |       1 |            20

The grade distribution of Chapter 11 with its cumulative column — a two-level trick worth studying: GROUP BY collapses to five rows, then the window function runs over the grouped result (a window function's input is always the post-grouping rows). Both platforms support window functions fully — MySQL since 8.0, which was the release that made this chapter writable for both books' platforms.

13.4 Ranking and analytical functions

The ranking family, distinguished by how they handle ties — best taught on data that has one:

SELECT full_name, dept_id, salary,
       ROW_NUMBER() OVER (ORDER BY dept_id) AS row_num,
       RANK()       OVER (ORDER BY dept_id) AS rnk,
       DENSE_RANK() OVER (ORDER BY dept_id) AS dense
FROM   instructor
ORDER  BY dept_id;

     full_name      | dept_id |  salary  | row_num | rnk | dense
--------------------+---------+----------+---------+-----+-------
 Ahmed Kabir        |       1 | 120000.00 |       1 |   1 |     1
 Farhana Rahman     |       1 | 105000.00 |       2 |   1 |     1
 Nazmul Chowdhury   |       2 | 130000.00 |       3 |   3 |     2
 Sharmin Ahmed      |       3 |  98000.00 |       4 |   4 |     3
 Mahmudul Islam     |       4 | 110000.00 |       5 |   5 |     4
 Tahmina Karim      |       5 |  92000.00 |       6 |   6 |     5

Ahmed and Farhana tie on dept 1: ROW_NUMBER gives them arbitrary-but-distinct numbers, RANK gives both 1 and skips to 3, DENSE_RANK gives both 1 and continues at 2. Choose by intent: competition standings (RANK), ordinal labels (ROW_NUMBER), category numbering (DENSE_RANK). NTILE(4) buckets rows into quartiles; and the offset functions read neighboring rows:

WITH per_year AS (
    SELECT cs.section_year, COUNT(*) AS enrollments
    FROM   enrollment e
    JOIN   course_section cs ON cs.section_id = e.section_id
    GROUP  BY cs.section_year
)
SELECT section_year, enrollments,
       LAG(enrollments) OVER (ORDER BY section_year) AS previous,
       enrollments - LAG(enrollments) OVER (ORDER BY section_year) AS change
FROM   per_year;

 section_year | enrollments | previous | change
--------------+-------------+----------+--------
         2024 |          11 |   (null) | (null)
         2025 |           9 |       11 |     -2
         2026 |           8 |        9 |     -1

Year-over-year enrollment: 11 graded-era enrollments in 2024, 9 in 2025, 8 so far in 2026, each compared with LAG (the row before) — LEAD looks ahead, and both take a default and offset (LAG(x, 1, 0)). The top-N-per-group question — "each department's best student" — is the ranking family's signature application, and Laboratory 4 builds it.

13.5 Common Table Expressions and recursive CTEs

Chapter 12 staged queries with CTEs and walked chains with WITH RECURSIVE; three advanced notes complete the tool. First, chained pipelines may mix non-recursive and recursive CTEs in one WITH, and every stage can be reasoned about independently — the structure of Section 12.11 scaled to any depth. Second, data-changing CTEs (PostgreSQL): a CTE may hold an INSERT/UPDATE/DELETE and pass RETURNING rows to the main statement — "archive then delete" in one statement, a pattern Chapter 22's triggers echo. Third, materialization control (PostgreSQL 12+): WITH x AS MATERIALIZED (...) forces the CTE to compute once (an optimization fence), NOT MATERIALIZED inlines it — the planner chooses by default, and the hint is for the rare query where you know better (Chapter 19 measures such choices). MySQL 8.0 supports all the chaining forms; its CTEs are always optimized inline.

13.6 Conditional aggregation

Chapter 11's FILTER and SUM(CASE ...) become reporting machinery: per-group counts of several conditions in one pass:

SELECT section_id,
       COUNT(*) FILTER (WHERE grade IN ('A','A+'))              AS top_band,
       COUNT(*) FILTER (WHERE grade IN ('A-','B+','B'))          AS mid_band,
       COUNT(*) FILTER (WHERE grade IN ('C+','C','C-','D','F'))  AS low_band
FROM   enrollment
WHERE  grade IS NOT NULL
GROUP  BY section_id
ORDER  BY section_id;

 section_id | top_band | mid_band | low_band
------------+----------+----------+----------
          1 |        2 |        1 |        0
          2 |        0 |        1 |        0
          3 |        0 |        2 |        0
          4 |        0 |        2 |        0
          6 |        2 |        0 |        0
          7 |        1 |        1 |        0
          8 |        1 |        2 |        1
          9 |        1 |        0 |        0
         10 |        0 |        3 |        0

One scan of the graded enrollments, three banded counts per section — section 8 (Calculus I, Fall 2024) shows the only low-band grade in the university. The portable form of every FILTER is SUM(CASE WHEN cond THEN 1 ELSE 0 END) (or COUNT(*) - COUNT(NULLIF(...))); the two are interchangeable, and the MySQL spelling of this listing is exactly that substitution. Conditional aggregation is also the heart of pivoting, next.

13.7 Pivoting and reporting

Pivoting turns rows into columns — "enrollments per year, one column per semester":

SELECT cs.section_year,
       SUM(CASE WHEN cs.semester = 'Fall'   THEN 1 ELSE 0 END) AS fall,
       SUM(CASE WHEN cs.semester = 'Spring' THEN 1 ELSE 0 END) AS spring,
       SUM(CASE WHEN cs.semester = 'Summer' THEN 1 ELSE 0 END) AS summer
FROM   enrollment e
JOIN   course_section cs ON cs.section_id = e.section_id
GROUP  BY cs.section_year
ORDER  BY cs.section_year;

 section_year | fall | spring | summer
--------------+------+--------+--------
         2024 |   11 |      0 |      0
         2025 |    0 |      9 |      0
         2026 |    8 |      0 |      0

Three rows, three semester columns — the university's whole enrollment history in one crosstab. Neither PostgreSQL nor MySQL has a PIVOT keyword (SQL Server does); the conditional-aggregation idiom is the portable pivot, and its cost model is honest: one column per pivot value, written by hand — fine for fixed domains like semesters, untenable for unbounded ones like product IDs, which belong in row form (a "long" table) until a report genuinely needs them wide. Unpivoting (columns back to rows) is the same idea inverted — a UNION ALL of one SELECT per column, or PostgreSQL's jsonb/unnest tricks; Chapter 25 meets both directions again in warehouse loading.

13.8 Advanced query-writing techniques

Four techniques that separate fluent from functional query writers:

• Anti-join as the standard idiom — "rows without matches" is NOT EXISTS (Section 12.8), not NOT IN (NULL trap) and not LEFT JOIN ... WHERE right IS NULL (correct but easy to get wrong under additional right-table conditions — ON/WHERE again).
• LATERAL — a derived table that may reference the FROM items to its left, one evaluation per outer row: PostgreSQL's JOIN LATERAL, MySQL 8.0.14+ derived tables with the same effect. The shape for "top 3 enrollments per section" without window functions, and the standard home for per-row parameterized subqueries.
• Grouping sets — several GROUP BY granularities in one query: PostgreSQL's GROUP BY ROLLUP (major_dept_id, admission_year) emits per-pair counts, per-major subtotals, and the grand total (12) as extra rows; MySQL spells it GROUP BY ... WITH ROLLUP (subtotals only, no full CUBE). Report tools consume ROLLUP rows directly — that is what spreadsheet subtotals are.
• PostgreSQL's DISTINCT ON — SELECT DISTINCT ON (major_dept_id) full_name, gpa FROM student ORDER BY major_dept_id, gpa DESC returns each department's top student in one statement (tie-break via the ORDER BY); MySQL's equivalent is ROW_NUMBER in a CTE with a filter — both spellings belong in your comparison notebook.

One caution to carry: every feature in this chapter changes row multiplicity — window functions add columns to existing rows, set operations add rows, ROLLUP adds aggregate rows. When a report's numbers look odd, count the rows first.

13.9 SQL portability and dialect differences

The chapter closes with the checklist this book has been building — the differences that actually bite between PostgreSQL 16 and MySQL 8.0:

The professional method for portable SQL, in four habits: write the standard spelling where one exists; catalog every divergence in a team document (Appendix I is this book's); test on both platforms during development, not after; and confine dialect to a layer — a query module, a reporting function — rather than scattering it through the codebase. Portability is not the belief that databases never change; it is the practice of making the change cheap.

Chapter Summary

• UNION deduplicates, UNION ALL does not; INTERSECT/EXCEPT require compatibility and arrived in MySQL only at 8.0.31.
• Views are virtual and always fresh (updatable when simple, guarded by WITH CHECK OPTION); materialized views are stored, fast, explicitly refreshed — native in PostgreSQL, emulated in MySQL.
• Window functions (OVER with PARTITION BY/ORDER BY/frame) aggregate across rows without collapsing them — per-row department averages, running totals over grouped results.
• ROW_NUMBER/RANK/DENSE_RANK differ on ties (arbitrary / skip / continue); NTILE buckets; LAG/LEAD offset (year-over-year change); top-N-per-group is their signature use.
• CTEs chain and recurse; PostgreSQL adds data-changing CTEs and MATERIALIZED/NOT MATERIALIZED hints.
• Conditional aggregation (FILTER or SUM(CASE)) produces banded counts per group in one scan — and is the portable pivot idiom.
• Pivoting is hand-per-column (fine for fixed domains); unpivoting is UNION ALL per column.
• Advanced idioms: NOT EXISTS anti-join, LATERAL per-row subqueries, ROLLUP subtotals, DISTINCT ON (PostgreSQL) versus ROW_NUMBER (portable).
• The portability checklist catalogs the real PostgreSQL/MySQL differences; the method is standard spelling, cataloged divergences, dual-platform testing, dialect confined to a layer.

Key Terms

Laboratory Exercises

1. Rebuild Section 13.1 with UNION ALL and count rows (6), then with UNION (5), and reconcile the difference by naming the two students in both sections. Expected results: UNION ALL returns 6 rows, UNION 5; Nusrat Jahan and Rakib Hasan appear in both section 8 and section 1.
2. Create the dept_gpa_stats materialized view (PostgreSQL) and its MySQL emulation (summary table + INSERT ... SELECT), then insert a new student in university_dev, refresh/reload, and compare freshness behavior. Expected result: the PostgreSQL view is stale until REFRESH; the MySQL table is as fresh as its reload job — the trade stated in mechanisms.
3. Run the window-average listing of Section 13.3 with and without WHERE gpa IS NOT NULL, and predict both outputs before running. Expected result: 11 rows without Zara versus 12 rows with her — dept 4's average drops to 2.98 shown on Farhan only in the filtered form, and Zara carries a NULL-average row in the unfiltered one (AVG skips her NULL in both cases).
4. Top-N per group, two ways: each department's highest-GPA student via ROW_NUMBER() ... AS rn in a CTE filtered to rn = 1, and (PostgreSQL) via DISTINCT ON. Verify both return the same five students. Expected result: Arif Mahmud (CSE, 3.90), Sadia Afrin (EEE, 3.88), Imran Hossain (BBA, 3.15), Sumaiya Tabassum (ENG, 3.60), and Mathematics' top student depends on your NULL handling — Farhan Akter (2.98) if NULLs are filtered or sorted last, but Zara Hossain (NULL) if you use PostgreSQL's plain ORDER BY gpa DESC, whose default is NULLS FIRST (Chapter 10); state which you chose and why.
5. Build the semester pivot of Section 13.7 and add a total column via COUNT(*); then unpivot it back to three-column (year, semester, count) form with UNION ALL. Expected result: pivot totals 11/9/8 by year; the unpivot yields 9 rows — (2024,Fall,11), (2025,Spring,9), (2026,Fall,8) plus six zero rows if the zero-semesters are kept, or 3 rows if filtered.
6. Write the ROLLUP report (GROUP BY ROLLUP (major_dept_id, admission_year) in PostgreSQL, WITH ROLLUP in MySQL) and identify the subtotal rows by their NULL markers; verify the grand total row. Expected result: 5 department subtotals (4, 3, 2, 2, 1), 12 detail rows, and a grand total of 12 students; MySQL's spelling differs and lacks CUBE.

Review Questions and Exercises

1. Why does the section 8/section 1 UNION return five rows and what would UNION ALL return? UNION deduplicates; UNION ALL returns all six rows including Nusrat Jahan's and Rakib Hasan's repeats.
2. A view over enrollment showing only graded rows: what does WITH CHECK OPTION prevent that its absence allows? Inserting or updating rows into invisible territory — e.g., setting a grade to NULL would leave the view's scope; CHECK OPTION refuses, without it the row silently vanishes from the view.
3. What can a window function do that GROUP BY cannot, in one sentence? Attach aggregate values to individual rows without collapsing them — per-row context like a department average beside each student.
4. Write the window clause for a running sum of enrollments ordered by section_id, partitioned by semester. *SUM(n) OVER (PARTITION BY semester ORDER BY section_id) — with the default frame from partition start to current row.*
5. Ahmed and Farhana tie; state ROW_NUMBER, RANK, and DENSE_RANK for both, and for Nazmul who follows. ROW_NUMBER 1 and 2 (arbitrary order); RANK 1 and 1, then Nazmul 3; DENSE_RANK 1 and 1, then Nazmul 2.
6. What do LAG(x), LAG(x, 2), and LAG(x, 1, 0) return on the first rows of a partition? NULL, NULL, and 0 respectively — no previous row, so the default applies.
7. Rewrite COUNT(*) FILTER (WHERE grade = 'A') in the portable MySQL form.

   SUM(CASE WHEN grade = 'A' THEN 1 ELSE 0 END)

8. Why is conditional aggregation the "portable pivot," and when does it stop scaling? Every platform has SUM and CASE; a pivot value costs a hand-written column — fixed domains are fine, unbounded domains (product IDs) explode.
9. Give two spellings of "each department's top student" and their platforms. PostgreSQL: DISTINCT ON (major_dept_id) with ORDER BY major_dept_id, gpa DESC; both platforms: ROW_NUMBER() OVER (PARTITION BY major_dept_id ORDER BY gpa DESC) filtered to 1.
10. What do ROLLUP rows look like, and what does the grand-total row of the student report show? Aggregate rows with NULL in the grouped columns; 12 students with NULLs in both major_dept_id and admission_year.
11. Name three real portability differences from the table and each platform's spelling. Any three: concatenation (|| vs CONCAT), FULL JOIN (native vs UNION emulation), FILTER (native vs CASE), identity keys (IDENTITY/SERIAL vs AUTO_INCREMENT), INTERSECT/EXCEPT (native vs 8.0.31+).
12. Why confine dialect SQL to a layer instead of scattering it? Portability is change-cost management — one module to rewrite when the platform changes, instead of a codebase-wide hunt.

Mini-Project

Upgrade the Chapter 11 dashboard into dashboard_advanced.sql — the same five questions, now with this chapter's machinery, each query annotated with what it replaced and why the new form is better or merely different: (1) the grade distribution with a percentage column via 100.0 * n / SUM(n) OVER () and a running total (no application-side math); (2) the cohort study with AVG(gpa) OVER (PARTITION BY admission_year) beside each student and a RANK within cohort; (3) the per-major summary with each student's deviation from the major average (window minus row value) and an NTILE(3) banding; (4) the instructor summary with RANK by salary within department and LAG against the previous hire; (5) the sections report as a pivot (per semester band columns) with ROLLUP subtotals. Deliver both platform versions where spellings differ, a comment per divergence keyed to Section 13.9's table, and a final paragraph: which upgrades genuinely improved the report, and which were showmanship — honest judgment is the last skill this Part teaches.