Inner Join - Learning Module

Loading content...

0/241

Matching Rows Only

The Heart of INNER JOIN Semantics

The defining characteristic of INNER JOIN—the feature that shapes its behavior and distinguishes it from all other join types—is its exclusive nature: only rows that find a match in both tables survive to appear in the result. Rows that cannot find a partner are silently excluded, as if they never existed.

This seemingly simple rule has profound implications. It determines what data you see and—equally important—what data you don't see. It creates logical filters without explicit WHERE clauses. It can cause entire subsets of your data to vanish from query results in ways that surprise developers who don't fully understand the matching semantics.

This page provides a rigorous examination of how INNER JOIN matching works. We'll explore the mechanics of row comparison, understand NULL's disruptive role in matching, visualize the matching process through concrete examples, and develop the mental model necessary to predict join outcomes with precision.

What You Will Master

By the end of this page, you will understand exactly how the database evaluates match conditions, why certain rows disappear from results, how NULL values affect matching, and how to predict join cardinality based on data relationships.

The Matching Algorithm Explained

Let us precisely define how INNER JOIN evaluates and produces matches. While the actual database execution uses sophisticated algorithms (hash joins, merge joins, nested loops), the logical behavior is consistent and predictable.

The Conceptual Matching Process:

Given two tables T1 and T2 and a join condition C, INNER JOIN conceptually works as follows:

Step-by-Step Matching Process

•Form Cartesian Product — Consider every possible pairing of rows: (t1_row1, t2_row1), (t1_row1, t2_row2), ..., for all combinations
•Evaluate Condition — For each pair, evaluate the join condition C. This produces TRUE, FALSE, or UNKNOWN (if NULLs are involved)
•Filter by TRUE — Only pairs where C evaluates to TRUE survive. Pairs evaluating to FALSE or UNKNOWN are discarded
•Combine Columns — For surviving pairs, merge columns from both tables into a single result row
•Return Result Set — The collection of merged rows becomes the query result

Illustration with small tables:

Consider two minimal tables:

sample_tables.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- Table: products
+------------+---------------+
| product_id | product_name  |
+------------+---------------+
|          1 | Laptop        |
|          2 | Mouse         |
|          3 | Keyboard      |
+------------+---------------+
 
-- Table: order_items
+----------+------------+----------+
| order_id | product_id | quantity |
+----------+------------+----------+
|      101 |          1 |        2 |
|      101 |          2 |        5 |
|      102 |          1 |        1 |
|      102 |          4 |        3 |  -- product_id 4 doesn't exist in products!
+----------+------------+----------+

Cartesian product (12 combinations):

products row	order_items row	Condition (p.product_id = oi.product_id)
(1, Laptop)	(101, 1, 2)	1 = 1 → TRUE ✓
(1, Laptop)	(101, 2, 5)	1 = 2 → FALSE
(1, Laptop)	(102, 1, 1)	1 = 1 → TRUE ✓
(1, Laptop)	(102, 4, 3)	1 = 4 → FALSE
(2, Mouse)	(101, 1, 2)	2 = 1 → FALSE
(2, Mouse)	(101, 2, 5)	2 = 2 → TRUE ✓
(2, Mouse)	(102, 1, 1)	2 = 1 → FALSE
(2, Mouse)	(102, 4, 3)	2 = 4 → FALSE
(3, Keyboard)	(101, 1, 2)	3 = 1 → FALSE
(3, Keyboard)	(101, 2, 5)	3 = 2 → FALSE
(3, Keyboard)	(102, 1, 1)	3 = 1 → FALSE
(3, Keyboard)	(102, 4, 3)	3 = 4 → FALSE

inner_join_result.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
-- Only 3 pairs passed the condition:
SELECT p.product_id, p.product_name, oi.order_id, oi.quantity
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id;
 
-- Result:
+------------+--------------+----------+----------+
| product_id | product_name | order_id | quantity |
+------------+--------------+----------+----------+
|          1 | Laptop       |      101 |        2 |
|          1 | Laptop       |      102 |        1 |
|          2 | Mouse        |      101 |        5 |
+------------+--------------+----------+----------+

What Disappeared

Notice what's missing: Product 3 (Keyboard) never appears—it has no order_items. Order_item for product_id 4 also vanishes—there's no such product. These exclusions happen silently, without error. Understanding this behavior is crucial for writing correct queries.

