The clauses you reach for most often, in readable query order.
SELECTChoose output columns or expressions.SELECT id, email, created_atFROMChoose the input relation.FROM users uJOINCombine related relations before filtering.LEFT JOIN orders o ON o.user_id = u.idWHEREFilter individual rows.WHERE u.status = 'active'GROUP BYCollapse rows into groups for aggregates.GROUP BY u.idHAVINGFilter groups after aggregation.HAVING COUNT(o.id) >= 2Useful mental model when aliases, aggregates, windows, or filters behave unexpectedly.
1 · FROM/JOINBuild the row source.FROM … JOIN … ON …2 · WHERERemove rows before grouping.WHERE …3 · GROUP BYForm groups.GROUP BY …4 · HAVINGRemove groups.HAVING …5 · SELECTCompute projected expressions.SELECT …6 · DISTINCTDeduplicate projected rows.DISTINCTSQL uses three-valued logic: NULL is unknown, not an ordinary value.
CompareNormal scalar comparisons.price >= 100 AND status <> 'cancelled'RangeInclusive lower and upper bounds.score BETWEEN 60 AND 80SetMatch any listed value.status IN ('new', 'paid', 'shipped')Pattern% = any run, _ = one character.email LIKE '%@example.com'NULLUse IS NULL / IS NOT NULL, never = NULL.deleted_at IS NULLNegationParenthesize mixed AND/OR expressions.NOT (status = 'x' OR status = 'y')Aggregate functions collapse multiple input rows into one result per group.
COUNT(*)Count rows, including rows whose columns contain NULL.COUNT(*)COUNT(col)Count non-NULL values in one expression.COUNT(paid_at)COUNT(DISTINCT)Count unique non-NULL values.COUNT(DISTINCT user_id)SUM / AVGTotal or mean of non-NULL numeric values.SUM(total), AVG(total)MIN / MAXSmallest or largest non-NULL value.MIN(created_at), MAX(created_at)HAVINGFilter aggregate groups, not source rows.HAVING SUM(total) > 1000Choose the join from the row-preservation requirement, then verify cardinality.
INNERKeep only matching rows from both sides.FROM users u JOIN orders o ON o.user_id = u.idLEFTKeep every left row; unmatched right columns become NULL.FROM users u LEFT JOIN orders o ON o.user_id = u.idCROSSCartesian product; useful intentionally, dangerous accidentally.FROM sizes CROSS JOIN colorsComposite keyJoin on the complete business/key relationship.ON a.tenant_id = b.tenant_id AND a.id = b.id1:N effectA parent repeats once per matching child.COUNT(*) may grow after a joinLEFT + filterA right-side WHERE condition can effectively turn LEFT JOIN into INNER JOIN.Put match-only condition in ON when left rows must surviveCombine compatible result sets vertically; column counts and compatible types must align.
UNIONCombine and remove duplicates.SELECT email FROM leads UNION SELECT email FROM usersUNION ALLCombine and preserve duplicates; usually cheaper than UNION.SELECT id FROM a UNION ALL SELECT id FROM bINTERSECTRows present in both results where supported.SELECT id FROM a INTERSECT SELECT id FROM bEXCEPTRows in the first result but not the second where supported.SELECT id FROM expected EXCEPT SELECT id FROM actualUse scalar subqueries for one value and EXISTS when the question is whether a related row exists.
ScalarSubquery must return at most one row/value.WHERE price > (SELECT AVG(price) FROM products)INCompare against a one-column result set.WHERE user_id IN (SELECT id FROM vip_users)EXISTSTrue as soon as a correlated match exists.WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)NOT EXISTSRobust anti-match pattern, including NULL-sensitive cases.WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)CorrelatedInner query refers to the current outer row.… WHERE o.user_id = u.idName intermediate result sets to make multi-step SQL easier to reason about and test.
WITHDefine a named result for the following statement.WITH paid AS (SELECT * FROM orders WHERE status = 'paid') SELECT * FROM paidMultipleChain readable stages separated by commas.WITH a AS (…), b AS (SELECT … FROM a) SELECT … FROM bColumnsOptionally name CTE output columns explicitly.WITH totals(user_id, amount) AS (…)Small expressions that make reporting and validation queries far more useful.
CASEConditional expression.CASE WHEN score >= 80 THEN 'high' ELSE 'other' ENDCOALESCEFirst non-NULL expression.COALESCE(display_name, email, 'unknown')NULLIFReturn NULL when two expressions are equal; handy for safe division.revenue / NULLIF(quantity, 0)CASTConvert to a target SQL type.CAST(total AS DECIMAL(12,2))Windows calculate across related rows without collapsing them like GROUP BY does.
ROW_NUMBERUnique sequence inside each partition; ties still receive different numbers.ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)RANKTies share rank and leave gaps.RANK() OVER (ORDER BY total DESC)DENSE_RANKTies share rank without gaps.DENSE_RANK() OVER (ORDER BY total DESC)Top per groupRank inside a CTE/subquery, then filter the generated rank outside.WITH x AS (SELECT …, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC) rn FROM …) SELECT * FROM x WHERE rn = 1Running totals, comparisons with adjacent rows, and partition-level statistics.
Running sumAccumulate in deterministic order.SUM(total) OVER (PARTITION BY user_id ORDER BY created_at, id ROWS UNBOUNDED PRECEDING)Partition totalAggregate per partition while retaining detail rows.SUM(total) OVER (PARTITION BY user_id)LAGRead a previous row's value in window order.LAG(total) OVER (PARTITION BY user_id ORDER BY created_at)LEADRead a following row's value.LEAD(created_at) OVER (PARTITION BY user_id ORDER BY created_at)Moving avgExplicit ROWS frame avoids surprising peer/range behavior.AVG(total) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)For destructive statements, prove the target rows with SELECT first and use transactions when appropriate.
INSERTInsert explicit columns; avoid relying on table column order.INSERT INTO users (id, email) VALUES (1, 'a@example.com')INSERT … SELECTInsert a query result.INSERT INTO archive (id, email) SELECT id, email FROM users WHERE status = 'inactive'UPDATEChange matching rows; verify the WHERE predicate first.UPDATE users SET status = 'inactive' WHERE last_login < '2025-01-01'DELETERemove matching rows; missing WHERE means all rows.DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMPConstraints make invalid states impossible at the database boundary instead of merely unlikely.
PRIMARY KEYUnique, non-NULL row identity.id INTEGER PRIMARY KEYFOREIGN KEYRequire referenced parent data.FOREIGN KEY (user_id) REFERENCES users(id)UNIQUEReject duplicate key combinations under engine NULL semantics.UNIQUE (tenant_id, external_id)NOT NULLReject missing values.email VARCHAR(320) NOT NULLCHECKEnforce a row-level predicate where supported/enforced.CHECK (total >= 0)DEFAULTValue used when the column is omitted.created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMPGroup dependent writes so they commit together or roll back together; begin syntax varies slightly by engine.
PostgreSQLExplicit transaction block.BEGIN; … COMMIT; -- or ROLLBACKMySQLExplicit transaction block for transactional storage engines.START TRANSACTION; … COMMIT;SQLiteBegin a transaction; locking mode can be qualified.BEGIN; … COMMIT;SQL ServerT-SQL transaction block.BEGIN TRANSACTION; … COMMIT TRANSACTION;PG / MySQL / SQLiteCreate a partial rollback point inside a transaction.SAVEPOINT before_step; … ROLLBACK TO before_stepSQL Server savepointT-SQL uses SAVE TRANSACTION and ROLLBACK TRANSACTION.SAVE TRANSACTION before_step; … ROLLBACK TRANSACTION before_stepIndexes are workload-specific. Verify plans and timings instead of assuming an index makes a query faster.
IndexCommon B-tree index for selective lookup/order patterns.CREATE INDEX idx_orders_user_created ON orders (user_id, created_at)Composite orderColumn order matters; align it with real predicates/orderings.(tenant_id, status, created_at)PostgreSQLInspect planner output; ANALYZE executes the statement.EXPLAIN (ANALYZE, BUFFERS) SELECT …MySQLInspect access path/plan.EXPLAIN SELECT …SQLiteInspect high-level query plan.EXPLAIN QUERY PLAN SELECT …SQL ServerUse actual/estimated execution plans in SSMS or SET SHOWPLAN options.Inspect plan operators, estimates, scans/seeksCompact queries for validating uniqueness, completeness, referential integrity, and reconciliation.
DuplicatesFind keys that occur more than once.SELECT external_id, COUNT(*) c FROM t GROUP BY external_id HAVING COUNT(*) > 1Missing requiredFind unexpected NULLs/blanks.SELECT * FROM t WHERE email IS NULL OR TRIM(email) = ''OrphansFind children without a parent.SELECT c.* FROM child c LEFT JOIN parent p ON p.id = c.parent_id WHERE p.id IS NULLReconcile totalsCompare independently computed aggregates at the same grain.GROUP BY business_date, currencyUnexpected domainSurface values outside an allowed set.SELECT status, COUNT(*) FROM t GROUP BY statusFreshnessCheck latest expected event/load time.SELECT MAX(loaded_at) FROM warehouse_tableAlways pair pagination with deterministic ORDER BY. The row-limiting syntax is not uniform.
PostgreSQLLIMIT/OFFSET is common; FETCH is also supported.ORDER BY id LIMIT 20 OFFSET 40MySQLLIMIT with OFFSET.ORDER BY id LIMIT 20 OFFSET 40SQLiteLIMIT with OFFSET.ORDER BY id LIMIT 20 OFFSET 40SQL ServerTOP for simple limits; OFFSET/FETCH for paging requires ORDER BY.ORDER BY id OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLYConflict handling and returning changed rows are vendor-specific—keep them isolated from portable SQL.
PostgreSQL upsertHandle a unique/exclusion conflict with ON CONFLICT.INSERT INTO users(id,email) VALUES (1,'a@x') ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.emailSQLite upsertSQLite also supports ON CONFLICT upsert syntax.INSERT INTO users(id,email) VALUES (1,'a@x') ON CONFLICT(id) DO UPDATE SET email = excluded.emailMySQL upsertUse ON DUPLICATE KEY UPDATE for duplicate unique/primary keys.INSERT INTO users(id,email) VALUES (1,'a@x') ON DUPLICATE KEY UPDATE email = 'a@x'PostgreSQL rowsRETURNING can return inserted/updated/deleted values.INSERT INTO users(email) VALUES ('a@x') RETURNING idSQLite rowsModern SQLite supports RETURNING on top-level DML.UPDATE users SET active = 0 WHERE id = 1 RETURNING idSQL Server rowsOUTPUT exposes inserted/deleted pseudo-tables.UPDATE users SET active = 0 OUTPUT inserted.id WHERE id = 1Date arithmetic is one of the fastest places for otherwise-portable SQL to diverge.
PostgreSQLInterval arithmetic.CURRENT_DATE + INTERVAL '7 days'MySQLDATE_ADD / DATE_SUB family.DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY)SQLiteDate/time functions use modifier strings.date('now', '+7 days')SQL ServerDATEADD / DATEDIFF family.DATEADD(day, 7, CAST(GETDATE() AS date))Portable ideaStore/compare timestamps at a clear timezone/precision and test boundary conversions explicitly.UTC instant vs local business date are different conceptsBasic string functions overlap, but aggregating many rows into one string uses different names.
PostgreSQLAggregate strings with a delimiter.STRING_AGG(name, ', ' ORDER BY name)MySQLGROUP_CONCAT supports ordering/separators.GROUP_CONCAT(name ORDER BY name SEPARATOR ', ')SQLitegroup_concat is the native name; current SQLite also documents string_agg as an alias.group_concat(name, ', ')SQL ServerSTRING_AGG combines rows; ordering uses WITHIN GROUP.STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name)Interview prompt: find business-key values that occur more than once.
AnswerGroup by the key and keep only groups whose row count is greater than one.SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;WhyGROUP BY creates one group per key; HAVING filters after aggregation.Use the real uniqueness rule: one column or the full composite business key.GROUP BY shows duplicate keys; this pattern returns the actual offending rows.
AnswerCount rows inside each key partition, then filter in an outer query.WITH x AS (
SELECT u.*, COUNT(*) OVER (PARTITION BY email) AS copies
FROM users u
)
SELECT * FROM x WHERE copies > 1;Why outer query?Window values are produced after WHERE, so filter them one query level later.CTE / derived table → compute window result → outer WHEREIdentify exactly which rows are duplicates before running dialect-specific DELETE syntax.
PreviewRank each duplicate group; rn = 1 is the survivor and rn > 1 are removal candidates.WITH ranked AS (
SELECT id, ROW_NUMBER() OVER (
PARTITION BY email ORDER BY updated_at DESC, id DESC
) AS rn
FROM users
)
SELECT id FROM ranked WHERE rn > 1;Tie-breakerORDER BY must choose one deterministic survivor.ORDER BY updated_at DESC, id DESCReturn the newest order, event, status, or version for every entity.
AnswerNumber rows newest-first inside each entity and keep row 1.WITH ranked AS (
SELECT o.*, ROW_NUMBER() OVER (
PARTITION BY customer_id ORDER BY created_at DESC, id DESC
) AS rn
FROM orders o
)
SELECT * FROM ranked WHERE rn = 1;PitfallMAX(created_at) alone returns only a timestamp and can match multiple tied rows.Add a stable secondary key such as id DESC.Classic interview task: return the second distinct salary, score, or amount.
AnswerDENSE_RANK ranks equal values together and does not leave gaps between distinct values.WITH r AS (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS pos
FROM employees
)
SELECT DISTINCT salary FROM r WHERE pos = 2;AlternativeFor exactly the second value, compare against the maximum.SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);Generalize the second-highest problem while handling ties correctly.
AnswerRank distinct value groups and filter by the requested rank.WITH r AS (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS pos
FROM employees
)
SELECT DISTINCT salary FROM r WHERE pos = :n;ROW_NUMBER?Use ROW_NUMBER only when every row should occupy a different position; use DENSE_RANK for distinct-value ranking.ties: ROW_NUMBER → separate positions · DENSE_RANK → same positionReturn the top three products per category, employees per team, or scores per user.
AnswerPartition by the group, order by the metric, then filter the generated row number.WITH r AS (
SELECT p.*, ROW_NUMBER() OVER (
PARTITION BY category_id ORDER BY revenue DESC, id
) AS rn
FROM products p
)
SELECT * FROM r WHERE rn <= 3;Include tiesUse RANK or DENSE_RANK when tied metric values should all qualify.RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC)Find customers without orders, users without profiles, or test cases without executions.
AnswerUse an anti-semi pattern with NOT EXISTS.SELECT c.*
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);Why not NOT IN?NOT EXISTS avoids the NULL trap that can make NOT IN evaluate to unknown.Prefer NOT EXISTS when the subquery key may contain NULL.Data-quality task: locate child records whose referenced parent does not exist.
AnswerStart from the child table and reject rows that have a matching parent.SELECT o.*
FROM orders o
WHERE o.customer_id IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM customers c WHERE c.id = o.customer_id
);QA useUseful after imports, disabled constraints, migrations, or ETL loads.Expected result in an integrity check: 0 rows.Find rows present in the expected result but missing or different in the actual result.
One directionEXCEPT returns distinct rows from the left result that are absent from the right result.SELECT id, status, amount FROM expected
EXCEPT
SELECT id, status, amount FROM actual;Full diffRun EXCEPT in both directions to detect both missing and unexpected rows.expected EXCEPT actual
UNION ALL
actual EXCEPT expectedCalculate cumulative balance, revenue, defects, or executions without collapsing rows.
AnswerUse SUM as a window aggregate ordered by the sequence of events.SELECT account_id, occurred_at, amount,
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY occurred_at, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM ledger;Why ROWS?An explicit ROWS frame avoids peer-group surprises when several rows share the same ordering value.Use a deterministic ORDER BY, usually timestamp + primary key.Detect a change in status, price, count, or measurement between consecutive events.
AnswerLAG exposes a value from the previous row in the window ordering.WITH x AS (
SELECT e.*, LAG(status) OVER (
PARTITION BY entity_id ORDER BY occurred_at, id
) AS previous_status
FROM events e
)
SELECT * FROM x
WHERE previous_status IS NOT NULL
AND status <> previous_status;First rowLAG returns NULL when no previous row exists unless a default is supplied.Handle the first row explicitly when NULL is also a valid business value.Detect missing IDs, invoice numbers, versions, or ordered event sequence values.
AnswerCompare each sequence value with its predecessor.WITH x AS (
SELECT sequence_no,
LAG(sequence_no) OVER (ORDER BY sequence_no) AS prev_no
FROM events
)
SELECT prev_no + 1 AS gap_start, sequence_no - 1 AS gap_end
FROM x
WHERE sequence_no > prev_no + 1;AssumptionThis checks numeric continuity, not whether every generated identifier must legally exist.Define the expected sequence rule before treating a gap as a defect.Produce pass/fail/error counts or status KPIs without multiple scans or queries.
AnswerTurn each condition into 1 or 0 and aggregate it.SELECT
SUM(CASE WHEN status = 'passed' THEN 1 ELSE 0 END) AS passed,
SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed,
SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END) AS errors
FROM test_results;By groupAdd GROUP BY to calculate the same metrics per build, suite, team, or day.GROUP BY build_idFind users or entities with no qualifying event in a given period without dialect-specific date arithmetic.
AnswerPass the cutoff as a parameter and use NOT EXISTS against qualifying activity.SELECT u.*
FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM events e
WHERE e.user_id = u.id
AND e.occurred_at >= :cutoff
);Why parameter?Date-add/subtract syntax differs by dialect; computing the cutoff outside the query keeps the core pattern portable.:cutoff = application/test parameterFind parent rows that multiply after a join because the relationship is one-to-many or the join key is incomplete.
AnswerCount joined rows per expected parent key and inspect groups with more than one match.SELECT p.id, COUNT(*) AS joined_rows
FROM parents p
JOIN children c ON c.parent_id = p.id
GROUP BY p.id
HAVING COUNT(*) > 1;InterpretationMultiple rows may be correct for 1:N; the defect is assuming 1:1 or omitting part of a composite join key.Check relationship cardinality before adding DISTINCT.Use the last seen sort key instead of scanning an ever-growing OFFSET.
Core predicateContinue strictly after the last row using the same deterministic sort keys.WHERE created_at < :last_created_at
OR (created_at = :last_created_at AND id < :last_id)
ORDER BY created_at DESC, id DESCRow capApply the dialect's normal row-limiting syntax after the predicate.PostgreSQL/MySQL/SQLite: LIMIT 50 · SQL Server: TOP / OFFSET-FETCHCalculate each group's contribution while keeping one result row per group.
AnswerAggregate each group, then window-sum those aggregate results for the denominator.SELECT category_id,
SUM(amount) AS amount,
100.0 * SUM(amount)
/ NULLIF(SUM(SUM(amount)) OVER (), 0) AS pct_total
FROM sales
GROUP BY category_id;Zero guardNULLIF prevents division by zero when the total is zero.NULLIF(total, 0)