SQL Cheat Sheet for Interviews: Clauses, Joins, Windows, and NULL Traps

Quick Overview
A compact SQL cheat sheet built around one auditable fixture. It opens with a one-screen syntax table, then each pattern shows input rows, the query, a row-flow diagram, and the exact output for logical order, NULL-safe filters, time ranges, joins, counts, averages, and ranking ties.
This cheat sheet is for the moment you know the business question but need the safe SQL shape. Every pattern uses the same small PostgreSQL fixture: inspect the input rows, run the query, trace which rows survive, then compare the exact output.
The examples are intentionally small enough to calculate by hand. That is the point. A query is easier to trust when you can explain every output row and then add one counterexample for NULLs, ties, time boundaries, or join fanout.
SQL syntax at a glance
| Task | Syntax | Watch for |
|---|---|---|
| Select, filter, sort, cut | SELECT a, b FROM t WHERE a > 0 ORDER BY b DESC LIMIT 10; | LIMIT without ORDER BY returns arbitrary rows |
| Drop duplicate rows | SELECT DISTINCT a, b FROM t; | Applies to the whole selected row |
| Mix AND with OR | WHERE a = 1 AND (b = 2 OR c = 3) | AND binds before OR, so bracket the OR |
| Lists, ranges, patterns | a IN (1, 2), a BETWEEN 1 AND 9, name LIKE 'A%' | BETWEEN includes both ends |
| NULL tests | a IS NULL, a IS DISTINCT FROM b | a = NULL never matches |
| NULL defaults | COALESCE(a, 0), x / NULLIF(y, 0) | NULLIF turns a zero divisor into NULL |
| Aggregate per group | SELECT g, COUNT(*), SUM(v), AVG(v) FROM t GROUP BY g; | Every selected column must be grouped or aggregated |
| Filter groups | GROUP BY g HAVING COUNT(*) >= 2 | WHERE filters rows, HAVING filters groups |
| Count variants | COUNT(*), COUNT(a), COUNT(DISTINCT a) | Only COUNT(*) counts rows where a is NULL |
| If / else | CASE WHEN v > 100 THEN 'high' ELSE 'low' END | Without ELSE, unmatched rows get NULL |
| Conditional count | SUM(CASE WHEN v > 100 THEN 1 ELSE 0 END) | PostgreSQL also has COUNT(*) FILTER (WHERE v > 100) |
| Inner join | FROM orders o JOIN users u ON u.id = o.user_id | Duplicate keys multiply rows |
| Keep every left row | FROM users u LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid' | A filter on o in WHERE drops users with no paid order |
| Rows with no match | WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id) | NOT IN returns nothing if the subquery has a NULL |
| Stack results | SELECT a FROM t1 UNION ALL SELECT a FROM t2 | UNION also removes duplicates |
| Name a step | WITH s AS (SELECT g, SUM(v) AS total FROM t GROUP BY g) SELECT * FROM s; | The name lasts for one statement |
| Rank within groups | ROW_NUMBER() OVER (PARTITION BY g ORDER BY v DESC) | Filter on it in an outer query, not in WHERE |
| Previous / next row | LAG(v) OVER (ORDER BY d), LEAD(v) OVER (ORDER BY d) | The first row's LAG is NULL |
| Running total | SUM(v) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING) | Under the default frame, rows that tie on d get the same total |
| Dates | date_trunc('month', ts), ts >= '2026-03-01' AND ts < '2026-04-01' | Use half-open ranges for timestamps |
| Change rows | INSERT INTO t (a) VALUES (1); UPDATE t SET a = 2 WHERE id = 1; DELETE FROM t WHERE id = 1; | Run the WHERE as a SELECT first |
The table uses placeholder names (t, a, g, v). The sections below run the trickier patterns (NULLs, date ranges, join counts and ties) on the sample data and show the exact output.
The one-screen SQL execution map
SQL is written from SELECT, but PostgreSQL reasons about the query in a logical order closer to this:
| Stage | Main question |
|---|---|
FROM and JOIN | Which source rows can meet? |
WHERE | Which individual rows survive? |
GROUP BY | What does one aggregate row represent? |
HAVING | Which completed groups survive? |
| Window functions | What can be calculated across the grouped result without collapsing it again? |
SELECT | Which expressions become output columns? |
DISTINCT | Which complete projected rows are duplicates? |
ORDER BY and LIMIT | In what deterministic order are rows returned, and where is the cutoff? |
The exact optimizer plan is an implementation detail, but this logical map explains alias visibility, aggregation errors, and why HAVING is not a row filter.
Suppose the task is to return users with at least two video sessions Practice this question.
Input: sessions
| session_id | user_id | session_ts | content_type | duration_sec |
|---|---|---|---|---|
| 1 | 1 | 2026-03-01 09:00 | video | 100 |
| 2 | 1 | 2026-03-02 09:00 | article | 200 |
| 3 | 1 | 2026-03-03 09:00 | audio | 300 |
| 4 | 2 | 2026-03-01 12:00 | video | 150 |
| 5 | 2 | 2026-04-01 12:00 | article | 250 |
| 6 | 3 | 2026-03-02 14:00 | video | 180 |
| 7 | 3 | 2026-03-03 14:00 | video | NULL |
| 8 | 3 | 2026-04-02 14:00 | audio | 225 |
| 9 | 5 | 2026-04-03 16:00 | audio | 200 |
SELECT
user_id,
COUNT(*) FILTER (WHERE content_type = 'video') AS video_sessions
FROM sessions
GROUP BY user_id
HAVING COUNT(*) FILTER (WHERE content_type = 'video') >= 2
ORDER BY user_id;
WHERE would remove session rows before grouping. HAVING evaluates the completed user groups.Output
| user_id | video_sessions |
|---|---|
| 3 | 2 |
For a deeper explanation of alias visibility and windows, use the SQL order of operations guide.
Filters, NULLs, and half-open date ranges
Ordinary comparison operators can return UNKNOWN when either side is NULL. In a WHERE clause, only TRUE survives. PostgreSQL's IS DISTINCT FROM gives a two-valued, NULL-aware comparison when that is the business rule.
Input: users
| user_id | country |
|---|---|
| 1 | US |
| 2 | US |
| 3 | IN |
| 4 | IN |
| 5 | NULL |
The requirement is “return users whose country is not US, including users whose country is unknown.”
SELECT user_id, country
FROM users
WHERE country IS DISTINCT FROM 'US'
ORDER BY user_id;
Output
| user_id | country |
|---|---|
| 3 | IN |
| 4 | IN |
| 5 | NULL |
country <> 'US' would omit user 5 because NULL <> 'US' is UNKNOWN. That may be correct under a different requirement. Define the population before choosing the operator. The SQL not-equal guide has a full truth-table walkthrough.
For timestamps, prefer a half-open range. The task below means every session on March 1, 2, or 3, regardless of its time of day.
Input: sessions
| session_id | session_ts |
|---|---|
| 1 | 2026-03-01 09:00 |
| 2 | 2026-03-02 09:00 |
| 3 | 2026-03-03 09:00 |
| 4 | 2026-03-01 12:00 |
| 5 | 2026-04-01 12:00 |
| 6 | 2026-03-02 14:00 |
| 7 | 2026-03-03 14:00 |
| 8 | 2026-04-02 14:00 |
| 9 | 2026-04-03 16:00 |
SELECT COUNT(*) AS march_1_to_3_sessions
FROM sessions
WHERE session_ts >= TIMESTAMP '2026-03-01 00:00:00'
AND session_ts < TIMESTAMP '2026-03-04 00:00:00';
Output
| march_1_to_3_sessions |
|---|
| 6 |
Date literals and timestamp types follow dialect-specific timezone rules. In production, state the business timezone and normalize boundaries before comparison.
Joins, counts, and denominators
A left join is a population tool. Put a right-side predicate in ON when the output should retain left rows that have no qualifying match Practice this question.
Input: users
| user_id | country |
|---|---|
| 1 | US |
| 2 | US |
| 3 | IN |
| 4 | IN |
| 5 | NULL |
Input: relevant columns from sessions
| session_id | user_id | content_type |
|---|---|---|
| 1 | 1 | video |
| 2 | 1 | article |
| 3 | 1 | audio |
| 4 | 2 | video |
| 5 | 2 | article |
| 6 | 3 | video |
| 7 | 3 | video |
| 8 | 3 | audio |
| 9 | 5 | audio |
SELECT
u.user_id,
COUNT(s.session_id) AS video_sessions
FROM users AS u
LEFT JOIN sessions AS s
ON s.user_id = u.user_id
AND s.content_type = 'video'
GROUP BY u.user_id
ORDER BY u.user_id;
WHERE would be null-rejecting and remove users 4 and 5.Output
| user_id | video_sessions |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 0 |
| 5 | 0 |
The correct predicate placement depends on the intended population. If the task asks only for users with video sessions, an inner join or WHERE filter may be appropriate. Review SQL joins before adding DISTINCT to repair unexpected fanout.
Aggregate functions answer different denominator questions Practice this question. Audit them together on the raw session table.
Input: sessions duration and user columns
| session_id | user_id | duration_sec |
|---|---|---|
| 1 | 1 | 100 |
| 2 | 1 | 200 |
| 3 | 1 | 300 |
| 4 | 2 | 150 |
| 5 | 2 | 250 |
| 6 | 3 | 180 |
| 7 | 3 | NULL |
| 8 | 3 | 225 |
| 9 | 5 | 200 |
SELECT
COUNT(*) AS total_rows,
COUNT(duration_sec) AS known_durations,
COUNT(DISTINCT user_id) AS distinct_users,
ROUND(AVG(duration_sec), 2) AS avg_known_duration
FROM sessions;
AVG(duration_sec) measures only known durations. Replacing NULL with zero would define a different metric.Output
| total_rows | known_durations | distinct_users | avg_known_duration |
|---|---|---|---|
| 9 | 8 | 4 | 200.63 |
Do not use COALESCE(duration_sec, 0) unless the product definition says an unmeasured session lasted zero seconds. The SQL COUNT guide shows how these denominators change after joins.
Window functions and tie behavior
Window functions annotate rows without collapsing the result. Make tie behavior explicit Practice this question and add a deterministic output sort.
Input: user_counts
| user_id | session_count |
|---|---|
| 1 | 3 |
| 2 | 2 |
| 3 | 3 |
| 5 | 1 |
SELECT
user_id,
session_count,
ROW_NUMBER() OVER (
ORDER BY session_count DESC, user_id
) AS row_number,
RANK() OVER (
ORDER BY session_count DESC
) AS rank_with_gaps,
DENSE_RANK() OVER (
ORDER BY session_count DESC
) AS dense_rank
FROM user_counts
ORDER BY session_count DESC, user_id;
ORDER BY controls presentation, not the values already assigned.Output
| user_id | session_count | row_number | rank_with_gaps | dense_rank |
|---|---|---|---|---|
| 1 | 3 | 1 | 1 | 1 |
| 3 | 3 | 2 | 1 | 1 |
| 2 | 2 | 3 | 3 | 2 |
| 5 | 1 | 4 | 4 | 3 |
For running metrics, specify the intended frame. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW advances one physical row at a time. PostgreSQL's default RANGE behavior can include peers with the same ordering value. Add a stable secondary key when peer order matters.
FAQ
Why can a left join return more rows than the left table?
Each left row appears once for every matching right row. A one-to-many relationship therefore multiplies rows. Check the relationship before aggregating, and reduce the right side to the needed grain when multiplication is not intended.
Is NOT IN safe when the list contains NULL?
Positive IN can still find a true match, but a nonmatching value compared with a list containing NULL becomes UNKNOWN. NOT IN then removes that row in a WHERE clause. Prefer NOT EXISTS for nullable subquery keys.
Can I rely on a select alias in WHERE?
No in PostgreSQL, because WHERE is logically evaluated before SELECT. Use the original expression, a subquery, or a CTE. Some dialects allow aliases in other clauses, so verify portability instead of assuming it.
Does UNION always sort the result?
UNION removes duplicates, while UNION ALL keeps them. The engine may use a sort, hash, or another physical strategy to implement duplicate elimination. Only an outer ORDER BY guarantees output order.
What should I check before submitting a SQL answer?
State the output grain, test NULL and tie behavior, inspect one-to-many joins, use half-open timestamp boundaries, and add a deterministic sort when row order is part of the expected answer. Then compare the rendered output with the input rows rather than trusting a successful execution alone.
Comments (0)