Cheat Sheet

SQL Quick Reference

The clauses in the order the database runs them, the patterns you reach for daily, and the Oracle vs PostgreSQL differences that bite when you switch.

Groups

Oracle 23

Checked

PG 17

Checked

Index · Spring · SQL · Git · Docker/kubectl · Kafka/Rabbit CLI · Redis · Big-O

Chapter 1 - Order of evaluation

Written Order vs Logical Order

You write SELECT first, but the database logically processes it near the end. This one table explains why you cannot use a SELECT alias in WHERE, why HAVING exists, and why window functions cannot appear in WHERE.

Step	Clause	What happens	Consequence
1	`FROM` / `JOIN`	Build the row source: tables, joins, subqueries	Join conditions (`ON`) filter before `WHERE`; for outer joins this matters
2	`WHERE`	Filter individual rows	Cannot use aggregates or select-list aliases here
3	`GROUP BY`	Form groups	Every non-aggregated select column must be in the group (standard SQL)
4	`HAVING`	Filter groups	Use for conditions on aggregates; use `WHERE` for row conditions (faster)
5	Window functions	Computed over the rows left after step 4	Cannot be used in `WHERE` or `GROUP BY`; wrap in a subquery or CTE to filter on them
6	`SELECT`	Evaluate the select list and aliases	Aliases exist only from here on
7	`DISTINCT`	Remove duplicate rows	Applies to the whole select list, not one column
8	`ORDER BY`	Sort	May use select-list aliases; PostgreSQL also accepts them in `GROUP BY`, Oracle does not
9	`LIMIT` / `FETCH FIRST`	Cut the sorted result	Without `ORDER BY` the rows you get are arbitrary

Chapter 2 - Query building blocks

Select, Filter, Sort, Page

The everyday clauses and the operators that trip people up.

Item	Meaning	Gotcha
`SELECT DISTINCT a, b`	Unique combinations of the listed columns	`DISTINCT` hides a bad join; fix the join instead
`WHERE a BETWEEN 1 AND 5`	Inclusive on both ends	On timestamps, `BETWEEN` day1 AND day2 misses most of day2; use `>=` and `<`
`WHERE a IN (1, 2, 3)`	Match any listed value	Oracle limits an `IN` list to 1000 expressions
`WHERE a LIKE 'ab%'`	`%` any characters, `_` one character	A leading `%` prevents a normal index range scan; `LIKE` is case-sensitive in Oracle and PostgreSQL
`WHERE EXISTS (SELECT 1 ...)`	True if the subquery returns any row	Stops at the first match; safe with NULLs, unlike `NOT IN`
`CASE WHEN c THEN x ELSE y END`	Conditional expression	No `ELSE` gives NULL; branches must have compatible types
`ORDER BY 2 DESC`	Sort by the second select column	Position numbers break when the select list changes
`ORDER BY x NULLS LAST`	Control NULL placement	Default in both Oracle and PostgreSQL: NULLs sort as largest (last for ASC, first for DESC)
`FETCH FIRST n ROWS ONLY`	Standard row limit (Oracle 12c+, PostgreSQL)	`WITH TIES` also returns rows tied with the last one and needs `ORDER BY`
`OFFSET m ROWS FETCH NEXT n ROWS ONLY`	Skip m, then take n	Deep offsets scan and discard rows; use keyset paging (`WHERE id > :last ORDER BY id`) for big tables
`LIMIT n OFFSET m`	PostgreSQL/MySQL spelling of the same	Not valid in Oracle
`SELECT ... FOR UPDATE SKIP LOCKED`	Lock rows, skip rows locked by others	Good for job queues; both Oracle and PostgreSQL support `SKIP LOCKED`

Chapter 3 - Joins

Combining Tables

Think of a join as: take every pair of rows, keep pairs where ON is true. Outer joins add unmatched rows back with NULLs.

