{"schemaVersion":"1.0","type":"TechArticle","types":["Article","TechArticle"],"slug":"a-visual-guide-to-sql-joins-types-examples-and-use-cases-fbk84","url":"https://zyvop.com/a-visual-guide-to-sql-joins-types-examples-and-use-cases-fbk84","title":"A Visual Guide to SQL Joins: Types, Examples, and Use Cases","subtitle":"A visual, row-by-row guide to INNER, OUTER, CROSS, SELF, SEMI, ANTI, and LATERAL joins, plus NULL behavior, query pitfalls, and performance basics.","tldr":"Learn how every major SQL join works through row-level diagrams and examples. Compare INNER, LEFT, RIGHT, FULL, SEMI, ANTI, CROSS, SELF, NON-EQUI, and LATERAL joins, then explore NULL handling, row multiplication, common mistakes, and join execution strategies.","keywords":["Data Engineering","SQL","SQL Tutorial","SQL Joins","database","Databases"],"entities":["Sanju Singh","Data Engineering","SQL","SQL Tutorial","SQL Joins","database","Databases","ZyVOP"],"keyTakeaways":["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."],"headings":["The Sample Data","The One Loop Behind Every Join","1. INNER JOIN: Only Matched Rows","2. LEFT JOIN: Keep Everything on the Left","3. RIGHT JOIN: The Mirror Image","4. FULL OUTER JOIN: Keep Everything","5. Anti-Joins: Rows With No Match","6. Semi-Joins: Is There a Match?","7. CROSS JOIN: Every Combination","8. Self Joins: A Table Meets Itself","9. NATURAL JOIN and USING: The Shorthands","10. Non-Equi Joins: Matching on Ranges","11. LATERAL Joins: A Subquery for Every Row","12. How the Database Executes a Join","Three Join Pitfalls","Pitfall 1: The WHERE trap on outer joins","Pitfall 2: NULL keys never match","Pitfall 3: Many-to-many row explosion","Choosing the Right Join","The Takeaway"],"outboundLinks":[],"contentText":"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): 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: flowchart LR subgraph CUST[\"customers\"] C1[\"1 Asha\"] C2[\"2 Ben\"] C3[\"3 Chitra\"] C4[\"4 Dev\"] end subgraph ORD[\"orders\"] O1[\"order 101&lt;br/&gt;customer 1\"] O2[\"order 102&lt;br/&gt;customer 1\"] O3[\"order 103&lt;br/&gt;customer 2\"] O4[\"order 104&lt;br/&gt;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 3Keep 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: flowchart TD A[\"Compare each candidate pair&lt;br/&gt;of rows using the ON condition\"] --&gt; B{\"ON is TRUE?\"} B --&gt;|Yes| C[\"Emit the combined row\"] B --&gt;|\"False or NULL\"| D[\"No match for this pair\"] D --&gt; E{\"Row never matched anything&lt;br/&gt;and the join type preserves&lt;br/&gt;its side?\"} E --&gt;|Yes| F[\"Emit the row once,&lt;br/&gt;padded with NULLs\"] E --&gt;|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. SELECT c.name, o.order_id, o.amount FROM customers c INNER JOIN orders o ON o.customer_id = c.customer_id;flowchart LR subgraph CUST[\"customers\"] C1[\"1 Asha\"] C2[\"2 Ben\"] C3[\"3 Chitra\"] C4[\"4 Dev\"] end subgraph ORD[\"orders\"] O1[\"order 101&lt;br/&gt;customer 1\"] O2[\"order 102&lt;br/&gt;customer 1\"] O3[\"order 103&lt;br/&gt;customer 2\"] O4[\"order 104&lt;br/&gt;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 3name 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. SELECT c.name, o.order_id, o.amount FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id;flowchart LR subgraph CUST[\"customers\"] C1[\"1 Asha\"] C2[\"2 Ben\"] C3[\"3 Chitra\"] C4[\"4 Dev\"] end subgraph ORD[\"orders\"] O1[\"order 101&lt;br/&gt;customer 1\"] O2[\"order 102&lt;br/&gt;customer 1\"] O3[\"order 103&lt;br/&gt;customer 2\"] O4[\"order 104&lt;br/&gt;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 3name 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: 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;flowchart LR subgraph CUST[\"customers\"] C1[\"1 Asha\"] C2[\"2 Ben\"] C3[\"3 Chitra\"] C4[\"4 Dev\"] end subgraph ORD[\"orders\"] O1[\"order 101&lt;br/&gt;customer 1\"] O2[\"order 102&lt;br/&gt;customer 1\"] O3[\"order 103&lt;br/&gt;customer 2\"] O4[\"order 104&lt;br/&gt;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 3name 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. SELECT c.name, o.order_id, o.amount FROM customers c FULL OUTER JOIN orders o ON o.customer_id = c.customer_id;flowchart LR subgraph CUST[\"customers\"] C1[\"1 Asha\"] C2[\"2 Ben\"] C3[\"3 Chitra\"] C4[\"4 Dev\"] end subgraph ORD[\"orders\"] O1[\"order 101&lt;br/&gt;customer 1\"] O2[\"order 102&lt;br/&gt;customer 1\"] O3[\"order 103&lt;br/&gt;customer 2\"] O4[\"order 104&lt;br/&gt;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 3name 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: 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: 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: SELECT c.name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id );flowchart LR subgraph CUST[\"customers\"] C1[\"1 Asha\"] C2[\"2 Ben\"] C3[\"3 Chitra\"] C4[\"4 Dev\"] end subgraph ORD[\"orders\"] O1[\"order 101&lt;br/&gt;customer 1\"] O2[\"order 102&lt;br/&gt;customer 1\"] O3[\"order 103&lt;br/&gt;customer 2\"] O4[\"order 104&lt;br/&gt;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 3Both 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: SELECT o.order_id, o.amount FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id );flowchart LR subgraph CUST[\"customers\"] C1[\"1 Asha\"] C2[\"2 Ben\"] C3[\"3 Chitra\"] C4[\"4 Dev\"] end subgraph ORD[\"orders\"] O1[\"order 101&lt;br/&gt;customer 1\"] O2[\"order 102&lt;br/&gt;customer 1\"] O3[\"order 103&lt;br/&gt;customer 2\"] O4[\"order 104&lt;br/&gt;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 3order_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: SELECT c.name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id );flowchart LR subgraph CUST[\"customers\"] C1[\"1 Asha\"] C2[\"2 Ben\"] C3[\"3 Chitra\"] C4[\"4 Dev\"] end subgraph ORD[\"orders\"] O1[\"order 101&lt;br/&gt;customer 1\"] O2[\"order 102&lt;br/&gt;customer 1\"] O3[\"order 103&lt;br/&gt;customer 2\"] O4[\"order 104&lt;br/&gt;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 3name 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: SELECT s.size, c.color FROM sizes s CROSS JOIN colors c;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 3size 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 SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id;flowchart BT M1[\"1 Maya&lt;br/&gt;manager_id NULL\"] M2[\"2 Raj&lt;br/&gt;manager_id 1\"] --&gt;|\"reports to\"| M1 M3[\"3 Sana&lt;br/&gt;manager_id 1\"] --&gt;|\"reports to\"| M1 M4[\"4 Tom&lt;br/&gt;manager_id 2\"] --&gt;|\"reports to\"| M2 classDef kept fill:#d1fae5,stroke:#059669,color:#064e3b class M1,M2,M3,M4 keptemployee 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: 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: 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 &lt;, &gt;, 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 SELECT o.order_id, o.amount, t.tier FROM orders o JOIN tiers t ON o.amount &gt;= t.min_amount AND o.amount &lt; t.max_amount;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 3order_id amount tier 101 250 Gold 102 80 Bronze 103 120 Silver 104 60 Bronze Notice the half-open range: &gt;= on the lower bound and &lt; 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: 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;flowchart LR A[\"customers row&lt;br/&gt;Asha, id 1\"] --&gt;|\"c.customer_id passed in\"| B[\"Subquery runs for this row&lt;br/&gt;orders of customer 1&lt;br/&gt;newest first, LIMIT 1\"] B --&gt; C[\"Returns order 102\"] D[\"customers row&lt;br/&gt;Chitra, id 3\"] --&gt;|\"c.customer_id passed in\"| E[\"Subquery runs for this row&lt;br/&gt;orders of customer 3\"] E --&gt; F[\"Returns nothing,&lt;br/&gt;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: 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: flowchart LR S[\"Smaller input&lt;br/&gt;customers\"] --&gt;|\"1. Build\"| H[\"Hash table&lt;br/&gt;keyed on customer_id\"] L[\"Larger input&lt;br/&gt;orders\"] --&gt;|\"2. Probe one row at a time\"| H H --&gt; R[\"Matching pairs&lt;br/&gt;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: flowchart TD Q{\"What does the join&lt;br/&gt;condition look like?\"} Q --&gt;|\"Range or inequality\"| N1[\"Nested loop\"] Q --&gt;|\"Equality, one small side&lt;br/&gt;and an index on the other\"| N2[\"Nested loop with index lookups\"] Q --&gt;|\"Equality, both inputs&lt;br/&gt;already sorted\"| M[\"Merge join\"] Q --&gt;|\"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 &gt; 100 is unknown, and WHERE discards them. Your left join has turned into an inner join: flowchart LR A[\"LEFT JOIN result&lt;br/&gt;Chitra and Dev padded with NULL\"] --&gt; B[\"WHERE o.amount &gt; 100\"] B --&gt; C[\"NULL &gt; 100 is unknown&lt;br/&gt;padded rows are removed\"] C --&gt; D[\"Behaves like an INNER JOIN\"]-- 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 &gt; 100;name amount Asha 250 Ben 120 Moving the condition into ON filters the orders before unmatched customers are padded, so everyone survives: SELECT c.name, o.amount FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.amount &gt; 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: ON a.customer_id IS NOT DISTINCT FROM b.customer_id -- standard SQL: PostgreSQL, Snowflake, BigQuery, others -- MySQL spelling: ON a.customer_id &lt;=&gt; b.customer_idPitfall 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: 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 3Six 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: SELECT customer_id, COUNT(*) FROM customers GROUP BY customer_id HAVING COUNT(*) &gt; 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: flowchart TD Q{\"What do you need&lt;br/&gt;from the result?\"} Q --&gt;|\"Only rows that match\"| I[\"INNER JOIN\"] Q --&gt;|\"Everything from one side,&lt;br/&gt;matches optional\"| L[\"LEFT JOIN\"] Q --&gt;|\"Everything from both sides\"| F[\"FULL OUTER JOIN\"] Q --&gt;|\"Rows with no match\"| A[\"Anti-join:&lt;br/&gt;NOT EXISTS\"] Q --&gt;|\"A yes or no about a match\"| S[\"Semi-join:&lt;br/&gt;EXISTS\"] Q --&gt;|\"Every combination\"| C[\"CROSS JOIN\"] Q --&gt;|\"Rows related to other rows&lt;br/&gt;in the same table\"| SJ[\"Self join\"] Q --&gt;|\"Matching on a range\"| N[\"Non-equi join\"] Q --&gt;|\"A subquery per row,&lt;br/&gt;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.","contentHash":"sha256:009357977d0de3d6f20e5f496ae017846f9c24648d41a6ddef20b9de0399eb73","authorName":"Sanju Singh","authorUrl":"https://zyvop.com/author/sanjay687","authorSameAs":[],"category":"Databases","tags":["Data Engineering","SQL","SQL Tutorial","SQL Joins","database"],"audience":"Developers, software engineers, and students learning Databases","tone":"Instructional, practical, code-first","readingTimeMinutes":18,"wordCount":4625,"faqs":null,"primaryTopic":"Databases","publishedAt":"2026-11-16T06:05:46.313Z","updatedAt":"2026-11-16T06:05:46.313Z","canonicalUrl":"https://zyvop.com/a-visual-guide-to-sql-joins-types-examples-and-use-cases-fbk84"}