SQL 33 🛢️ Relational Joins Concept

A relational database stores data in separate tables to avoid redundancy. Customers live in one table, orders in another, products in a third. Each table holds only the data relevant to its entity, and relationships between entities are represented by shared keys: an order row contains a customer_id that points to a customer row. A join is the operation that combines rows from two or more tables based on these relationships, producing a result set that spans the tables. Without joins, answering “which customers placed orders last month” would require the application to fetch customers, fetch orders, and match them in code. With joins, the database does the matching and returns the combined result.

This chapter covers the concept of joining, the role of keys and relationships, the different join types (inner, left, right, full, cross), the difference between joining in the ON clause and filtering in the WHERE clause, self-joins, and the patterns for reasoning about which join type produces which result.

Key point: A join combines rows from two tables based on a matching condition. An inner join returns only matching rows; a left join returns all rows from the left table and matching rows from the right; a right join does the opposite; a full outer join returns all rows from both. The join condition usually matches a foreign key in one table to a primary key in another. Filtering the joined result in WHERE can turn an outer join into an inner join.

---

Why joins exist

The normalization problem. Relational databases are designed to avoid storing the same data in multiple places. If every order row repeated the customer’s name, address, and phone number, updating the customer’s address would require updating every order. Instead, the customer data lives once in the customers table, and orders reference it by ID. Joins reconstruct the combined view when it is needed.

The relationship problem. Relationships between entities are the core of relational modeling. A customer has many orders (one-to-many). An order has many products (many-to-many through an order_items table). An employee has one manager, who is also an employee (self-referencing). Each relationship is represented by keys, and each is traversed by a join.

The query-completeness problem. A single table rarely answers a business question. “Which products were ordered by customers in Norway” requires three tables: customers, orders, and products. Joins make multi-table questions expressible in a single query.

The performance problem. Joins can be expensive. The database must compare rows and produce combinations, and the cost grows with table size. Indexes on the join columns, appropriate join types, and the optimizer’s choice of join algorithm (nested loop, hash, merge) all affect performance.

The correctness problem. The join type determines which rows appear. An inner join drops unmatched rows; an outer join preserves them. Choosing the wrong type produces a result that is missing rows or contains unexpected NULLs. Understanding the difference is essential for writing queries that return the intended data.

---

a. Keys and relationships

A join is based on a relationship between two tables, expressed as a condition that matches values. The most common relationship is a foreign key in one table referencing a primary key in another.

-- customers table
-- customer_id (PK), name, country

-- orders table
-- order_id (PK), customer_id (FK), total, order_date

The orders.customer_id column references customers.customer_id. To join the tables, the condition is orders.customer_id = customers.customer_id.

Relationships come in several forms. A one-to-many relationship (a customer has many orders) is the most common. A one-to-one relationship (a user has one profile) uses a unique key. A many-to-many relationship (orders and products) requires a junction table with foreign keys to both sides.

Not all joins use foreign keys. A join condition can match any comparable columns, including columns that are not formally declared as keys. But the presence of keys and indexes makes joins efficient and semantically clear.

---

b. Inner join

An inner join returns rows where the join condition matches in both tables. Rows that have no match in the other table are excluded from the result.

SELECT c.name, o.order_id, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

This returns one row for each order, with the customer’s name attached. Customers who have never placed an order do not appear, because there is no matching order row. Orders whose customer_id does not exist in the customers table also do not appear.

The INNER keyword is optional. JOIN alone means inner join in standard SQL.

FROM customers c
JOIN orders o ON c.customer_id = o.customer_id

Inner joins are the most common type. They express “give me the combinations that exist” and are the default when no outer behavior is needed.

---

c. Left join (left outer join)

A left join returns all rows from the left table and the matching rows from the right table. When a left row has no match in the right table, the right columns are filled with NULL.

SELECT c.name, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

This returns one row per customer, with order data attached when it exists. Customers with no orders appear once, with NULL for order_id and total.