Understanding Row Exclusion

The exclusion of unmatched rows is not a side effect—it's the defining behavior of INNER JOIN. Let's examine the categories of rows that get excluded and understand why this happens.

Categories of excluded rows:

Rows Eliminated by INNER JOIN

•Left table orphans — Rows in the left table with no matching row in the right table (e.g., a product that has never been ordered)
•Right table orphans — Rows in the right table with no matching row in the left table (e.g., an order_item referencing a non-existent product)
•NULL key rows — Rows where the join column is NULL (NULL never equals anything, including another NULL)
•Condition failures — Rows that exist in both tables but fail the join condition (e.g., when using non-equi joins)

Visual representation:

Imagine two circles representing the rows from each table. The INNER JOIN result is only the intersection—where matching pairs exist:

Converting Mermaid diagram...

Practical implications:

This exclusion behavior has real consequences in application development:

Real-World Exclusion Scenarios
Scenario	What Gets Excluded	When This Matters
Customer-Orders join	Customers with no orders	When building a 'customer activity' report—new customers vanish
Employee-Department join	Employees in NULL departments	When employees haven't been assigned yet
Product-Category join	Uncategorized products	When building category browsing pages
Order-Shipment join	Unshipped orders	When tracking order fulfillment status
User-Profile join	Users without profiles	When displaying user dashboards

When Exclusion is Desirable

Sometimes you WANT unmatched rows excluded. If you're calculating 'total revenue by product', including products with no sales would add nothing. If you're showing 'employees with their managers', showing employees without managers might be confusing. The key is knowing the behavior and choosing consciously.

NULL and the Matching Problem

NULL values require special attention in join operations because of SQL's three-valued logic. This is one of the most common sources of unexpected join behavior.

The fundamental rule:

NULL never equals anything, including another NULL.

When a join condition compares two values and either (or both) is NULL, the result is UNKNOWN—not TRUE or FALSE. Since INNER JOIN only keeps rows where the condition is TRUE, all NULL comparisons fail.

Demonstration:

null_join_demo.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- Sample data with NULLs
-- employees table:
+-------------+------------+---------------+
| employee_id | name       | department_id |
+-------------+------------+---------------+
|           1 | Alice      |           100 |
|           2 | Bob        |           200 |
|           3 | Charlie    |          NULL |  -- Unassigned employee
|           4 | Diana      |           300 |
+-------------+------------+---------------+
 
-- departments table:
+---------------+-----------------+
| department_id | department_name |
+---------------+-----------------+
|           100 | Engineering     |
|           200 | Marketing       |
|          NULL | Holding         |  -- Placeholder department
+---------------+-----------------+
 
-- INNER JOIN result:
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
 
-- Result (only 2 rows!):
+-------+-----------------+
| name  | department_name |
+-------+-----------------+
| Alice | Engineering     |
| Bob   | Marketing       |
+-------+-----------------+
 