Join	Returns	Use when	Gotcha
`INNER JOIN`	Only matching pairs	You need rows that exist on both sides	Silently drops rows with no match
`LEFT [OUTER] JOIN`	All left rows, right columns NULL if no match	Keep the main entity even without children	A `WHERE` on a right-table column turns it into an inner join; put it in `ON`
`RIGHT [OUTER] JOIN`	Mirror of left join	Rarely; swap the table order instead	Mixing left and right joins in one query is hard to read
`FULL [OUTER] JOIN`	All rows from both sides	Reconciliation: find rows missing on either side	Filter with `WHERE l.id IS NULL OR r.id IS NULL`
`CROSS JOIN`	Every pair (Cartesian product)	Generate combinations, calendars	Forgetting a join condition produces one by accident: N x M rows
Self join	Join a table to itself with two aliases	Hierarchies, "compare to previous row"	Alias both sides; consider a window function (`LAG`) instead
`USING (col)`	Join on same-named column, output it once	Short joins on identical key names	`SELECT *` shows the joined column once; names must really be identical on both sides
`NATURAL JOIN`	Joins on all same-named columns	Almost never	A new column with the same name changes the join silently
Anti join: `NOT EXISTS`	Rows with no match in another table	"Customers without orders"	Prefer it over `NOT IN` (NULL trap) and usually over `LEFT JOIN ... IS NULL`
Semi join: `EXISTS`	Rows that have at least one match, no duplicates	"Customers with orders"	A plain join would repeat the customer once per order
Join fan-out	One-to-many join multiplies parent rows	Aggregating after joining two child tables	`SUM` over a fan-out double counts; aggregate each child first in a subquery

Chapter 4 - Aggregates

Grouping and Summaries

Aggregates collapse rows. NULLs are ignored by every aggregate except COUNT(*).

Item	Meaning	Gotcha
`COUNT(*)`	Number of rows	Counts NULL rows too
`COUNT(col)`	Number of non-NULL values	Different from `COUNT(*)` when the column has NULLs
`COUNT(DISTINCT col)`	Number of distinct non-NULL values	Can be slow on big sets
`SUM` / `AVG`	Total and mean of non-NULL values	`AVG` over no rows or all NULLs is NULL; `SUM` of no rows is NULL, not 0
`MIN` / `MAX`	Smallest and largest	Work on strings and dates too
`GROUP BY a, b`	One output row per distinct pair	Selecting a column not in the group fails in Oracle and PostgreSQL (unless it is functionally dependent in PostgreSQL)
`HAVING COUNT(*) > 1`	Keep groups meeting a condition	The standard way to find duplicates
`ROLLUP(a, b)`	Subtotals and a grand total	Subtotal rows show NULL in the rolled-up columns; use `GROUPING()` to tell them from real NULLs
`CUBE(a, b)`	Subtotals for every combination	Row count grows as 2 to the power of the number of columns
`GROUPING SETS`	Choose exactly which groupings	Verbose but exact
String aggregation	PostgreSQL `string_agg(x, ',' ORDER BY x)`; Oracle `LISTAGG(x, ',') WITHIN GROUP (ORDER BY x)`	Oracle `LISTAGG` raises ORA-01489 on overflow by default; `ON OVERFLOW TRUNCATE` is available
`FILTER (WHERE ...)`	PostgreSQL: aggregate over a subset, e.g. `COUNT(*) FILTER (WHERE x > 0)`	Not in Oracle; use `SUM(CASE WHEN ... THEN 1 ELSE 0 END)`

Chapter 5 - Window functions

Analytic Functions

A window function adds a value to each row without collapsing rows: fn() OVER (PARTITION BY ... ORDER BY ... frame). Both Oracle (as "analytic functions") and PostgreSQL support the functions below.

Item	Meaning	Gotcha
`ROW_NUMBER()`	1, 2, 3 ... within the partition	Ties get different numbers in an arbitrary order; add a tie-breaker column to `ORDER BY`
`RANK()`	Ties share a rank, then a gap (1, 1, 3)	Gaps surprise people expecting consecutive numbers
`DENSE_RANK()`	Ties share a rank, no gap (1, 1, 2)	Good for "second highest salary"
`NTILE(n)`	Split rows into n buckets	Bucket sizes differ by one when not divisible
`LAG(x, n, default)` / `LEAD`	Value from n rows before or after	Returns NULL at the edges unless you give a default
`FIRST_VALUE` / `LAST_VALUE`	First or last value in the frame	Default frame ends at the current row, so `LAST_VALUE` often returns the current row; extend the frame to `UNBOUNDED FOLLOWING`
`SUM(x) OVER (ORDER BY d)`	Running total	With `ORDER BY` and no frame, ties (peers) are summed together (`RANGE` default)
`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`	Moving window by physical rows	`ROWS` counts rows; `RANGE` counts by value and includes peers
`PARTITION BY`	Restart the calculation per group	Omit it and the whole result is one partition
Filtering on a window result	Wrap in a subquery or CTE and filter outside	Window functions run after `WHERE`, so you cannot filter on them directly

Chapter 6 - Patterns

Everyday Patterns

Top N per group, de-duplicate, running total, hierarchy (standard SQL; checked on SQLite 3.45 for syntax and results, Oracle needs the alias changes in chapter 10)