The LEFT JOIN and LEFT OUTER JOIN are synonyms. The OUTER keyword is optional.

Left joins are used when the presence of the left table’s rows matters more than the presence of the right table’s rows. “List all customers and their orders, including customers with no orders” is a left join.

A common pattern is to find rows in the left table that have no match in the right table:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

This returns customers who have never placed an order. The left join preserves all customers, and the IS NULL filter keeps only those with no matching order.

---

d. Right join (right outer join)

A right join returns all rows from the right table and the matching rows from the left table. It is the mirror of the left join.

SELECT c.name, o.order_id, o.total
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

This returns one row per order, with customer data attached. Orders whose customer_id does not exist in the customers table appear with NULL for the customer columns.

Right joins are less common than left joins because the same result can be produced by swapping the table order and using a left join. Many developers prefer to use only left joins for consistency, rewriting right joins as left joins by reversing the table order.

-- Equivalent to the right join above
SELECT c.name, o.order_id, o.total
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

---

e. Full outer join

A full outer join returns all rows from both tables, matching where possible and filling with NULL where not. It combines the behavior of left and right joins.

SELECT c.name, o.order_id, o.total
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

Customers with no orders appear with NULL order columns. Orders with no matching customer appear with NULL customer columns. Rows that match appear with both sides populated.

Not all databases support FULL OUTER JOIN. MySQL does not support it directly and requires a UNION of a left join and a right join to simulate it. PostgreSQL, SQL Server, Oracle, and SQLite support it.

Full outer joins are used when both sides matter independently: “show me all customers and all orders, whether or not they match.” This is less common than inner or left joins but useful for data reconciliation and auditing.

---

f. Cross join

A cross join produces the Cartesian product of two tables: every row from the left combined with every row from the right. It has no join condition.

SELECT c.name, p.product_name
FROM customers c
CROSS JOIN products p;

If customers has 100 rows and products has 50 rows, the result has 5,000 rows. Cross joins are rarely what you want by accident, but they are useful for generating combinations: all sizes and colors, all dates and regions, all employees and training courses.

A cross join with a WHERE clause that filters is equivalent to an inner join:

-- Cross join with filter (equivalent to inner join)
SELECT c.name, o.order_id
FROM customers c
CROSS JOIN orders o
WHERE c.customer_id = o.customer_id;

This works but is less clear than writing the join condition in the ON clause. The explicit join syntax is preferred.

---

g. Joining more than two tables

A query can join any number of tables. Each join adds another table and another condition.

SELECT c.name, o.order_id, p.product_name, oi.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

This query traverses four tables: customers to orders, orders to order_items, order_items to products. Each join condition matches a foreign key to a primary key. The result combines data from all four tables.

The order of joins in the written query is not necessarily the order in which they are executed. The optimizer chooses an execution order based on statistics and available indexes. What matters for correctness is that each join condition correctly expresses the relationship.

---

h. Join conditions and filters

The ON clause specifies the join condition: which rows from one table match which rows from the other. The WHERE clause filters the joined result. The distinction matters because filtering in WHERE can change the behavior of an outer join.

Consider a left join that preserves all customers, with the intent to show each customer’s orders in 2026:

SELECT c.name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01';

The WHERE clause filters out rows where order_date is NULL, which includes the rows for customers with no orders. The result is effectively an inner join: customers with no 2026 orders disappear.

To preserve the outer join behavior, the filter must go in the ON clause:

SELECT c.name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
 AND o.order_date >= '2026-01-01';

Now the condition is part of the join: only orders from 2026 match. Customers with no 2026 orders still appear, with NULL for the order columns. The outer join behavior is preserved.

The rule: conditions on the right table of a left join belong in ON if you want to preserve unmatched left rows, and in WHERE if you want to filter them out.

---

Complete Example Session

-- ============================================
-- PART 1: CREATE SAMPLE TABLES
-- ============================================
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        VARCHAR(50),
    country     VARCHAR(50)
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total       NUMERIC(10, 2),
    order_date  DATE
);

