sqlANYinner-joinsubscriptions
sql
SELECT u.user_id, u.email, s.plan_code
FROM users u
JOIN subscriptions s ON s.user_id = u.user_id
WHERE s.status = 'active';`JOIN` without a modifier means `INNER JOIN` in most SQL dialects.
INNER, LEFT, RIGHT, and FULL OUTER JOIN Patterns
Practical SQL patterns for inner, outer, and reconciliation-style joins, including full outer join emulation.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
Common join patterns
Examples that show how output changes by join type.
Left join to find users without subscriptions
Find unmatched left-side rows by checking for `NULL` on the right.
sqlANYleft-joinanti-join
sql
SELECT u.user_id, u.email
FROM users u
LEFT JOIN subscriptions s ON s.user_id = u.user_id
WHERE s.user_id IS NULL;This is a classic anti-join pattern for missing relationships.
sqlANYfull-outer-joindata-diff
sql
SELECT a.sku AS a_sku, b.sku AS b_sku
FROM inventory_snapshot_a a
FULL OUTER JOIN inventory_snapshot_b b
ON b.sku = a.sku;Great for reconciliation and migration verification.
Emulate FULL OUTER JOIN where unsupported
Use a `LEFT JOIN` + unmatched rows from the other side via `UNION ALL`.
sqlANYfull-outer-joinunion-all
sql
SELECT a.id, b.id
FROM a
LEFT JOIN b ON b.id = a.id
UNION ALL
SELECT a.id, b.id
FROM b
LEFT JOIN a ON a.id = b.id
WHERE a.id IS NULL;Useful in engines without native `FULL OUTER JOIN`, such as some MySQL versions and SQLite.
More in SQL Joins
SQL Join Patterns and Pitfalls
Common SQL join mistakes, reusable templates, and correctness rules for production queries.
Join Performance, EXPLAIN, and Optimization
SQL join tuning with indexes, plan inspection, row reduction, and troubleshooting guidance.
Aggregation After Joins and NULL Handling
Counting, summing, pre-aggregating, and handling NULLs correctly after one-to-many and outer joins.
Semi-Joins and Anti-Joins
High-value EXISTS, IN, NOT EXISTS, and LEFT JOIN IS NULL patterns for membership and missing-row queries.
Many-to-Many Joins and Bridge Tables
Many-to-many SQL join patterns with bridge tables, aggregation, filtering, and index guidance.
Self Joins and Hierarchies
SQL self join examples for managers, duplicates, comparisons, and basic hierarchy modeling.