SQL Advanced

WITH cte AS (…)

Common Table Expression — a named, reusable temporary result set scoped to one query. Improves readability. WITH cte AS (SELECT …) SELECT * FROM cte

Recursive CTE

A CTE that references itself. Used for hierarchical data (org charts, trees). WITH RECURSIVE cte AS (anchor UNION ALL recursive_part) SELECT * FROM cte

ROW_NUMBER()

Assigns a unique sequential integer to each row within a partition. No ties — always unique.

RANK()

Assigns rank within a partition. Ties get the same rank; the next rank skips (1,1,3,4).

DENSE_RANK()

Like RANK() but no gaps after ties (1,1,2,3). Use when you don't want rank gaps.

NTILE(n)

Divides rows into n roughly equal buckets and assigns each row a bucket number (1 to n).

LAG(col, n, default)

Returns the value of col from n rows BEFORE the current row. Default if no prior row.

LEAD(col, n, default)

Returns the value of col from n rows AFTER the current row. Default if no next row.

FIRST_VALUE(col)

Returns the first value of col in the window frame.

LAST_VALUE(col)

Returns the last value of col in the current window frame. Frame defaults to current row — add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get true last.

OVER(PARTITION BY … ORDER BY …)

Defines the window for a window function. PARTITION BY = groups (like GROUP BY). ORDER BY = sort within the group. Can be used without PARTITION BY for global window.

SUM() OVER()

Running or partitioned total without collapsing rows. Rows are preserved. SUM(col) OVER(PARTITION BY grp ORDER BY date)

Window Frame: ROWS BETWEEN

Controls which rows are included in the window. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW = running total. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING = 3-row rolling window.

EXISTS

Returns TRUE if the subquery returns at least one row. SELECT * FROM t WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.id = t.id)

NOT EXISTS

Returns TRUE if the subquery returns no rows. Often more efficient than NOT IN with NULLs.

STRING_AGG(col, sep)

Concatenates values from multiple rows into one string with a separator. (PostgreSQL / SQL Server 2017+). STRING_AGG(name, ', ')

GROUP_CONCAT(col)

MySQL equivalent of STRING_AGG. GROUP_CONCAT(col SEPARATOR ', ')

INTERSECT

Returns only rows that appear in BOTH query results.

EXCEPT / MINUS

Returns rows in the first result that do NOT appear in the second. (MINUS in Oracle)

PIVOT

Transforms distinct row values into columns. Syntax varies by database. Often done with CASE WHEN + GROUP BY for portability.

INDEX

A data structure that speeds up lookups at the cost of write overhead. CREATE INDEX idx ON table(col). Use on columns frequently used in WHERE, JOIN ON, ORDER BY.

EXPLAIN / QUERY PLAN

Shows how the database will execute a query — scans, joins, index usage. Use to identify slow operations.

Correlated Subquery

A subquery that references columns from the outer query. Runs once per row — can be slow. Often replaceable with a JOIN.

SELF JOIN

A table joined to itself. Requires aliases. SELECT a.emp, b.manager FROM emp a JOIN emp b ON a.mgr_id = b.emp_id