INSERT INTO customers VALUES
(1, 'Alice',   'Norway'),
(2, 'Bob',     'Sweden'),
(3, 'Carol',   'Denmark'),
(4, 'David',   'Norway'),
(5, 'Eve',     'Finland');

INSERT INTO orders VALUES
(101, 1, 250.00, '2026-01-15'),
(102, 1, 180.00, '2026-02-20'),
(103, 2, 320.00, '2026-01-10'),
(104, 4, 95.00,  '2026-03-05'),
(105, 9, 500.00, '2026-02-01');

-- ============================================
-- PART 2: INNER JOIN
-- ============================================
SELECT c.name, o.order_id, o.total
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
-- Returns 4 rows: Alice (2), Bob (1), David (1)
-- Carol and Eve excluded (no orders)
-- Order 105 excluded (customer 9 does not exist)

-- ============================================
-- PART 3: LEFT JOIN
-- ============================================
SELECT c.name, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- Returns 6 rows: all customers, including Carol and Eve with NULLs

-- ============================================
-- PART 4: LEFT JOIN TO FIND UNMATCHED
-- ============================================
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
-- Returns: Carol, Eve

-- ============================================
-- PART 5: RIGHT JOIN
-- ============================================
SELECT c.name, o.order_id, o.total
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
-- Returns 5 rows: all orders, including order 105 with NULL customer

-- ============================================
-- PART 6: FULL OUTER JOIN
-- ============================================
SELECT c.name, o.order_id, o.total
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
-- Returns 7 rows: all customers and all orders
-- Carol and Eve with NULL orders
-- Order 105 with NULL customer

-- ============================================
-- PART 7: CROSS JOIN
-- ============================================
SELECT c.name, o.order_id
FROM customers c
CROSS JOIN orders o;
-- Returns 25 rows (5 customers × 5 orders)

-- ============================================
-- PART 8: THREE-TABLE JOIN
-- ============================================
SELECT c.name, o.order_id, o.total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE oi.quantity > 1;

-- ============================================
-- PART 9: FILTER IN WHERE BREAKS OUTER JOIN
-- ============================================
SELECT c.name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-02-01';
-- Carol and Eve disappear because their order_date is NULL
-- Effectively an inner join

-- ============================================
-- PART 10: FILTER IN ON PRESERVES OUTER JOIN
-- ============================================
SELECT c.name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
 AND o.order_date >= '2026-02-01';
-- Carol and Eve appear with NULL order columns
-- Outer join behavior preserved

These ten parts cover inner join, left join, left join with unmatched filter, right join, full outer join, cross join, three-table join, and the difference between filtering in WHERE and in ON.

---

Quick Reference

Join Types

Join	Returns
INNER JOIN	Rows matching in both tables
LEFT JOIN	All left rows + matching right rows
RIGHT JOIN	All right rows + matching left rows
FULL OUTER JOIN	All rows from both tables
CROSS JOIN	Cartesian product (all combinations)

Join Syntax

Type	Syntax
Inner	FROM a JOIN b ON a.id = b.id
Left	FROM a LEFT JOIN b ON a.id = b.id
Right	FROM a RIGHT JOIN b ON a.id = b.id
Full	FROM a FULL OUTER JOIN b ON a.id = b.id
Cross	FROM a CROSS JOIN b

ON vs WHERE

Clause	Purpose
ON	Join condition; determines matching
WHERE	Filters the joined result

Outer Join Preservation

Filter Location	Effect on Left Join
ON	Preserves unmatched left rows
WHERE	Removes unmatched left rows (becomes inner)

Unmatched Row Detection

Goal	Pattern
Left rows with no match	LEFT JOIN ... WHERE right.key IS NULL
Right rows with no match	RIGHT JOIN ... WHERE left.key IS NULL
Either side unmatched	FULL OUTER JOIN ... WHERE left.key IS NULL OR right.key IS NULL

---

Best Practices

✅ Do This:

-- Use explicit JOIN syntax
FROM customers c JOIN orders o ON c.customer_id = o.customer_id;

