Why are Venn diagrams misleading for SQL joins?

Venn diagrams illustrate set membership (which keys exist in set A or B), but they fail to show relational cardinality. If table A has 3 matching rows for a key in table B, an INNER JOIN produces 3 rows (multiplication), whereas a Venn diagram implies rows are merely intersected without duplicating.

What is an ANTI-JOIN in SQL?

An Anti-Join returns rows from the first table that have NO matching rows in the second table. In standard SQL, write an Anti-Join using `LEFT JOIN ... WHERE right_table.id IS NULL` or `WHERE NOT EXISTS (SELECT 1 FROM ...)`. It is significantly faster and safer with NULLs than `NOT IN`.

What is the difference between putting a filter in ON vs WHERE in a LEFT JOIN?

A condition in the `ON` clause restricts which right-table rows are joined, but ALL left-table rows are still returned. A condition in the `WHERE` clause filters the joined result set AFTER joining, which silently converts a LEFT JOIN into an INNER JOIN if it filters on right-table non-null columns.

How does a database choose between Hash Join, Nested Loop, and Merge Join?

Query optimizers evaluate table sizes and available indexes: Nested Loop joins are chosen for small outer sets with indexed inner tables; Hash Joins build an in-memory hash table for unsorted, large datasets; and Merge Joins are chosen when both inputs are already sorted by the join keys.

SQL Dialects

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

By Sharon Ben-Moshe · October 13, 2026 · 3 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

The definitive guide to SQL joins. Master INNER, LEFT, RIGHT, FULL OUTER, CROSS, and ANTI-JOINs, physical join algorithms, and cardinality pitfalls.

Quick answer

A SQL JOIN combines columns from two or more tables based on a related key expression. INNER JOIN returns rows matching both tables; LEFT JOIN preserves all left rows and fills unmatched right columns with NULL; FULL OUTER JOIN preserves all rows from both tables; and CROSS JOIN produces the Cartesian product (M × N). Physical execution relies on Nested Loop, Hash, or Merge Join algorithms.

The Ultimate Guide to SQL JOINs: Visualizing Every Join Type, Cardinality, and Performance

Quick answer

FAQs

Why are Venn diagrams misleading for SQL joins?

What is an ANTI-JOIN in SQL?

What is the difference between putting a filter in ON vs WHERE in a LEFT JOIN?

How does a database choose between Hash Join, Nested Loop, and Merge Join?

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

BigQuery STRUCT vs ARRAY: Handling Nested and Repeated Data

Key takeaways

The complete SQL join catalog

1. INNER JOIN

2. LEFT OUTER JOIN

3. RIGHT OUTER JOIN

4. FULL OUTER JOIN

5. CROSS JOIN (Cartesian Product)

6. ANTI-JOIN (Unmatched Records)

The ON vs WHERE predicate trap in outer joins

Physical join algorithms: How the engine executes joins

Official references

Quick answer

FAQs

Why are Venn diagrams misleading for SQL joins?

What is an ANTI-JOIN in SQL?

What is the difference between putting a filter in ON vs WHERE in a LEFT JOIN?

How does a database choose between Hash Join, Nested Loop, and Merge Join?

Keep reading

The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability

The Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes

BigQuery STRUCT vs ARRAY: Handling Nested and Repeated Data

Key takeaways

The complete SQL join catalog

1. INNER JOIN

2. LEFT OUTER JOIN

3. RIGHT OUTER JOIN

4. FULL OUTER JOIN

5. CROSS JOIN (Cartesian Product)

6. ANTI-JOIN (Unmatched Records)

The ON vs WHERE predicate trap in outer joins

Physical join algorithms: How the engine executes joins

Related SQL guides

Official references