-- Missing: Charlie (NULL doesn't match anything)
-- Missing: Diana (no department_id 300)
-- Missing: Holding department (NULL doesn't match anything)

Why NULL = NULL returns UNKNOWN, not TRUE:

SQL's logic follows this reasoning: NULL represents "unknown value." Comparing two unknowns cannot definitively prove they're equal. For example:

If Alice's shoe size is unknown (NULL) and Bob's shoe size is unknown (NULL), we cannot conclude they have the same shoe size
They might have the same size, or they might not—we simply don't know
Therefore, NULL = NULL → UNKNOWN

This is semantically sound but trips up many developers expecting NULL = NULL to match.

Common Mistake

Developers often expect rows with NULL keys to match each other. They don't. If you want to include NULL-to-NULL matches, you need explicit handling: ON (t1.col = t2.col OR (t1.col IS NULL AND t2.col IS NULL)). However, this is rarely the correct solution—usually NULL keys indicate data quality issues.

Handling NULL scenarios:

Strategies for NULL in Join Columns
Scenario	Strategy	Example
Find unassigned entities	Use LEFT JOIN + IS NULL check	`LEFT JOIN ... WHERE d.department_id IS NULL`
Include NULL matches intentionally	Explicit NULL equality	`ON (e.dept_id = d.dept_id OR (e.dept_id IS NULL AND d.dept_id IS NULL))`
Replace NULL with default	COALESCE in join	`ON COALESCE(e.dept_id, 0) = COALESCE(d.dept_id, 0)`
Prevent NULLs entirely	Schema constraint	`department_id INT NOT NULL`

Understanding Join Cardinality

Cardinality refers to the number of rows produced by a join operation. Understanding how join relationships affect cardinality is essential for predicting query behavior and performance.

The three fundamental relationships:

One-to-One (1:1) Relationship

Each row in table A matches at most one row in table B, and vice versa.

Cardinality impact: Result rows ≤ MIN(rows in A, rows in B)

Example: users ↔ user_profiles (each user has exactly one profile)

one_to_one.sql
1
2
3
4
5
-- users: 1000 rows, user_profiles: 950 rows
-- Result: At most 950 rows (profiles for existing users)
SELECT u.username, p.bio
FROM users u
INNER JOIN user_profiles p ON u.user_id = p.user_id;

The Cartesian Product Trap

When join conditions don't properly constrain the relationship—or when you accidentally create many-to-many relationships—result cardinality can explode. Joining two 10,000-row tables with a faulty condition can produce 100,000,000 rows. Always verify your join conditions enforce the intended relationship.

Cardinality estimation formulas:

For planning and debugging, these approximations help:

Relationship	Approximate Result Size
1:1	MIN(table_a, table_b) × match_rate
1:N	matching_rows_in_many_table
N:M	sum of all matching pairs (can exceed both tables)

Where match_rate is the fraction of rows that have a matching partner (1.0 if all match, 0.0 if none match).

Row Duplication and Data Multiplication

A common source of incorrect results is unexpected row duplication caused by join relationships. This happens when a row from one table matches multiple rows in another.

The problem visualized:

duplication_example.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- orders table:
+----------+-------------+--------+
| order_id | customer_id | total  |
+----------+-------------+--------+
|      101 | C001        | 500.00 |
|      102 | C002        | 300.00 |
+----------+-------------+--------+
 
-- order_items table:
+---------+----------+----------------+-------+
| item_id | order_id | product        | price |
+---------+----------+----------------+-------+
|       1 |      101 | Laptop         | 400.00|
|       2 |      101 | Mouse          |  50.00|
|       3 |      101 | Keyboard       |  50.00|
|       4 |      102 | Monitor        | 300.00|
+---------+----------+----------------+-------+
 
-- Naive aggregation attempt (WRONG!):
SELECT 
    SUM(o.total) AS grand_total  -- Results in 1400, not 800!
FROM orders o
INNER JOIN order_items oi ON o.order_id = oi.order_id;
 
-- Why? Order 101 appears 3 times (once per item), order 102 appears once
-- 500 + 500 + 500 + 300 = 1800 (actually 1800, not 1400 - let me correct)
-- $500 × 3 + $300 × 1 = $1800

The Aggregation Trap

When aggregating values after a join, each row from the "one" side gets multiplied by its matching "many" side rows. Summing an order total once per order item gives wildly incorrect results. This is one of the most common SQL bugs.

Correct solutions:

Aggregate on one table before joining:

pre_aggregate.sql
1
2
3
4
5
6
7
8
9
-- Correct: Aggregate items first, then join
WITH item_totals AS (
    SELECT order_id, SUM(price) AS items_sum
    FROM order_items
    GROUP BY order_id
)
SELECT o.order_id, o.total, it.items_sum
FROM orders o
INNER JOIN item_totals it ON o.order_id = it.order_id;

Equi-Join vs. Non-Equi Join Matching

Most join conditions use equality comparisons (equi-joins), but SQL allows any boolean expression as a join condition. Understanding both types is essential.

Equi-join: Using equality (=) to match rows. This is the most common pattern.

equi_join.sql
1
2
3
4
5
6
7
8
9
10
11
12
-- Standard equi-join (equality comparison)
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d 
    ON e.department_id = d.department_id;
 
-- Multiple equality conditions (compound equi-join)
SELECT *
FROM orders o
INNER JOIN order_archive oa
    ON o.order_id = oa.order_id
    AND o.customer_id = oa.customer_id;

Non-equi join: Using inequality operators (<, >, <=, >=, <>, BETWEEN) to match rows.

non_equi_join.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Find employees earning more than their department's average
SELECT e.name, e.salary, d.department_name, d.avg_salary
FROM employees e
INNER JOIN department_stats d
    ON e.department_id = d.department_id
    AND e.salary > d.avg_salary;
 
-- Price band matching (BETWEEN)
SELECT p.product_name, p.price, pb.band_name
FROM products p
INNER JOIN price_bands pb
    ON p.price BETWEEN pb.min_price AND pb.max_price;
 
-- Temporal join: find active records at a point in time
SELECT c.name, pm.membership_level
FROM customers c
INNER JOIN plan_memberships pm
    ON c.customer_id = pm.customer_id
    AND '2024-01-15' BETWEEN pm.start_date AND pm.end_date;

Non-Equi Join Performance

Non-equi joins are often more expensive than equi-joins. Hash joins and merge joins—the fastest join algorithms—work best with equality conditions. Range conditions may force nested loop joins, which are O(n×m) in the worst case. Use non-equi joins when semantically necessary, but be aware of performance implications.

Common Non-Equi Join Patterns
Pattern	Operators	Use Case
Range lookup	`BETWEEN`, `>=` and `<=`	Tax brackets, price bands, date ranges
Inequality filter	`>`, `<`, `<>`	Find records greater/less than reference
Self-comparison	`<` on same column	Pair generation, ordering (discussed in self-join page)
Temporal validity	`>=` and `<=` on dates	Point-in-time queries, SCD Type 2 lookups

Verifying Match Completeness

When writing production queries, you should verify that your joins behave as expected. Silent row exclusion can lead to incorrect reports and subtle bugs.

Verification techniques:

Strategies for Join Verification

•Compare row counts — Check if join result has expected cardinality relative to source tables
•Use LEFT JOIN temporarily — Replace INNER with LEFT JOIN, check for NULLs in right table columns
•Find orphans explicitly — Query for rows that don't match using NOT EXISTS or LEFT JOIN + IS NULL
•Check for duplicates — Verify GROUP BY with HAVING COUNT(*) > 1 if duplicates are unexpected
•Sample and verify — Manually check a sample of results against expected behavior

verification_queries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- How many rows are we losing to the join?
SELECT 
    (SELECT COUNT(*) FROM employees) AS total_employees,
    (SELECT COUNT(*) FROM employees e
     INNER JOIN departments d ON e.department_id = d.department_id) AS matched_employees;
 
-- Find employees with no matching department
SELECT e.employee_id, e.name, e.department_id
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
 
-- Alternative: Using NOT EXISTS
SELECT e.employee_id, e.name, e.department_id
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM departments d 
    WHERE d.department_id = e.department_id
);
 
-- Find potential duplicates from join
SELECT 
    e.employee_id,
    COUNT(*) AS matches_found
FROM employees e
INNER JOIN assignments a ON e.employee_id = a.employee_id
GROUP BY e.employee_id
HAVING COUNT(*) > 1;

Data Quality Insight

Join verification often reveals data quality issues: orphaned foreign keys, missing records, unexpected duplicates. Treat these discoveries as valuable feedback about your data, not just query debugging. Clean data makes joins predictable.

Summary: Understanding Matching Semantics

We have thoroughly examined how INNER JOIN determines which rows survive into the result set. Let's consolidate the key insights:

Key Takeaways

•Only matching rows survive — INNER JOIN is an exclusive operation; unmatched rows from both tables are silently excluded.
•The matching process is condition-based — Each row pair is evaluated against the ON condition; only TRUE survives (not FALSE, not UNKNOWN).
•NULL never matches — Comparisons involving NULL yield UNKNOWN, causing those rows to be excluded. This is often unexpected.
•Cardinality depends on relationships — 1:1, 1:N, and N:M relationships produce dramatically different result sizes.
•Row duplication affects aggregations — One-to-many joins duplicate "one" side rows, breaking naive SUM/AVG calculations.
•Non-equi joins are valid but costly — Any boolean expression works, but inequality conditions may impact performance.
•Verification prevents silent bugs — Always check that your joins return the expected number of rows and don't silently drop data.

What's next:

With matching semantics mastered, the next page explores multiple join conditions—how to combine multiple predicates in the ON clause, when to use AND vs. OR, and how compound conditions affect the matching process.

Page Complete

You now understand the precise semantics of INNER JOIN matching—why rows appear or disappear, how NULL affects results, how cardinality works, and why duplication matters for aggregation. Next, we'll explore the power of multiple conditions in a single join.