Code & computing / QUICK REFERENCE

SQL query essentials

Read, filter, join and summarize data without turning a reference lookup into an accidental database change.

32 entries

Select and sort

Choose columns: SELECT id, name FROM employees;
Explicit columns make the result contract clearer. [1][6]
Distinct values: SELECT DISTINCT dept_id FROM employees;
Removes duplicate result rows for the selected expression. [1][6]
Column alias: SELECT name AS employee_name FROM employees;
Labels the output column. [1][6]
Sort ascending: SELECT id, name FROM employees ORDER BY name, id;
A tie-breaker makes order more predictable. [1][6]
Sort descending: SELECT id, salary FROM employees ORDER BY salary DESC;
NULL ordering differs across database systems. [1][6]
Limit a result: SELECT id, name FROM employees ORDER BY id LIMIT 10;
LIMIT is not universal SQL syntax. [1][6]
Offset a result: SELECT id FROM employees ORDER BY id LIMIT 10 OFFSET 10;
Skips ten ordered rows; large offsets can be expensive. [1][6]
Inspect a query plan: EXPLAIN SELECT id FROM employees WHERE id = 1;
Plain EXPLAIN does not run the query like EXPLAIN ANALYZE does. [7]

Numeric threshold: SELECT id FROM employees WHERE salary > 50000;
Only rows where the condition is true pass. [1][6]
Combine conditions: SELECT id FROM employees WHERE dept_id = 2 AND salary > 50000;
Both conditions must be true. [1][6]
Either condition: SELECT id FROM employees WHERE dept_id = 1 OR dept_id = 2;
Parenthesize mixed AND/OR expressions. [1][6]
Set membership: SELECT id FROM employees WHERE dept_id IN (1, 2);
Equivalent to several equality alternatives for these literals. [1][6]
Inclusive range: SELECT id FROM employees WHERE salary BETWEEN 40000 AND 60000;
Both endpoints are included. [1][6]
Missing values: SELECT id FROM employees WHERE salary IS NULL;
Do not use = NULL. [1][6]
Present values: SELECT id FROM employees WHERE salary IS NOT NULL;
Tests for a known value. [1][6]
Prefix match: SELECT id FROM employees WHERE name LIKE 'A%';
% is a wildcard; case sensitivity is dialect/collation-dependent. [1][6]

Inner join: SELECT e.name, d.name FROM employees e JOIN departments d ON d.id = e.dept_id;
Keeps matches on both sides. [2][6]
Left join: SELECT e.name, d.name FROM employees e LEFT JOIN departments d ON d.id = e.dept_id;
Keeps every employee, including those without a matching department. [2][6]
Find unmatched rows: SELECT e.id FROM employees e LEFT JOIN departments d ON d.id = e.dept_id WHERE d.id IS NULL;
Assumes department IDs cannot be NULL. [2][6]
Count rows: SELECT COUNT(*) FROM employees;
Counts rows, including rows containing NULL fields. [3][6]
Count known salaries: SELECT COUNT(salary) FROM employees;
Ignores NULL salary values. [3][6]
Group totals: SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id;
Produces one result per department value. [3][6]
Filter grouped results: SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id HAVING COUNT(*) >= 3;
HAVING filters groups after aggregation. [3][6]
Average by group: SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;
AVG ignores NULL inputs. [3][6]

Fallback for NULL: SELECT name, COALESCE(salary, 0) FROM employees;
The fallback is a presentation choice, not a claim that unknown pay is zero. [5][6]
Conditional value: SELECT name, CASE WHEN salary >= 50000 THEN 'higher' ELSE 'other' END FROM employees;
CASE computes a value per row. [5][6]
Common table expression: WITH known AS (SELECT * FROM employees WHERE salary IS NOT NULL) SELECT COUNT(*) FROM known;
Names an intermediate query for readability. [4][6]
Rank within department: SELECT name, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, id) FROM employees;
Assigns distinct row numbers within each group. [4][6]
Running total: SELECT id, SUM(salary) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM employees;
Uses an explicit row frame. [4][6]
Keep rows while adding averages: SELECT name, AVG(salary) OVER (PARTITION BY dept_id) FROM employees;
A window aggregate does not collapse rows like GROUP BY. [4][6]
Combine and remove duplicates: SELECT dept_id FROM employees UNION SELECT id FROM departments;
UNION compares complete output rows. [4][6]
Combine without removing duplicates: SELECT dept_id FROM employees UNION ALL SELECT id FROM departments;
Keeps duplicates and often avoids deduplication work. [4][6]

Reserved placement, separate from reference controls. No ad network is active.

Updated 2026-10-19SQL · PostgreSQL-oriented SELECT patterns

cheatsheetatlas.com/sheets/sql/ · Updated 2026-10-19 · SQL · PostgreSQL-oriented SELECT patterns