-- Top 2 salaries per department (add a tie-breaker so the result is stable)
SELECT dept, name, salary
FROM (SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, id) AS rn
    FROM emp e) ranked
WHERE rn <= 2;

-- Keep the first row of each duplicate set
SELECT * FROM (
SELECT e.*, ROW_NUMBER() OVER (PARTITION BY dept, salary ORDER BY id) AS rn FROM emp e
) d WHERE rn = 1;

-- Running total
SELECT id, salary,
     SUM(salary) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM emp;

-- Org chart walk (PostgreSQL and SQLite: WITH RECURSIVE; Oracle: WITH without RECURSIVE)
WITH RECURSIVE t(id, name, lvl) AS (
SELECT id, name, 1 FROM emp WHERE mgr IS NULL
UNION ALL
SELECT e.id, e.name, t.lvl + 1 FROM emp e JOIN t ON e.mgr = t.id
)
SELECT * FROM t;

Chapter 7 - Subqueries, CTEs, set operators

Composing Queries

A CTE (WITH name AS (...)) names a subquery so the main query reads top to bottom.

Item	Meaning	Gotcha
Scalar subquery	Returns one value, used like a column	More than one row is a runtime error
`IN (subquery)`	Match any value returned	Fine for `IN`; `NOT IN` is dangerous with NULLs (see chapter 8)
Correlated subquery	References the outer row; re-evaluated per row logically	Optimisers often turn it into a join; rewrite as a join or window function if slow
Derived table	Subquery in `FROM`	Oracle: no `AS` before the alias; PostgreSQL requires an alias
`WITH x AS (...)`	Common table expression (CTE)	Readability tool; whether it is inlined or materialised depends on the database and version
`WITH RECURSIVE`	Anchor query + recursive member joined to the CTE	Oracle omits the `RECURSIVE` keyword; add a termination condition or it loops (cycle clauses exist)
`UNION`	Combine results, remove duplicates	Sorts or hashes to deduplicate; use `UNION ALL` when you know there are no duplicates
`UNION ALL`	Combine results, keep duplicates	Fastest; columns must match in count and type group
`INTERSECT`	Rows in both results	Duplicates removed; NULLs are treated as equal here
`EXCEPT` / `MINUS`	Rows in the first but not the second	PostgreSQL: `EXCEPT`. Oracle: `MINUS`, and the Oracle 23 docs list `EXCEPT` as a synonym
`ALL` variants	`INTERSECT ALL`, `EXCEPT ALL` keep duplicates by count	PostgreSQL has them; Oracle 23 docs list them too, older Oracle does not
Set operator precedence	Evaluated left to right in Oracle; use parentheses	In standard SQL `INTERSECT` binds tighter than `UNION`; parenthesise to be safe

Chapter 8 - NULL logic

Three-Valued Logic

SQL has TRUE, FALSE and UNKNOWN. WHERE keeps only TRUE rows, so UNKNOWN rows vanish.

Expression	Result	Why / fix
`NULL = NULL`	UNKNOWN (not TRUE)	Use `IS NULL` and `IS NOT NULL`
`NULL <> 1`	UNKNOWN	Rows with NULL are excluded by `WHERE x <> 1`; add `OR x IS NULL` if you want them
`1 NOT IN (2, NULL)`	UNKNOWN: returns no rows	Use `NOT EXISTS`; checked: returns zero rows
`TRUE OR UNKNOWN`	TRUE	Short-circuit rule
`FALSE AND UNKNOWN`	FALSE	Short-circuit rule
`NOT UNKNOWN`	UNKNOWN	Negating does not turn it into TRUE
`NULL + 10`	NULL	Arithmetic with NULL is NULL
`COUNT(*)` vs `COUNT(col)`	5 vs 4 when one value is NULL	Checked on a sample table
`COALESCE(a, b, c)`	First non-NULL argument	ANSI standard, works in Oracle and PostgreSQL; all arguments must share a type group
`NULLIF(a, b)`	NULL if a equals b, else a	Classic divide-by-zero guard: `x / NULLIF(y, 0)`
`GROUP BY` / `DISTINCT`	Treat all NULLs as one group	Unlike `=`, grouping considers NULLs equal
Unique constraint	PostgreSQL allows many NULLs in a unique column	Oracle allows many NULLs in a single-column unique key; in composite keys, two rows with all-NULL are considered duplicates (Oracle treats nulls as equal in compound keys)
Oracle empty string	`''` is NULL in Oracle	The Oracle docs say a zero-length character value is treated as NULL; PostgreSQL keeps `''` and NULL distinct
`\|\|` with NULL	Oracle: NULL is ignored; PostgreSQL: result is NULL	In PostgreSQL use `concat()` (skips NULLs) or `COALESCE`

