--- name: sql-joins description: > Write, review and debug SQL joins that return the right rows at the right grain across PostgreSQL, MySQL, SQLite, SQL Server and other engines. Use it when an agent is asked to combine two or more tables, when a query uses JOIN, LEFT JOIN, INNER JOIN, OUTER JOIN, USING or ON, when a result has missing rows, duplicate rows or an inflated SUM or COUNT after a join, when someone asks to "join these tables", "match customers to orders", "find records with no match", "why are my totals doubled" or "why did my LEFT JOIN drop rows", and when writing an anti-join, self-join or many-to-many join. Do NOT use it for single-table queries with no join, for stacking rows with UNION or UNION EXCEPT INTERSECT set operators, for subquery or CTE structure (see subqueries-and-ctes), for running totals and per-partition ranking (see window-functions), or for schema and foreign-key design. license: Free to use and adapt under CC BY 4.0, with credit to Afeez Adebayo. Link afeezadebayo.com, https://www.linkedin.com/in/afeez-adebayo/ and this skill's page at https://afeezadebayo.com/skills-md/data/sql-querying/sql-joins/. --- # SQL joins ## When to use this, and when not to Use this skill when the task is to combine rows from two or more tables on a related key, or to fix a join that has gone wrong. The clearest triggers are a query that already contains `JOIN`, `LEFT JOIN`, `ON` or `USING`; a request to match one table to another; and a result whose row count, missing rows or totals look wrong after tables were combined. A join is also the right tool for finding records in one table that have no match in another, for comparing a table to itself, and for attaching reference data to a fact table. Do not use it for a query that reads a single table, for stacking two result sets with `UNION`, `EXCEPT` or `INTERSECT`, for deciding how to nest subqueries or common table expressions (that is `subqueries-and-ctes`), or for per-row running totals and ranking (that is `window-functions`). It does not design schemas or foreign keys. It writes and repairs the join itself, and it leaves the business decision to a person. ## Inputs the agent must have before starting Ask for anything on this list that is missing. Do not assume cardinality, because the whole correctness of a join turns on it. 1. The tables involved, their columns and the type of each join key. A join between a text key and an integer key, or between two different collations, will either error or match nothing. 2. The join key or keys, and the relationship between the tables on those keys: one-to-one, one-to-many, or many-to-many. If the person does not know, treat it as many-to-many until proven otherwise and verify with a grouped count (step 2 below). 3. The grain of the result they want, stated as "one row per ...". This single sentence decides whether a join has multiplied rows by accident. 4. The SQL engine and version. Support for `FULL OUTER JOIN`, `RIGHT JOIN`, `LATERAL`, `USING` and update-with-join syntax differs by engine (see the appendix). 5. Whether the join keys can contain `NULL`, and whether unmatched rows must be kept. 6. The actual question the result feeds. "Total revenue per customer" and "list of customers and their orders" need different joins and different grains. If the data is live and the join feeds an `UPDATE`, `DELETE` or `MERGE`, also confirm who approves the change. Default to a `SELECT` that previews the affected rows; never run a destructive statement without explicit human confirmation. ## The workflow, step by step Follow these in order. Each step has a decision rule, not a suggestion. ### 1. Write down the target grain first Before any SQL, write one sentence: "The result has one row per ___." Everything after this checks against that sentence. If you cannot state the grain, stop and ask. ### 2. Establish each table's grain and the join cardinality For each table, confirm how many rows share a given join key: ```sql SELECT join_key, COUNT(*) AS n FROM the_table GROUP BY join_key HAVING COUNT(*) > 1 LIMIT 10; ``` If this returns rows, that table has many rows per key. Decision rule: if both sides return rows here, the join is many-to-many and will produce a cross product per key. If exactly one side does, the join is one-to-many and will multiply the "one" side. Only if neither side returns rows is the join one-to-one and safe to treat as a simple lookup. ### 3. Choose the join type by intent | You need | Use | Note | |---|---|---| | Only rows that match in both tables | `INNER JOIN` | Unmatched rows on either side are dropped | | Every row of the primary table, with matches where they exist | `LEFT JOIN` | Make the primary table the left table | | Rows from both tables even when unmatched | `FULL OUTER JOIN` | Reconciliation; not in MySQL or old SQLite (see appendix) | | Every combination on purpose | `CROSS JOIN` | A calendar times a category list, for example | Decision rule: never use `RIGHT JOIN`. Rewrite it as a `LEFT JOIN` with the tables swapped, which reads in the same order as the result. A missing `ON` clause silently becomes a cross product, so every inner or outer join must carry a join condition. ### 4. Put join conditions in ON, row filters in WHERE `ON` decides which rows match. `WHERE` decides which rows survive into the result, and it runs after the join has built its rows. Decision rule for outer joins: a condition on the nullable (outer) side belongs in `ON` when it should only limit which rows match, and in `WHERE` when it should remove rows from the result. Put a filter on the right table of a `LEFT JOIN` in `WHERE` and you drop every unmatched left row, because its right-side columns are `NULL` and `NULL` fails the test. The query then returns an inner join while still reading as a left join. If you want all left rows kept, move that condition into `ON`. ### 5. Guard against fan-out before aggregating When a one-to-many join feeds `SUM`, `COUNT` or `AVG`, the aggregate counts the "one" side once per matching "many" row. An order with three line items has its order total added three times. Decision rule: if step 2 found a one-to-many or many-to-many relationship and the query aggregates, do one of these before trusting the number: - Aggregate the many side down to the target grain in a subquery or CTE, then join the already-collapsed result. - Use `WHERE EXISTS (...)` when you only need to know a match is present, not to add its values. - Use `COUNT(DISTINCT key)` when you are counting the "one" side through a fanned-out join. Then verify: the row count of the final result must equal the count of distinct keys at the target grain. If it is higher, fan-out is still in the query. ### 6. Write anti-joins NULL-safely To find rows in A with no match in B, use one of: ```sql -- Preferred: NULL-safe and reads as intent SELECT a.* FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id); -- Equivalent: the LEFT JOIN anti-join idiom SELECT a.* FROM a LEFT JOIN b ON b.a_id = a.id WHERE b.a_id IS NULL; ``` Decision rule: never write `NOT IN (SELECT col FROM b)` when `col` can be `NULL`. A single `NULL` in the subquery makes the whole `NOT IN` evaluate to unknown and the query returns zero rows, with no error. `NOT EXISTS` handles `NULL` correctly. ### 7. Join on the full key Join on every column that defines the relationship. A join on part of a composite key fans out across the missing column. If two tables relate on `(tenant_id, user_id)`, joining on `user_id` alone mixes tenants. ### 8. Handle self-joins deliberately Alias both copies of the table. For a hierarchy such as employee to manager, use a `LEFT JOIN` so the root row, whose parent key is `NULL`, is kept rather than dropped. For comparing rows within a group, add `a.id < b.id` to return each pair once instead of twice and to exclude a row paired with itself. ### 9. Make the join perform Index the join key on at least the table being looked up, so the planner can use an index join rather than scanning. The planner chooses join order, and the number of orderings grows factorially with the number of tables, so join only the tables the question needs. Push selective filters as early as the logic allows. ### 10. Verify before handing over Run `EXPLAIN` (or `EXPLAIN ANALYZE` where safe) and confirm the join uses an index where one exists. Check the final row count against the target grain from step 1. Test at least one unmatched case and one `NULL`-key case. For a destructive join, show the `SELECT` preview and its row count, and stop for confirmation. ## Worked example, end to end Three tables: `customers` (one row per customer), `orders` (one row per order, many per customer), `order_items` (one row per line, many per order). The question: total revenue and order count per customer for 2026, including customers who placed no order. A first attempt looks reasonable and is wrong twice over: ```sql SELECT c.id, c.name, SUM(oi.line_total) AS revenue, COUNT(o.id) AS orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.id LEFT JOIN order_items oi ON oi.order_id = o.id WHERE o.created_at >= '2026-01-01' GROUP BY c.id, c.name; ``` Two bugs. First, `WHERE o.created_at >= '2026-01-01'` sits on the outer side of the `LEFT JOIN`, so every customer with no 2026 order is dropped: the query silently became an inner join, and the "including customers who placed no order" requirement is lost. Second, `COUNT(o.id)` is inflated by the second join: each order is repeated once per line item, so a three-line order counts as three orders. The corrected version collapses the many side first and filters in the right place: ```sql WITH order_revenue AS ( SELECT o.id, o.customer_id, o.created_at, SUM(oi.line_total) AS order_total FROM orders o JOIN order_items oi ON oi.order_id = o.id GROUP BY o.id, o.customer_id, o.created_at ) SELECT c.id, c.name, COALESCE(SUM(orv.order_total), 0) AS revenue, COUNT(orv.id) AS orders FROM customers c LEFT JOIN order_revenue orv ON orv.customer_id = c.id AND orv.created_at >= '2026-01-01' GROUP BY c.id, c.name; ``` The date filter moved into `ON`, so unmatched customers survive with `revenue = 0` and `orders = 0`. The line items are summed to one row per order inside the CTE, so `COUNT` and `SUM` now count orders once. Verify it: the result row count must equal `SELECT COUNT(*) FROM customers`, because the grain is one row per customer. ## Templates you can use verbatim ```sql -- Lookup (one-to-one or many-to-one): attach reference data SELECT f.*, d.name AS dim_name FROM fact f JOIN dim d ON d.id = f.dim_id; -- Keep all primary rows, filter the matched side in ON SELECT p.*, m.status FROM primary p LEFT JOIN match m ON m.p_id = p.id AND m.active = true; -- Pre-aggregate the many side, then join (avoids fan-out) WITH agg AS ( SELECT parent_id, SUM(amount) AS total, COUNT(*) AS n FROM child GROUP BY parent_id ) SELECT p.*, COALESCE(agg.total, 0) AS total, COALESCE(agg.n, 0) AS n FROM parent p LEFT JOIN agg ON agg.parent_id = p.id; -- Anti-join: rows in A with no match in B SELECT a.* FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id); -- Self-join: hierarchy, keeping the root SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON m.id = e.manager_id; -- FULL OUTER emulation for engines without it SELECT a.key, a.val AS a_val, b.val AS b_val FROM a LEFT JOIN b ON b.key = a.key UNION ALL SELECT b.key, NULL, b.val FROM b WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.key = b.key); ``` Prompts that should trigger this skill: "Join orders to customers and give me revenue per customer", "Why does my LEFT JOIN drop rows when I add a WHERE?", "Find users with no orders", "My totals doubled after I added the items table", "Compare each employee's salary to their manager's". ## Failure modes and anti-patterns 1. **A WHERE filter on the outer table turns a LEFT JOIN into an INNER JOIN.** Spot it when unmatched rows you expected are missing and the filtered column belongs to the right table. Fix: move the condition into `ON`, or decide you really wanted an inner join and say so. 2. **Fan-out inflates aggregates.** Spot it when a `SUM` or `COUNT` is a clean multiple of the right answer, or when the result has more rows than the target grain. Fix: pre-aggregate the many side before joining, or use `EXISTS` or `COUNT(DISTINCT ...)`. 3. **NOT IN with a NULL returns nothing.** Spot it when an anti-join query returns zero rows against data you know has non-matches. Fix: use `NOT EXISTS` or the `LEFT JOIN ... IS NULL` idiom. 4. **Duplicate rows from a partial or many-to-many key.** Spot it with the grouped-count check in step 2 and a higher-than-expected final row count. Fix: join on the full composite key, or collapse one side to the needed grain first. 5. **A missing ON clause or a comma join creates a cross product.** Spot it when the row count explodes to roughly the product of the two table sizes. Fix: add the explicit `ON` condition; in MySQL be aware that `JOIN` binds tighter than a comma, so mixing the two breaks the `ON` scope (see appendix). ## Quality bar Run the output against this list before handing it over. Every item is checkable, not aspirational. - The target grain is stated as one sentence, and the final row count matches it. - The join type matches the stated intent, and no `RIGHT JOIN` is used. - Join conditions are in `ON`; row filters are in `WHERE`; any outer-side condition that must keep unmatched rows is in `ON`. - Every inner and outer join has an explicit `ON` (or `USING`) condition. - Aggregates over a one-to-many join are protected against fan-out and verified by row count. - Anti-joins use `NOT EXISTS` or the `IS NULL` idiom, never `NOT IN` on a nullable column. - Composite relationships join on the full key. - The join key is indexed on the looked-up table, and `EXPLAIN` confirms the plan. - Any `UPDATE`, `DELETE` or `MERGE` that uses a join is shown first as a `SELECT` preview with a row count and held for human confirmation. ## Data handling and safety Joins themselves read data, but the patterns here are often used to build `UPDATE`, `DELETE` and `MERGE` statements, which are irreversible. Default to a `SELECT` that lists the rows a destructive statement would touch, report the count, and require explicit human sign-off before running the change. Never paste real personal, health or financial values into a prompt, a commit message or a shared log; describe columns by name and type instead. Do not send table contents to a third-party service to "help write the join"; the schema and a few synthetic rows are enough. ## Appendix: engine-specific notes (verify before relying on these) This appendix is dialect-specific and sits apart from the tool-agnostic workflow above. - **FULL OUTER JOIN**: supported in PostgreSQL, SQL Server, Oracle and SQLite 3.39+ (2022). MySQL has never supported it; emulate with the `LEFT JOIN` plus `UNION ALL` anti-join template above. - **RIGHT JOIN**: valid in PostgreSQL, MySQL and SQL Server. Rewrite as `LEFT JOIN` regardless, for readability. - **USING vs ON**: `USING (col)` requires the column to exist in both tables and collapses it to one output column; `ON` keeps both. `NATURAL JOIN` joins on all same-named columns implicitly and is best avoided, because a later schema change can silently add or drop a join key. - **Comma joins (MySQL and others)**: `JOIN` binds tighter than a comma, so `FROM t1, t2 JOIN t3 ON t1.a = t3.a` fails with "Unknown column 't1.a' in 'on clause'". Use explicit `JOIN` throughout, or parenthesise. - **Update or delete with a join**: PostgreSQL uses `UPDATE t SET ... FROM other WHERE ...` and `DELETE FROM t USING other WHERE ...`. MySQL uses `UPDATE t JOIN other ON ... SET ...` and `DELETE t FROM t JOIN other ON ...`. SQLite uses `UPDATE t SET ... FROM other WHERE ...` (3.33+, 2020). SQL Server uses `UPDATE t SET ... FROM t JOIN other ON ...`. Preview with a `SELECT` first in every dialect. - **LATERAL / CROSS APPLY**: PostgreSQL and MySQL 8.0.14+ support `LATERAL`; SQL Server uses `CROSS APPLY` and `OUTER APPLY`. These let a joined subquery reference the left row, which a plain join cannot. - **Indexing for joins**: the engine may use nested-loop, hash or merge joins. A nested-loop join needs an index on the join key of the inner table; a hash join does not but needs memory. The planner decides; your job is to provide the index and check the plan. ## Sources - PostgreSQL 18 documentation, "Table Expressions" and "SELECT" (joined tables, `ON`/`USING`, `LATERAL`). Verified 29 October 2026. - MySQL 8.0 Reference Manual, "JOIN Clause" (`ON` vs `WHERE`, `NATURAL`/`USING`, comma-join precedence, NULL extension, anti-join idiom). Verified 29 October 2026. - SQLite documentation, "SELECT" and release notes for `FULL OUTER JOIN` (3.39, 2022) and `UPDATE ... FROM` (3.33, 2020). Verified 29 October 2026. - "Where SQL joins quietly go wrong", techinterview.org (WHERE-undoes-LEFT-JOIN, fan-out, NOT IN with NULL, self-joins). Verified 29 October 2026. - Markus Winand, "Use The Index, Luke", joins chapter (nested-loop, hash and sort-merge joins; join order and permutations). Verified 29 October 2026.