SQL Joins and Subqueries

SQL•Permalink

Advanced relational query patterns for joins, subqueries, EXISTS, IN, set operations, and correlated queries.

View

Standard Detailed Compact

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.

Join with additional predicate

Add extra conditions to the join itself.

↗#

sqlANYjoinon-clause

sql

SELECT o.order_id, s.status
FROM orders o
JOIN shipments s
  ON s.order_id = o.order_id
 AND s.status = 'delivered';

Predicates in the ON clause affect join matching behavior.

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;

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;

This pattern keeps the outer query simpler when joining summarized data.

Subqueries

Nested query patterns.

Use a subquery in WHERE

Filter rows based on results from another query.

↗#

sqlANYsubquerywhere

sql

SELECT *
FROM products
WHERE category_id IN (
  SELECT category_id
  FROM categories
  WHERE is_active = TRUE
);

Subqueries are useful when one query determines the filter set for another.

Scalar subquery

Return a single value from a nested query.

↗#

sqlANYsubqueryscalar

sql

SELECT employee_id, salary,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

A scalar subquery must return at most one row.

Correlated subquery

Reference outer query columns inside the subquery.

↗#

sqlANYsubquerycorrelated

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
);

Correlated subqueries execute relative to each outer row.

Subquery in FROM

Use a derived table as a query source.

↗#

sqlANYsubqueryderived-table

sql

SELECT q.department_id, q.avg_salary
FROM (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
) q;

Derived tables let you treat nested query results as temporary relations.

EXISTS and Set Operations

Semi-joins, anti-joins, and unions.

Filter with EXISTS

Return rows when a related row exists.

↗#

sqlANYexistssubquery

sql

SELECT c.customer_id
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

EXISTS is often efficient for existence checks.

Filter with NOT EXISTS

Return rows with no related matches.

↗#

sqlANYnot-existssubquery

sql

SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

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;

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;

UNION ALL is usually faster than UNION because it skips duplicate elimination.

Find common rows with INTERSECT

Return rows present in both queries.

↗#

sqlANYintersectset-operations

sql

SELECT user_id FROM newsletter_subscribers
INTERSECT
SELECT user_id FROM paying_customers;

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;

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.

Recommended next

No recommendations yet.

Browse all sheets →