Chapter 9 - Dates and times

Date Handling: Oracle vs PostgreSQL

Dates are where portability ends. Learn these pairs.

Task	Oracle	PostgreSQL	Gotcha
Current date and time	`SYSDATE` (DATE with time), `SYSTIMESTAMP`, `CURRENT_TIMESTAMP`	`now()`, `CURRENT_TIMESTAMP`, `CURRENT_DATE`	Oracle `DATE` always has a time part; PostgreSQL `date` does not
Drop the time part	`TRUNC(d)`	`date_trunc('day', ts)` or cast `ts::date`	Using `TRUNC(col)` in a `WHERE` stops index use; compare with a range
Add days	`d + 7`	`d + 7` (date) or `ts + INTERVAL '7 days'`	In Oracle the number is days, fractions are hours
Add months	`ADD_MONTHS(d, 1)`	`d + INTERVAL '1 month'`	End-of-month behaviour differs; test Jan 31 + 1 month
Difference	`d2 - d1` gives days (NUMBER)	`date - date` gives integer days; `timestamp - timestamp` gives an interval	Different types, so reporting code needs changes
Months between	`MONTHS_BETWEEN(d2, d1)`	`age(d2, d1)` or `EXTRACT` on an interval	Results are not the same kind of number
Last day of month	`LAST_DAY(d)`	`(date_trunc('month', d) + INTERVAL '1 month - 1 day')::date`	Different idioms
Format as text	`TO_CHAR(d, 'YYYY-MM-DD HH24:MI:SS')`	`to_char(d, 'YYYY-MM-DD HH24:MI:SS')`	Format models are similar but not identical; check minutes (`MI`) vs months (`MM`)
Parse text	`TO_DATE(s, fmt)`, `TO_TIMESTAMP`	`to_date`, `to_timestamp`, or cast	Without a format, Oracle uses `NLS_DATE_FORMAT` of the session
Extract a part	`EXTRACT(YEAR FROM d)`	`EXTRACT(YEAR FROM d)`	Same standard syntax in both
Date literal	`DATE '2026-10-31'`	`DATE '2026-10-31'`	Prefer literals over relying on session formats
Time zone	`TIMESTAMP WITH TIME ZONE`, `AT TIME ZONE`	`timestamptz`, `AT TIME ZONE`	PostgreSQL `timestamptz` stores UTC and converts on display; store UTC and convert at the edge
Range filter (any DB)	`WHERE d >= DATE '2026-10-01' AND d < DATE '2026-11-01'`	same	Half-open ranges avoid the `BETWEEN` end-of-day trap

Chapter 10 - Oracle vs PostgreSQL

Dialect Differences You Will Hit

Checked against the Oracle Database SQL Language Reference (23 series) and the PostgreSQL documentation (current) on 2026-10-31. Where the Oracle 23 release changed behaviour, the row says so.

