--- title: "A Visual Guide to SQL Joins: Types, Examples, and Use Cases" author: "Sanju Singh" published: "2026-11-16T06:05:46.313Z" updated: "2026-11-16T06:05:46.313Z" url: "https://zyvop.com/a-visual-guide-to-sql-joins-types-examples-and-use-cases-fbk84" canonical: "https://zyvop.com/a-visual-guide-to-sql-joins-types-examples-and-use-cases-fbk84" tags: ["Data Engineering","SQL","SQL Tutorial","SQL Joins","database"] --- # A Visual Guide to SQL Joins: Types, Examples, and Use Cases A visual, row-by-row guide to INNER, OUTER, CROSS, SELF, SEMI, ANTI, and LATERAL joins, plus NULL behavior, query pitfalls, and performance basics. ![Cover image]() _Inner, outer, cross, self, semi, anti, and lateral joins, drawn as row-matching diagrams so you can see exactly what each one returns._ Most join tutorials reach for the same picture: two overlapping circles. Venn diagrams are tidy, but they describe _sets of values_, and joins operate on _rows_. A circle can’t show why a customer with two orders appears twice, why a row with a NULL key vanishes, or why a careless join multiplies your row count. This guide uses a different picture. Each diagram draws the actual rows from two tables, connects the ones that match, and colors every row by what happens to it. Once you can see the rows, every join type, from INNER to LATERAL, turns out to be a small variation on one idea. ## The Sample Data Two tables, built with deliberate quirks. Asha has two orders, Chitra and Dev have none, and order 104 points to a customer who doesn’t exist. **customers** | customer\_id | name | | --- | --- | | 1 | Asha | | 2 | Ben | | 3 | Chitra | | 4 | Dev | **orders** | order\_id | customer\_id | amount | | --- | --- | --- | | 101 | 1 | 250 | | 102 | 1 | 80 | | 103 | 2 | 120 | | 104 | 5 | 60 | To follow along, run this in most SQL engines (PostgreSQL, MySQL, SQL Server, SQLite, and similar): ```sql CREATE TABLE customers (customer_id INT, name VARCHAR(20)); INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ben'), (3, 'Chitra'), (4, 'Dev'); CREATE TABLE orders (order_id INT, customer_id INT, amount INT); INSERT INTO orders VALUES (101, 1, 250), (102, 1, 80), (103, 2, 120), (104, 5, 60); ``` Here are the same tables drawn as rows. A line joins two rows when `customer_id` matches. Rows drawn in red have no partner on the other side: ```mermaid flowchart LR subgraph CUST["customers"] C1["1 Asha"] C2["2 Ben"] C3["3 Chitra"] C4["4 Dev"] end subgraph ORD["orders"] O1["order 101
customer 1"] O2["order 102
customer 1"] O3["order 103
customer 2"] O4["order 104
customer 5"] end C1 --- O1 C1 --- O2 C2 --- O3 C4 ~~~ O4 classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888 classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d class C3,C4,O4 lonely style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 ``` Keep this picture in mind. Every diagram below reuses it, and only the colors change. One note on the result tables: they are sorted for readability. Without an `ORDER BY`, SQL guarantees no particular row order. ## The One Loop Behind Every Join Whatever its name, every join follows the same logic. The engine compares candidate pairs of rows using the `ON` condition, and then decides what to do with rows that never found a partner: ```mermaid flowchart TD A["Compare each candidate pair
of rows using the ON condition"] --> B{"ON is TRUE?"} B -->|Yes| C["Emit the combined row"] B -->|"False or NULL"| D["No match for this pair"] D --> E{"Row never matched anything
and the join type preserves
its side?"} E -->|Yes| F["Emit the row once,
padded with NULLs"] E -->|No| G["Drop the row"] ``` Only the last decision changes between INNER, LEFT, RIGHT, and FULL joins: which sides are preserved. Keep that diamond in mind as we go through them. From here on, the join diagrams use three colors: - **Green:** the row appears in the result. - **Amber:** the row appears in the result, padded with NULLs because it has no partner. - **Grey:** the row does not appear in the result. * * * ## 1\. INNER JOIN: Only Matched Rows An inner join keeps a row only when it found a partner on the other side. `JOIN` on its own means `INNER JOIN`. ```sql SELECT c.name, o.order_id, o.amount FROM customers c INNER JOIN orders o ON o.customer_id = c.customer_id; ``` ```mermaid flowchart LR subgraph CUST["customers"] C1["1 Asha"] C2["2 Ben"] C3["3 Chitra"] C4["4 Dev"] end subgraph ORD["orders"] O1["order 101
customer 1"] O2["order 102
customer 1"] O3["order 103
customer 2"] O4["order 104
customer 5"] end C1 --- O1 C1 --- O2 C2 --- O3 C4 ~~~ O4 classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888 classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d class C1,C2,O1,O2,O3 kept class C3,C4,O4 dropped style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 ``` | name | order\_id | amount | | --- | --- | --- | | Asha | 101 | 250 | | Asha | 102 | 80 | | Ben | 103 | 120 | Two things to notice. Asha appears twice because a join returns **one row per matching pair**, not one row per customer. And Chitra, Dev, and order 104 are gone, because none of them had a partner. Use an inner join when you only care about complete records, such as orders that belong to known customers. ## 2\. LEFT JOIN: Keep Everything on the Left A left join keeps every row from the left table. Where there is no match, the right-side columns are filled with NULL. ```sql SELECT c.name, o.order_id, o.amount FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id; ``` ```mermaid flowchart LR subgraph CUST["customers"] C1["1 Asha"] C2["2 Ben"] C3["3 Chitra"] C4["4 Dev"] end subgraph ORD["orders"] O1["order 101
customer 1"] O2["order 102
customer 1"] O3["order 103
customer 2"] O4["order 104
customer 5"] end C1 --- O1 C1 --- O2 C2 --- O3 C4 ~~~ O4 classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888 classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d class C1,C2,O1,O2,O3 kept class C3,C4 padded class O4 dropped style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 ``` | name | order\_id | amount | | --- | --- | --- | | Asha | 101 | 250 | | Asha | 102 | 80 | | Ben | 103 | 120 | | Chitra | NULL | NULL | | Dev | NULL | NULL | Chitra and Dev survive, shown in amber, with NULL where the order columns would be. Order 104 is dropped, since it belongs to the right side and the right side isn’t preserved. The left join is the workhorse of analytics because it lets you start from a base table (customers, users, dates) and attach optional context without losing anyone. A handy sanity check: the result can never have fewer rows than the left table. ## 3\. RIGHT JOIN: The Mirror Image A right join is a left join with the roles reversed. These two queries return exactly the same rows: ```sql SELECT c.name, o.order_id, o.amount FROM customers c RIGHT JOIN orders o ON o.customer_id = c.customer_id; SELECT c.name, o.order_id, o.amount FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id; ``` ```mermaid flowchart LR subgraph CUST["customers"] C1["1 Asha"] C2["2 Ben"] C3["3 Chitra"] C4["4 Dev"] end subgraph ORD["orders"] O1["order 101
customer 1"] O2["order 102
customer 1"] O3["order 103
customer 2"] O4["order 104
customer 5"] end C1 --- O1 C1 --- O2 C2 --- O3 C4 ~~~ O4 classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888 classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d class C1,C2,O1,O2,O3 kept class O4 padded class C3,C4 dropped style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 ``` | name | order\_id | amount | | --- | --- | --- | | Asha | 101 | 250 | | Asha | 102 | 80 | | Ben | 103 | 120 | | NULL | 104 | 60 | Now it is the orders that are all preserved, so order 104 appears with a NULL name, while Chitra and Dev drop out. Many teams standardize on `LEFT JOIN` and reorder the tables when needed, because "start with the base table and keep adding to it" is easier to read than a mix of directions. ## 4\. FULL OUTER JOIN: Keep Everything A full outer join preserves both sides. Matched rows are combined, and unmatched rows from either side appear with NULLs. ```sql SELECT c.name, o.order_id, o.amount FROM customers c FULL OUTER JOIN orders o ON o.customer_id = c.customer_id; ``` ```mermaid flowchart LR subgraph CUST["customers"] C1["1 Asha"] C2["2 Ben"] C3["3 Chitra"] C4["4 Dev"] end subgraph ORD["orders"] O1["order 101
customer 1"] O2["order 102
customer 1"] O3["order 103
customer 2"] O4["order 104
customer 5"] end C1 --- O1 C1 --- O2 C2 --- O3 C4 ~~~ O4 classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888 classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d class C1,C2,O1,O2,O3 kept class C3,C4,O4 padded style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 ``` | name | order\_id | amount | | --- | --- | --- | | Asha | 101 | 250 | | Asha | 102 | 80 | | Ben | 103 | 120 | | Chitra | NULL | NULL | | Dev | NULL | NULL | | NULL | 104 | 60 | The classic use is **reconciliation**: comparing two systems and finding what exists in only one of them. Any row where one side’s key is NULL is a discrepancy. MySQL has no `FULL OUTER JOIN`. You can emulate it by combining a left and a right join. Use `UNION` here, not `UNION ALL`, so the matched rows that both queries return are not counted twice: ```sql SELECT c.name, o.order_id, o.amount FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id UNION SELECT c.name, o.order_id, o.amount FROM customers c RIGHT JOIN orders o ON o.customer_id = c.customer_id; ``` One caveat: `UNION` also removes rows that are genuinely identical, which matters if your data can contain true duplicates. * * * ## 5\. Anti-Joins: Rows With No Match An anti-join answers "which rows have no partner?" Standard SQL has no `ANTI JOIN` keyword, so you write it one of two ways. (Spark SQL and Databricks are the exception: they offer `LEFT ANTI JOIN` and `LEFT SEMI JOIN` directly.) The first is a left join that keeps only the unmatched rows: ```sql SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL; ``` The second uses `NOT EXISTS`, which says what you mean more directly: ```sql SELECT c.name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id ); ``` ```mermaid flowchart LR subgraph CUST["customers"] C1["1 Asha"] C2["2 Ben"] C3["3 Chitra"] C4["4 Dev"] end subgraph ORD["orders"] O1["order 101
customer 1"] O2["order 102
customer 1"] O3["order 103
customer 2"] O4["order 104
customer 5"] end C1 --- O1 C1 --- O2 C2 --- O3 C4 ~~~ O4 classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888 classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d class C3,C4 kept class C1,C2,O1,O2,O3,O4 dropped style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 ``` Both queries return the same result: | name | | --- | | Chitra | | Dev | In the first form, the `IS NULL` test must be on a column that can never be NULL in a real match, such as the right table’s key. Avoid `NOT IN (subquery)` for this job: a single NULL in the subquery makes it return no rows at all. Swap the tables and you get the other direction. This one finds **orphans**, orders with no customer, and it is a staple data quality check: ```sql SELECT o.order_id, o.amount FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id ); ``` ```mermaid flowchart LR subgraph CUST["customers"] C1["1 Asha"] C2["2 Ben"] C3["3 Chitra"] C4["4 Dev"] end subgraph ORD["orders"] O1["order 101
customer 1"] O2["order 102
customer 1"] O3["order 103
customer 2"] O4["order 104
customer 5"] end C1 --- O1 C1 --- O2 C2 --- O3 C4 ~~~ O4 classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888 classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d class O4 kept class C1,C2,O1,O2,O3,C3,C4 dropped style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 ``` | order\_id | amount | | --- | --- | | 104 | 60 | ## 6\. Semi-Joins: Is There a Match? A semi-join keeps rows from one side only if a match exists, and it never pulls in columns from the other side. It is written with `EXISTS`: ```sql SELECT c.name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id ); ``` ```mermaid flowchart LR subgraph CUST["customers"] C1["1 Asha"] C2["2 Ben"] C3["3 Chitra"] C4["4 Dev"] end subgraph ORD["orders"] O1["order 101
customer 1"] O2["order 102
customer 1"] O3["order 103
customer 2"] O4["order 104
customer 5"] end C1 --- O1 C1 --- O2 C2 --- O3 C4 ~~~ O4 classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b classDef padded fill:#fef3c7,stroke:#d97706,color:#78350f classDef dropped fill:#eeeeee,stroke:#bbbbbb,color:#888888 classDef lonely fill:#fee2e2,stroke:#dc2626,color:#7f1d1d class C1,C2 kept class C3,C4,O1,O2,O3,O4 dropped style CUST fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 ``` | name | | --- | | Asha | | Ben | Compare this with the inner join from earlier, which returned Asha twice. A semi-join returns each customer **at most once**, no matter how many orders match, because the orders are only used to answer a yes-or-no question. That makes it the right tool for "customers who have ordered" when you don’t need order details. * * * ## 7\. CROSS JOIN: Every Combination A cross join has no `ON` condition. It pairs every row on the left with every row on the right, so the result has m × n rows. Given two sizes and two colors: ```sql SELECT s.size, c.color FROM sizes s CROSS JOIN colors c; ``` ```mermaid flowchart LR subgraph SZ["sizes"] S1["S"] S2["M"] end subgraph CL["colors"] K1["Red"] K2["Blue"] end S1 --- K1 S1 --- K2 S2 --- K1 S2 --- K2 classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b class S1,S2,K1,K2 kept style SZ fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 style CL fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 ``` | size | color | | --- | --- | | M | Blue | | M | Red | | S | Blue | | S | Red | Cross joins are useful for generating combinations, such as every product in every store, or a calendar of dates paired with every account so that days with no activity still show up as zeros. They are also dangerous when they happen by accident. The old comma syntax, `FROM customers, orders`, with the `WHERE` condition forgotten, silently becomes a cross join: our 4 customers and 4 orders would produce 16 rows. On two large tables, the result can be catastrophic. Write `CROSS JOIN` when you mean a cross join, and always write the `ON` condition otherwise. Some engines, such as PostgreSQL and SQL Server, reject a plain `JOIN` with no `ON`, but MySQL, SQLite, and Databricks accept it and treat it as a cross join, so you can’t count on a syntax error to catch the mistake. ## 8\. Self Joins: A Table Meets Itself A self join joins a table to itself under two aliases, so each row can be related to another row of the same table. The classic example is an employees table where each row stores its manager’s id: | emp\_id | name | manager\_id | | --- | --- | --- | | 1 | Maya | NULL | | 2 | Raj | 1 | | 3 | Sana | 1 | | 4 | Tom | 2 | ```sql SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id; ``` ```mermaid flowchart BT M1["1 Maya
manager_id NULL"] M2["2 Raj
manager_id 1"] -->|"reports to"| M1 M3["3 Sana
manager_id 1"] -->|"reports to"| M1 M4["4 Tom
manager_id 2"] -->|"reports to"| M2 classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b class M1,M2,M3,M4 kept ``` | employee | manager | | --- | --- | | Maya | NULL | | Raj | Maya | | Sana | Maya | | Tom | Raj | The alias `e` plays the employee and `m` plays the manager, even though both are the same table. We used a `LEFT JOIN` so that Maya, who has no manager, still appears. An inner join would have dropped her. Self joins also handle one level of a hierarchy at a time, such as a category and its parent (walking a whole tree takes a recursive CTE), and they answer questions like "which pairs of rows in this table share a property?" ## 9\. NATURAL JOIN and USING: The Shorthands Two shortcuts exist for joins on identically named columns. `USING` names the column explicitly: ```sql SELECT * FROM customers JOIN orders USING (customer_id); ``` | customer\_id | name | order\_id | amount | | --- | --- | --- | --- | | 1 | Asha | 101 | 250 | | 1 | Asha | 102 | 80 | | 2 | Ben | 103 | 120 | Besides saving typing, `USING` merges the join column, so `customer_id` appears once in `SELECT *` instead of twice. `NATURAL JOIN` goes further and joins on **every** column the two tables share, with no condition written at all: ```sql SELECT * FROM customers NATURAL JOIN orders; ``` It returns the same rows today, which is exactly why it is risky. If someone later adds an `updated_at` column to both tables, the join condition silently changes to include it, and the results change with no code edit. Treat `NATURAL JOIN` as a curiosity and prefer `USING` or an explicit `ON`. If the code must also run on SQL Server, use `ON`, because SQL Server supports neither shorthand. ## 10\. Non-Equi Joins: Matching on Ranges Every join so far matched on equality. The `ON` condition can be any expression, including `<`, `>`, or a range. This is a **non-equi join**. Suppose we want to label each order with a discount tier based on its amount: | tier | min\_amount | max\_amount | | --- | --- | --- | | Bronze | 0 | 100 | | Silver | 100 | 200 | | Gold | 200 | 1000 | ```sql SELECT o.order_id, o.amount, t.tier FROM orders o JOIN tiers t ON o.amount >= t.min_amount AND o.amount < t.max_amount; ``` ```mermaid flowchart LR subgraph ORD["orders"] A1["101: amount 250"] A2["102: amount 80"] A3["103: amount 120"] A4["104: amount 60"] end subgraph TIER["tiers"] T1["Bronze: 0 up to 100"] T2["Silver: 100 up to 200"] T3["Gold: 200 up to 1000"] end A1 --- T3 A2 --- T1 A3 --- T2 A4 --- T1 classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b class A1,A2,A3,A4,T1,T2,T3 kept style ORD fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 style TIER fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 ``` | order\_id | amount | tier | | --- | --- | --- | | 101 | 250 | Gold | | 102 | 80 | Bronze | | 103 | 120 | Silver | | 104 | 60 | Bronze | Notice the half-open range: `>=` on the lower bound and `<` on the upper bound. With `BETWEEN`, which is inclusive on both ends, an amount of exactly 100 would match both Bronze and Silver and the order would be duplicated. The same half-open pattern is how you join facts to historical dimension rows, such as slowly changing dimensions. Non-equi joins are also where performance suffers, because most engines can’t use their fastest strategy (the hash join, covered below) for them, though some add specialized range-join optimizations. On large tables, keep the range side small or pre-filter aggressively. ## 11\. LATERAL Joins: A Subquery for Every Row An ordinary derived table can’t see the other tables in the `FROM` clause. A **lateral** join can: the subquery is conceptually re-run for each row on the left, and it may reference that row’s columns. This unlocks the classic "top N per group" problem, such as the latest order for each customer: ```sql SELECT c.name, o.order_id, o.amount FROM customers c LEFT JOIN LATERAL ( SELECT order_id, amount FROM orders WHERE orders.customer_id = c.customer_id ORDER BY order_id DESC -- highest order_id = latest order LIMIT 1 ) o ON TRUE; ``` ```mermaid flowchart LR A["customers row
Asha, id 1"] -->|"c.customer_id passed in"| B["Subquery runs for this row
orders of customer 1
newest first, LIMIT 1"] B --> C["Returns order 102"] D["customers row
Chitra, id 3"] -->|"c.customer_id passed in"| E["Subquery runs for this row
orders of customer 3"] E --> F["Returns nothing,
LEFT JOIN pads with NULL"] ``` Syntax varies by engine. PostgreSQL, MySQL 8.0.14 and later, and Oracle 12c and later support `LATERAL`. SQL Server uses `CROSS APPLY` and `OUTER APPLY`, which Oracle 12c and later also accepts. If your engine has neither, a window function gives the same answer: ```sql SELECT c.name, o.order_id, o.amount FROM customers c LEFT JOIN ( SELECT order_id, customer_id, amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_id DESC) AS rn FROM orders ) o ON o.customer_id = c.customer_id AND o.rn = 1; ``` Either way, each customer gets at most one order, and customers with no orders still appear: | name | order\_id | amount | | --- | --- | --- | | Asha | 102 | 80 | | Ben | 103 | 120 | | Chitra | NULL | NULL | | Dev | NULL | NULL | * * * ## 12\. How the Database Executes a Join So far we’ve covered what a join _returns_. How the engine _computes_ it is a separate question, and it explains many join performance problems. There are three main strategies, and the optimizer picks one for you. **Nested loop.** For each row on one side, look for matches on the other side. This is cheap when one side is small and the other has an index on the join key, and it is the fallback for non-equi joins. **Hash join.** Build an in-memory hash table from the smaller input, then stream the larger input through it, probing for matches: ```mermaid flowchart LR S["Smaller input
customers"] -->|"1. Build"| H["Hash table
keyed on customer_id"] L["Larger input
orders"] -->|"2. Probe one row at a time"| H H --> R["Matching pairs
are emitted"] ``` This is the usual choice for large inputs joined on equality, with no useful index. **Merge join.** Walk two inputs side by side after sorting both on the join key. It shines when the data is already sorted, for example through an index or a clustered layout. You can see which one the optimizer chose by running `EXPLAIN`, where, in PostgreSQL, they show up as `Nested Loop`, `Hash Join`, and `Merge Join` (other engines use their own names). The choice follows roughly this logic: ```mermaid flowchart TD Q{"What does the join
condition look like?"} Q -->|"Range or inequality"| N1["Nested loop"] Q -->|"Equality, one small side
and an index on the other"| N2["Nested loop with index lookups"] Q -->|"Equality, both inputs
already sorted"| M["Merge join"] Q -->|"Equality, large inputs"| H["Hash join"] ``` * * * ## Three Join Pitfalls Most join bugs come from just three places. ### Pitfall 1: The WHERE trap on outer joins You write a `LEFT JOIN` to keep every customer, then add a filter on the right table in `WHERE`. The padded rows have NULL in that column, `NULL > 100` is unknown, and `WHERE` discards them. Your left join has turned into an inner join: ```mermaid flowchart LR A["LEFT JOIN result
Chitra and Dev padded with NULL"] --> B["WHERE o.amount > 100"] B --> C["NULL > 100 is unknown
padded rows are removed"] C --> D["Behaves like an INNER JOIN"] ``` ```sql -- Looks like a LEFT JOIN, behaves like an INNER JOIN SELECT c.name, o.amount FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.amount > 100; ``` | name | amount | | --- | --- | | Asha | 250 | | Ben | 120 | Moving the condition into `ON` filters the orders _before_ unmatched customers are padded, so everyone survives: ```sql SELECT c.name, o.amount FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.amount > 100; ``` | name | amount | | --- | --- | | Asha | 250 | | Ben | 120 | | Chitra | NULL | | Dev | NULL | The rule of thumb: conditions on the preserved table (the left side of a left join) go in `WHERE`, and conditions on the optional table go in `ON`. For inner joins the two placements are equivalent. ### Pitfall 2: NULL keys never match `NULL = NULL` evaluates to unknown, not true, so a row whose join key is NULL matches nothing. In an inner join the row vanishes. In a left join, a NULL-keyed row on the left side comes through unmatched, while one on the right side is simply dropped. If you really want NULL to match NULL, use a null-safe comparison: ```sql ON a.customer_id IS NOT DISTINCT FROM b.customer_id -- standard SQL: PostgreSQL, Snowflake, BigQuery, others -- MySQL spelling: ON a.customer_id <=> b.customer_id ``` ### Pitfall 3: Many-to-many row explosion When the join key isn’t unique on either side, rows multiply. Two matching rows on the left and three on the right produce 2 × 3 = 6 result rows, and any sum computed afterward is inflated: ```mermaid flowchart LR subgraph LA["table a, key = 1"] X1["a1"] X2["a2"] end subgraph LB["table b, key = 1"] Y1["b1"] Y2["b2"] Y3["b3"] end X1 --- Y1 X1 --- Y2 X1 --- Y3 X2 --- Y1 X2 --- Y2 X2 --- Y3 classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b class X1,X2,Y1,Y2,Y3 kept style LA fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 style LB fill:none,stroke:#9ca3af,stroke-dasharray: 4 3 ``` Six lines means six rows. Before trusting any join, check that the key is unique on the side you expect to have one row per key: ```sql SELECT customer_id, COUNT(*) FROM customers GROUP BY customer_id HAVING COUNT(*) > 1; ``` If that query returns rows, joining to `customers` will multiply your data. Also compare row counts before and after the join. A left join that returns more rows than the left table, when you expected one row per left row, is the signature of this bug. * * * ## Choosing the Right Join When you’re unsure, start from what you need in the output: ```mermaid flowchart TD Q{"What do you need
from the result?"} Q -->|"Only rows that match"| I["INNER JOIN"] Q -->|"Everything from one side,
matches optional"| L["LEFT JOIN"] Q -->|"Everything from both sides"| F["FULL OUTER JOIN"] Q -->|"Rows with no match"| A["Anti-join:
NOT EXISTS"] Q -->|"A yes or no about a match"| S["Semi-join:
EXISTS"] Q -->|"Every combination"| C["CROSS JOIN"] Q -->|"Rows related to other rows
in the same table"| SJ["Self join"] Q -->|"Matching on a range"| N["Non-equi join"] Q -->|"A subquery per row,
like top N per group"| LAT["LATERAL or APPLY"] ``` And here is how the main ones treat the rows that don’t match, plus whether they can multiply rows: | Join | Left-only rows | Right-only rows | Can repeat a row? | | --- | --- | --- | --- | | INNER | Dropped | Dropped | Yes | | LEFT | Kept, NULL-padded | Dropped | Yes | | RIGHT | Dropped | Kept, NULL-padded | Yes | | FULL OUTER | Kept, NULL-padded | Kept, NULL-padded | Yes | | Left anti-join | The only rows returned | Not returned | No | | Semi-join | Dropped | Not returned | No | | CROSS | Not applicable | Not applicable | Yes, m × n rows | ## The Takeaway Joins are not magic. They are one simple loop: pair up rows, test the condition, and decide the fate of the lonely ones. The next time a join returns a surprising number of rows, don’t guess. Sketch the two tables, draw a line between every pair that matches, and color the rows by what happens to them. The bug is usually visible within a minute: a row with no partner, a key that isn’t unique, or a filter in the wrong clause.