sql
SELECT o.order_id, s.status
FROM orders o
JOIN shipments s
ON s.order_id = o.order_id
AND s.status = 'delivered';Notes
Predicates in the ON clause affect join matching behavior.
SQL Joins and Subqueries
Advanced relational query patterns for joins, subqueries, EXISTS, IN, set operations, and correlated queries.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
Join Conditions and Filters
Practical join patterns with predicates.
Find missing related rows
Use LEFT JOIN plus NULL filtering to find unmatched rows.
sqlANYleft-joinanti-join
sql
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;Notes
This is a common anti-join pattern.
Join to an aggregated subquery
Pre-aggregate a table and join the result.
sqlANYjoinsubqueryaggregation
sql
SELECT c.customer_id, x.total_spent
FROM customers c
JOIN (
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
) x ON x.customer_id = c.customer_id;Notes
This pattern keeps the outer query simpler when joining summarized data.
Subqueries
Nested query patterns.
sql
SELECT *
FROM products
WHERE category_id IN (
SELECT category_id
FROM categories
WHERE is_active = TRUE
);Notes
Subqueries are useful when one query determines the filter set for another.
sql
SELECT employee_id, salary,
(SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;Notes
A scalar subquery must return at most one row.
sql
SELECT e.employee_id, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.department_id = e.department_id
);Notes
Correlated subqueries execute relative to each outer row.
sql
SELECT q.department_id, q.avg_salary
FROM (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) q;Notes
Derived tables let you treat nested query results as temporary relations.
EXISTS and Set Operations
Semi-joins, anti-joins, and unions.
sql
SELECT c.customer_id
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);Notes
EXISTS is often efficient for existence checks.
sql
SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);Notes
NOT EXISTS is a robust anti-join approach.
Combine result sets with UNION
Merge distinct rows from two compatible queries.
sqlANYunionset-operations
sql
SELECT email FROM customers
UNION
SELECT email FROM leads;Notes
UNION removes duplicates. Use UNION ALL to preserve them.
Combine result sets with UNION ALL
Merge all rows without deduplication.
sqlANYunion-allset-operations
sql
SELECT event_type FROM web_events
UNION ALL
SELECT event_type FROM mobile_events;Notes
UNION ALL is usually faster than UNION because it skips duplicate elimination.
sql
SELECT user_id FROM newsletter_subscribers
INTERSECT
SELECT user_id FROM paying_customers;Notes
INTERSECT behaves like set intersection in SQL dialects that support it.
Subtract rows with EXCEPT
Return rows from the first query not present in the second.
sqlANYexceptset-operations
sql
SELECT user_id FROM all_users
EXCEPT
SELECT user_id FROM banned_users;Notes
Some dialects use MINUS instead of EXCEPT.
More in SQL
SQL Indexing and Performance
Query tuning patterns with EXPLAIN, indexing, keyset pagination, deduplication, and optimization-oriented SQL recipes.
SQL Window Functions and CTEs
Use CTEs, recursive queries, ranking, running totals, partitions, and lag/lead patterns for analytical SQL.
SQL DDL and DML
Create and change schema objects, then insert, update, delete, merge, and manage transactional data changes.
SQL Functions and Aggregation
Reference for string, date, numeric, conditional, null-handling, and aggregate SQL functions.
SQL Cheat Sheet
Core SQL syntax and patterns for querying, filtering, sorting, joining, grouping, and modifying data.