-- Use LEFT JOIN when left rows must appear
FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- Put row-preservation conditions in ON
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.total > 100;

-- Qualify column names with table aliases
SELECT c.name, o.total

❌ Don’t Do This:

-- Implicit join in WHERE
FROM customers c, orders o WHERE c.customer_id = o.customer_id;  -- ❌ less clear

-- Row filter in WHERE on outer join
LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.total > 100;  -- ❌ breaks outer

-- Unqualified columns in multi-table query
SELECT name, total FROM customers c JOIN orders o ON ...;  -- ❌ ambiguous

-- CROSS JOIN without intent
FROM customers CROSS JOIN orders;  -- ❌ explosive result

---

Common Pitfalls

Pitfall	Why It Happens	Fix
Missing rows after LEFT JOIN	Filter in WHERE removes NULLs	Move filter to ON
Unexpected duplicate rows	One-to-many join multiplies rows	Aggregate or use DISTINCT
Ambiguous column error	Same column name in both tables	Qualify with alias
Cross join accidentally	Missing join condition	Add ON condition
Full outer join unsupported	MySQL limitation	Use UNION of left and right
NULLs in join columns	Foreign key is NULL	NULL never matches; use IS NULL

---

Real-World Examples

1. Customers and Orders

SELECT c.name, o.order_id FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

2. All Customers, Including Those Without Orders

SELECT c.name, o.order_id FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

3. Customers Without Orders

SELECT c.name FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

4. Orders Without Valid Customers

SELECT o.order_id FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

5. Three-Table Join

SELECT c.name, p.product_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

6. Self-Join for Hierarchy

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

7. Left Join with Filter in ON

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.total > 200;

8. Full Outer Join for Reconciliation

SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

9. Cross Join for Combinations

SELECT s.size, c.color FROM sizes s CROSS JOIN colors c;

10. Count Orders per Customer with Left Join

SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name;

---

Visual

Join Types

┌──────────────────────────────────────────────────────────────┐
│  INNER JOIN          LEFT JOIN                               │
│  ┌─────┐ ┌─────┐     ┌─────┐ ┌─────┐                         │
│  │  A  │ │  B  │     │  A  │ │  B  │                         │
│  │   ┌─┴─┐     │     │   ┌─┴─┐     │                         │
│  │   │ █ │     │     │ █ │ █ │     │                         │
│  │   └─┬─┘     │     │ █ └─┬─┘     │                         │
│  │     │       │     │     │       │                         │
│  └─────┘ └─────┘     └─────┘ └─────┘                         │
│  Only overlapping    All of A + overlap                      │
│                                                              │
│  RIGHT JOIN          FULL OUTER JOIN                         │
│  ┌─────┐ ┌─────┐     ┌─────┐ ┌─────┐                         │
│  │  A  │ │  B  │     │  A  │ │  B  │                         │
│  │     │ ┌─┴─┐ │     │   ┌─┴─┐     │                         │
│  │     │ │ █ │ │     │ █ │ █ │ █   │                         │
│  │     │ └─┬─┘ │     │   └─┬─┘     │                         │
│  └─────┘ └─────┘     └─────┘ └─────┘                         │
│  All of B + overlap  All of A and B                          │
└──────────────────────────────────────────────────────────────┘

ON vs WHERE in Outer Joins

┌──────────────────────────────────────────────────────────────┐
│  FILTER IN WHERE                    FILTER IN ON             │
│                                                              │
│  LEFT JOIN orders o                 LEFT JOIN orders o        │
│    ON c.id = o.customer_id            ON c.id = o.customer_id│
│  WHERE o.total > 100                  AND o.total > 100      │
│                                                              │
│  Unmatched left rows:               Unmatched left rows:     │
│  - o.total is NULL                  - preserved with NULLs   │
│  - WHERE removes them               - not removed            │
│  - behaves like INNER JOIN          - behaves like LEFT JOIN │
└──────────────────────────────────────────────────────────────┘

