A scannable PostgreSQL cheat sheet for NULLs, date ranges, joins, counts, denominators, windows, ties, and logical query order.

Author: PracHub

Published: 8/14/2026

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

August 14, 2026
25 min read
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.

Data ScientistFree

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

TaskSyntaxWatch for
Select, filter, sort, cutSELECT a, b FROM t WHERE a > 0 ORDER BY b DESC LIMIT 10;LIMIT without ORDER BY returns arbitrary rows
Drop duplicate rowsSELECT DISTINCT a, b FROM t;Applies to the whole selected row
Mix AND with ORWHERE a = 1 AND (b = 2 OR c = 3)AND binds before OR, so bracket the OR
Lists, ranges, patternsa IN (1, 2), a BETWEEN 1 AND 9, name LIKE 'A%'BETWEEN includes both ends
NULL testsa IS NULL, a IS DISTINCT FROM ba = NULL never matches
NULL defaultsCOALESCE(a, 0), x / NULLIF(y, 0)NULLIF turns a zero divisor into NULL
Aggregate per groupSELECT g, COUNT(*), SUM(v), AVG(v) FROM t GROUP BY g;Every selected column must be grouped or aggregated
Filter groupsGROUP BY g HAVING COUNT(*) >= 2WHERE filters rows, HAVING filters groups
Count variantsCOUNT(*), COUNT(a), COUNT(DISTINCT a)Only COUNT(*) counts rows where a is NULL
If / elseCASE WHEN v > 100 THEN 'high' ELSE 'low' ENDWithout ELSE, unmatched rows get NULL
Conditional countSUM(CASE WHEN v > 100 THEN 1 ELSE 0 END)PostgreSQL also has COUNT(*) FILTER (WHERE v > 100)
Inner joinFROM orders o JOIN users u ON u.id = o.user_idDuplicate keys multiply rows
Keep every left rowFROM 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 matchWHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)NOT IN returns nothing if the subquery has a NULL
Stack resultsSELECT a FROM t1 UNION ALL SELECT a FROM t2UNION also removes duplicates
Name a stepWITH s AS (SELECT g, SUM(v) AS total FROM t GROUP BY g) SELECT * FROM s;The name lasts for one statement
Rank within groupsROW_NUMBER() OVER (PARTITION BY g ORDER BY v DESC)Filter on it in an outer query, not in WHERE
Previous / next rowLAG(v) OVER (ORDER BY d), LEAD(v) OVER (ORDER BY d)The first row's LAG is NULL
Running totalSUM(v) OVER (ORDER BY d ROWS UNBOUNDED PRECEDING)Under the default frame, rows that tie on d get the same total
Datesdate_trunc('month', ts), ts >= '2026-03-01' AND ts < '2026-04-01'Use half-open ranges for timestamps
Change rowsINSERT 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:

StageMain question
FROM and JOINWhich source rows can meet?
WHEREWhich individual rows survive?
GROUP BYWhat does one aggregate row represent?
HAVINGWhich completed groups survive?
Window functionsWhat can be calculated across the grouped result without collapsing it again?
SELECTWhich expressions become output columns?
DISTINCTWhich complete projected rows are duplicates?
ORDER BY and LIMITIn 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_iduser_idsession_tscontent_typeduration_sec
112026-03-01 09:00video100
212026-03-02 09:00article200
312026-03-03 09:00audio300
422026-03-01 12:00video150
522026-04-01 12:00article250
632026-03-02 14:00video180
732026-03-03 14:00videoNULL
832026-04-02 14:00audio225
952026-04-03 16:00audio200
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;
Logical row flow from sessions through grouping and HAVING Nine session rows are grouped into four users, video rows are counted inside each group, and only user three passes the two-video threshold. 9 session rows4 distinct users GROUP BY uservideo counts: 1, 1, 2, 0one row per group HAVINGcount ≥ 2 user 32 videos
WHERE would remove session rows before grouping. HAVING evaluates the completed user groups.

Output

user_idvideo_sessions
32

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_idcountry
1US
2US
3IN
4IN
5NULL

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;
Row flow for a NULL-aware not-equal filter Five users enter the IS DISTINCT FROM filter. Two US rows are removed; two India rows and one unknown country row survive. 5 user rowsUS · US · IN · IN · NULL IS DISTINCT FROM USNULL is a distinct value 3 survivorsIN · IN · NULL
Use this form only when an unknown country belongs in the “not US” population.

Output

user_idcountry
3IN
4IN
5NULL

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_idsession_ts
12026-03-01 09:00
22026-03-02 09:00
32026-03-03 09:00
42026-03-01 12:00
52026-04-01 12:00
62026-03-02 14:00
72026-03-03 14:00
82026-04-02 14:00
92026-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';
Half-open timestamp range selecting three complete calendar days A timeline includes March first through every time on March third, then excludes the boundary at midnight on March fourth. include Mar 1 00:00 exclude Mar 4 00:00 6 selected sessionsevery timestamp in three full days
The exclusive next-day boundary avoids guessing the final fractional second of March 3.

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_idcountry
1US
2US
3IN
4IN
5NULL

Input: relevant columns from sessions

session_iduser_idcontent_type
11video
21article
31audio
42video
52article
63video
73video
83audio
95audio
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;
Row flow for preserving every user while counting video sessions Five user rows left join only to video sessions, unmatched users receive padding rows, and counting the session ID converts those padding rows to zero. 5 usersfixed population 4 video rowspredicate in ON LEFT JOINretain unmatched usersright columns become NULL COUNT(session_id)1 · 1 · 2 · 0 · 0NULL padding is not counted
Moving the video predicate to WHERE would be null-rejecting and remove users 4 and 5.

Output

user_idvideo_sessions
11
21
32
40
50

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_iduser_idduration_sec
11100
21200
31300
42150
52250
63180
73NULL
83225
95200
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;
Row flow for four aggregate denominator choices Nine session rows split into total rows, eight non-null durations, four distinct users, and an average over the eight known durations. 9 rowsone NULL duration COUNT(*)9 COUNT(duration)8 distinct users4 AVG known duration200.63
AVG(duration_sec) measures only known durations. Replacing NULL with zero would define a different metric.

Output

total_rowsknown_durationsdistinct_usersavg_known_duration
984200.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_idsession_count
13
22
33
51
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;
Row flow comparing row number, rank, and dense rank with a tie Four user counts are ordered descending. Users one and three tie at three sessions; row number separates them while rank and dense rank keep the tie. User counts3 · 3 · 2 · 1two rows tie ROW_NUMBER1 · 2 · 3 · 4 RANK1 · 1 · 3 · 4 DENSE_RANK1 · 1 · 2 · 3
Choose the function from the tie rule. The final ORDER BY controls presentation, not the values already assigned.

Output

user_idsession_countrow_numberrank_with_gapsdense_rank
13111
33211
22332
51443

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)