Topic	Oracle	PostgreSQL	Note
Limit rows	`FETCH FIRST n ROWS ONLY` (12c+); older code uses `WHERE ROWNUM <= n`	`LIMIT n` or `FETCH FIRST n ROWS ONLY`	`ROWNUM` is assigned before `ORDER BY`, so `WHERE ROWNUM <= 5 ORDER BY x` is not the top 5 by x; sort in a subquery first
Row number in rows	`ROWNUM` pseudocolumn	None; use `ROW_NUMBER() OVER ()`	`ROWNUM > 1` never returns rows
No-table select	`SELECT 1 FROM DUAL`; from Release 23 the `FROM` clause may be omitted	`SELECT 1`	Keep `FROM DUAL` for code that must run on older Oracle
Null replacement	`NVL(a, b)`, `COALESCE`	`COALESCE(a, b)`	`NVL` is Oracle-only and takes exactly two arguments; prefer `COALESCE` for portability
Upsert	`MERGE INTO t USING s ON (...) WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...`	`INSERT ... ON CONFLICT (...) DO UPDATE`; `MERGE` since PostgreSQL 15	Oracle `MERGE` allows `DELETE WHERE` inside `WHEN MATCHED`; PostgreSQL 17 added `RETURNING` and `WHEN NOT MATCHED BY SOURCE` to `MERGE`
Sequences	`seq.NEXTVAL`, `seq.CURRVAL`	`nextval('seq')`, `currval('seq')`	Both can leave gaps; identity columns (`GENERATED ... AS IDENTITY`) exist in both
Set difference	`MINUS` (and `EXCEPT` as a synonym in the 23 docs)	`EXCEPT`	Use `MINUS` on older Oracle
Table alias	`FROM emp e` (no `AS` for tables)	`FROM emp AS e` or `emp e`	`AS` before a table alias is an error in Oracle
String concatenation	`\|\|`, NULL treated as empty	`\|\|` (NULL propagates), `concat()`	See chapter 8
Empty string	Is NULL	Is a value, distinct from NULL	Port `WHERE col = ''` with care
String aggregation	`LISTAGG`	`string_agg`	Different overflow behaviour
Top row per group	`ROW_NUMBER()` subquery	`SELECT DISTINCT ON (g) ... ORDER BY g, x DESC` or `ROW_NUMBER()`	`DISTINCT ON` is PostgreSQL only
Return generated values	`RETURNING col INTO :var` (PL/SQL, JDBC)	`RETURNING col` returns a result set	Different client handling
Boolean type	`BOOLEAN` data type is documented in the 23 release; older releases use `NUMBER(1)` or `CHAR(1)` flags	`boolean`	Check your Oracle release before relying on it in tables
Pagination in queries	`OFFSET n ROWS FETCH NEXT m ROWS ONLY`	same or `LIMIT m OFFSET n`	Standard form works in both
Comparison of strings	Depends on `NLS_COMP` / `NLS_SORT`	Depends on collation of the column/database	Case-insensitive search needs `LOWER()` or a configured collation
Identifier case	Unquoted names are folded to upper case	Unquoted names are folded to lower case	Quoted identifiers keep case and then must always be quoted
Procedural language	PL/SQL	PL/pgSQL	Similar ideas, different syntax
Recursive CTE	`WITH t(cols) AS (...)` (no `RECURSIVE`)	`WITH RECURSIVE t(cols) AS (...)`	Keyword differs

Chapter 11 - Common mistakes

SQL Mistakes

⚠ The ones that cost real time

**NOT IN with a subquery that can return NULL.** Returns nothing. Use NOT EXISTS.
**WHERE on the right table of a LEFT JOIN.** Silently makes it an inner join; put the filter in ON.
**BETWEEN on timestamps.** Misses the rest of the last day; use >= start AND < next_start.
Aggregating after a join fan-out. Sums double count; aggregate the child tables first.
**ROWNUM with ORDER BY in the same query (Oracle).** Rows are numbered before sorting; wrap the sorted query in a subquery.
**LAST_VALUE with the default frame.** It returns the current row; extend the frame.
**ORDER BY missing with LIMIT or FETCH FIRST.** Different rows on different runs.
**UNION where UNION ALL is meant.** Costs a sort and silently removes legitimate duplicates.
**Function on an indexed column in WHERE** (TRUNC(d), UPPER(name)). The plain index is not used; compare ranges or create a function-based index.
**COUNT(col) when you meant COUNT(*).** NULL values are not counted.

Chapter 12 - Go deeper

Long-form pages and what was checked

Topic	Page
Filtering, sorting, limiting	Filtering with WHERE, Sorting and Limiting
Aggregation and joins	Aggregation, Joining Tables
Subqueries and CTEs	Subqueries and CTEs
Window functions	Window Functions
Indexes and performance	Indexes and Performance
Writing data (MERGE, upsert)	Writing Data
Oracle PL/SQL	PL/SQL
SQL course index	SQL Tutorial

Version note

Checked on 2026-10-31: Oracle ROWNUM ordering, set operators (EXCEPT as a synonym of MINUS, ALL variants), the optional FROM clause from Release 23, empty string as NULL, || with NULL, LISTAGG overflow default and the MERGE ... DELETE WHERE clause against the Oracle Database 23 SQL Language Reference; PostgreSQL default NULL ordering, LIMIT/FETCH FIRST ... WITH TIES, DISTINCT ON and concat()/|| behaviour against the current PostgreSQL manual; MERGE in PostgreSQL 15 and its RETURNING and WHEN NOT MATCHED BY SOURCE additions in PostgreSQL 17 against the 15.0 and 17.0 release notes. The code block was run on SQLite 3.45 for syntax and results only. Not verified against a live Oracle or PostgreSQL server: the date-function rows (standard vendor behaviour, listed from experience; test on your version), the IN list limit of 1000, and the Oracle 12c start for FETCH FIRST. The Oracle BOOLEAN type was confirmed in the Release 23 data types list.

← Spring Annotations
All cheat sheets
Git →

LearningTree

SQL | Cheat Sheets