Join Processing

┌──────────────────────────────────────────────────────────────┐
│  customers           orders                                  │
│  ┌────┬────────┐    ┌─────┬──────┬───────┐                  │
│  │ id │ name   │    │ oid │ cid  │ total │                  │
│  ├────┼────────┤    ├─────┼──────┼───────┤                  │
│  │ 1  │ Alice  │    │ 101 │ 1    │ 250   │                  │
│  │ 2  │ Bob    │    │ 102 │ 1    │ 180   │                  │
│  │ 3  │ Carol  │    │ 103 │ 2    │ 320   │                  │
│  └────┴────────┘    └─────┴──────┴───────┘                  │
│                                                              │
│  INNER JOIN ON c.id = o.cid:                                 │
│  ┌────────┬─────┬───────┐                                    │
│  │ Alice  │ 101 │ 250   │                                    │
│  │ Alice  │ 102 │ 180   │                                    │
│  │ Bob    │ 103 │ 320   │                                    │
│  └────────┴─────┴───────┘  Carol excluded                    │
│                                                              │
│  LEFT JOIN ON c.id = o.cid:                                  │
│  ┌────────┬─────┬───────┐                                    │
│  │ Alice  │ 101 │ 250   │                                    │
│  │ Alice  │ 102 │ 180   │                                    │
│  │ Bob    │ 103 │ 320   │                                    │
│  │ Carol  │ NULL│ NULL  │  Carol preserved with NULLs        │
│  └────────┴─────┴───────┘                                    │
└──────────────────────────────────────────────────────────────┘

---

Summary

Item	Value
Join	Combines rows from two or more tables
Join condition	Usually matches foreign key to primary key
INNER JOIN	Only matching rows
LEFT JOIN	All left rows + matching right
RIGHT JOIN	All right rows + matching left
FULL OUTER JOIN	All rows from both tables
CROSS JOIN	Cartesian product
ON	Join condition
WHERE	Filters joined result
Outer join preservation	Filter right table in ON, not WHERE
Self-join	Table joined to itself with aliases
Unmatched detection	LEFT JOIN ... WHERE right.key IS NULL

Key takeaways:

• A join combines rows from multiple tables based on a matching condition. The condition is usually a foreign key in one table matching a primary key in another. Joins let a single query answer questions that span multiple entities.
• INNER JOIN returns only matching rows. Rows with no match in the other table are dropped. This is the most common join type and the default when JOIN is written alone.
• LEFT JOIN preserves all rows from the left table. Unmatched left rows appear with NULL in the right columns. This is used when the presence of the left table’s rows matters more than whether a match exists.
• RIGHT JOIN is the mirror of LEFT JOIN. It preserves all rows from the right table. The same result can usually be produced by swapping the table order and using a LEFT JOIN.
• FULL OUTER JOIN preserves all rows from both tables. Unmatched rows on either side appear with NULLs. Not all databases support it; MySQL requires a UNION of left and right joins.
• CROSS JOIN produces the Cartesian product. Every row from the left combined with every row from the right. It has no join condition and is used for generating combinations.
• The ON clause defines matching; the WHERE clause filters the result. For outer joins, filtering the right table in WHERE removes unmatched rows and turns the outer join into an inner join. To preserve outer behavior, put the condition in ON.
• Self-joins use table aliases to distinguish the two instances. They are used for hierarchical data (employee-manager), comparisons within a table, and finding pairs.

Remember: A join is the operation that reconstructs the combined view of normalized data. Each table holds one entity’s data, and keys represent the relationships between entities. The join type determines which rows survive: inner joins keep only matches, outer joins preserve one or both sides, and cross joins produce all combinations. The join condition goes in ON; the filters go in WHERE. For outer joins, the placement of a filter determines whether unmatched rows are preserved or removed. Understanding which join type produces which result, and where to put the conditions, is the core skill of relational querying. Once you can reason about joins, you can express almost any question that spans multiple tables in a single query.

---

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!