Understanding SQL Joins

## The problem: related data is stored in different tables

In a relational database, data is commonly divided into tables so that each table represents a particular type of entity.

For example, an online store might use a customers table and an orders table.

**customers**

| customer_id | customer_name |
|---:|---|
| 1 | Asha |
| 2 | Rahul |
| 3 | Meera |

**orders**

| order_id | customer_id | amount |
|---:|---:|---:|
| 101 | 1 | 1500 |
| 102 | 1 | 800 |
| 103 | 2 | 2200 |

The customers table stores customer information. The orders table stores orders and the ID of the customer who placed each order.

Notice that customer 1 appears in two orders. This means one customer can have multiple orders.

Now suppose you want to display each order alongside the customer's name.

The orders table contains the customer's ID, but not the customer's name. The customers table contains the name, but not the order amount.

A **JOIN** lets you combine information from both tables in one query.

### The relationship

- customers.customer_id identifies a customer.
- orders.customer_id identifies the customer associated with an order.
- The matching IDs connect the rows.

This is a common one-to-many relationship: one customer can have many orders.

### Why not store everything in one table?

Repeating customer names and other customer details in every order row can cause unnecessary duplication and update inconsistencies. Separating related entities helps organize the data, while joins allow queries to bring the information together when needed.

**Important:** Normalization does not mean every piece of data must always live in a separate table. Tables should be designed around the application's data and relationships.

## How does SQL know which rows belong together?

A join usually needs a condition that describes how rows from two tables match.

That condition is written using the **ON** clause.

For our example:

```sql
SELECT
customers.customer_name,
orders.order_id,
orders.amount
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;
```

Read the query in plain English:

1. Start with the customers table.
2. Join it with the orders table.
3. Match rows where the customer's ID equals the order's customer ID.
4. Display the customer name, order ID, and amount.

### What is a join predicate?

A join predicate is the condition used to decide whether rows match.

For example:

```sql
ON customers.customer_id = orders.customer_id
```

The equality comparison is the predicate here.

Join conditions are not limited to primary-key/foreign-key equality. They can use other appropriate expressions, including multiple conditions and non-equality comparisons.

However, matching a primary key to its corresponding foreign key is one of the most common patterns.

### Table aliases make queries shorter

Instead of repeatedly writing full table names, you can use aliases:

```sql
SELECT
c.customer_name,
o.order_id,
o.amount
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;
```

Here, `c` represents customers and `o` represents orders.

Aliases are especially useful when queries involve several tables or when different tables contain columns with the same name.

### Why qualify column names?

Both tables have a column called `customer_id`. Writing only `customer_id` can be ambiguous.

Use:

```sql
c.customer_id
o.customer_id
```

This makes the intended table explicit and helps prevent ambiguity errors.

Let's build a complete example that you can run in PostgreSQL.

The following statements create two small tables and insert sample records. Run them in a practice database, not in a database containing important data with the same table names.

### Step 1: Create the tables

```sql
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id),
amount NUMERIC(10, 2) NOT NULL
);
```

The foreign key on `orders.customer_id` ensures that a non-NULL customer ID must reference an existing customer.

Because the column is not declared NOT NULL, it can still contain NULL. If every order must have a customer, add a NOT NULL constraint.

### Step 2: Insert sample data

```sql
INSERT INTO customers (customer_id, customer_name)
VALUES
(1, 'Asha'),
(2, 'Rahul'),
(3, 'Meera');

INSERT INTO orders (order_id, customer_id, amount)
VALUES
(101, 1, 1500.00),
(102, 1, 800.00),
(103, 2, 2200.00);
```

Meera has no orders in this sample.

### Step 3: Join the tables

```sql
SELECT
c.customer_name,
o.order_id,
o.amount
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id
ORDER BY o.order_id;
```

**Expected output**

| customer_name | order_id | amount |
|---|---:|---:|
| Asha | 101 | 1500.00 |
| Asha | 102 | 800.00 |
| Rahul | 103 | 2200.00 |

Meera is absent because the query uses INNER JOIN and she has no matching order.

Asha appears twice because two different orders match her customer ID. That is correct: each output row represents a matching customer-order pair.

## A join produces matching row combinations

Think of SQL as examining combinations of rows from two input tables and checking the ON condition.

For each possible pair:

- If the condition evaluates to TRUE, the pair matches.
- If it evaluates to FALSE or UNKNOWN, the pair does not satisfy the condition.

For an equality join, the customer and order rows match when their customer IDs are equal.

### A small example

Customers:

| customer_id | customer_name |
|---:|---|
| 1 | Asha |
| 2 | Rahul |
| 3 | Meera |

Orders:

| order_id | customer_id |
|---:|---:|
| 101 | 1 |
| 102 | 1 |
| 103 | 2 |

The matching pairs are:

- Customer 1 with order 101.
- Customer 1 with order 102.
- Customer 2 with order 103.

There is no matching pair for customer 3.

### Joins are not automatically one row per customer

A common misconception is that joining customers and orders returns one row per customer. It does not necessarily do so.

If a customer has five matching orders, the join can return five rows for that customer.

The number of output rows depends on the matching relationships and the join type.

If you need one row per customer, you may need aggregation, a carefully chosen subquery, or another query pattern. Adding DISTINCT without understanding the row multiplication may hide a data or query-design problem.

### Venn diagrams: a useful but incomplete model

Venn diagrams can help visualize which sets of rows are included by different join types. But a join is more precisely understood as producing row combinations that satisfy a condition.

SQL tables can contain duplicate values, and a row may match several rows on the other side. A simple Venn diagram does not fully illustrate this multiplicity.

An **INNER JOIN** returns combinations of rows for which the ON condition is TRUE.

```sql
SELECT
c.customer_name,
o.order_id
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;
```

In our example, this returns three rows:

| customer_name | order_id |
|---|---:|
| Asha | 101 |
| Asha | 102 |
| Rahul | 103 |

Meera does not appear because she has no matching order.

### When is INNER JOIN useful?

Use it when you need only records that have a matching related record.

Examples include:

- Orders together with their customers.
- Employees together with their departments.
- Enrollment records together with matching students.
- Payments that have a matching invoice.

### INNER JOIN is the default meaning of JOIN

In PostgreSQL, writing `JOIN` without a type means `INNER JOIN`.

These queries are equivalent:

```sql
SELECT c.customer_name, o.order_id
FROM customers AS c
JOIN orders AS o
ON c.customer_id = o.customer_id;
```

```sql
SELECT c.customer_name, o.order_id
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;
```

Writing INNER explicitly can make a lesson or complex query easier to read.

A **LEFT JOIN**, also called a LEFT OUTER JOIN, returns all rows from the left table.

When a left-side row has no match in the right table, the right-side columns are filled with NULL.

```sql
SELECT
c.customer_name,
o.order_id,
o.amount
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;
```

**Expected output**

| customer_name | order_id | amount |
|---|---:|---:|
| Asha | 101 | 1500.00 |
| Asha | 102 | 800.00 |
| Rahul | 103 | 2200.00 |
| Meera | NULL | NULL |

Meera is preserved because customers is the left table.

### Why are NULL values returned?

The database has no matching order row for Meera. Therefore, there is no actual order ID or amount to display for that result row.

The NULL values are generated for the unmatched right-side columns. They do not mean an order exists with a NULL order ID.

### When should you use LEFT JOIN?

Use it when every row from the left table matters, whether or not a related row exists.

Examples:

- Every customer, including customers without orders.
- Every department, including departments without employees.
- Every product, including products without sales.
- Every student, including students without enrollments.

### The direction matters

These queries are not interchangeable if you keep the same left and right tables:

```sql
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
```

This preserves all customers.

```sql
FROM orders AS o
LEFT JOIN customers AS c
ON o.customer_id = c.customer_id
```

This preserves all orders.

The preserved rows come from the table on the left side of LEFT JOIN.

The ON clause defines how rows match. The WHERE clause filters the rows produced by the FROM and JOIN operations.

These clauses can produce different results, especially with outer joins.

### Example A: Filter in ON

```sql
SELECT
c.customer_name,
o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
AND o.amount > 1000
ORDER BY c.customer_id, o.order_id;
```

This keeps every customer. Only orders above 1000 are eligible to match.

Asha matches order 101 but not order 102. Rahul matches order 103. Meera remains in the result with NULL order columns.

### Example B: Filter in WHERE

```sql
SELECT
c.customer_name,
o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE o.amount > 1000
ORDER BY c.customer_id, o.order_id;
```

The WHERE clause removes rows for which the condition is not TRUE. Meera's NULL amount makes the comparison UNKNOWN, so her row is removed. Asha's order 102 is also excluded.

This query returns only the matching orders above 1000, so for this condition its result behaves like an inner join with respect to qualifying orders.

### The rule to remember

- Put a condition in ON when it should determine which right-side rows match while preserving unmatched left-side rows.
- Put a condition in WHERE when it should filter the final result rows.

This is a practical rule, not a requirement that every condition must belong to only one clause. Choose the placement based on the intended result.

## Why can one join return more rows than expected?

The number of matches for each row determines how many result rows it can generate.

Suppose one customer has four orders. When joining that customer to the orders table, the customer data appears in four result rows.

This is not automatically a duplicate-data problem. Each row represents a different order.

### One-to-one relationship

If each row on either side matches at most one row on the other side, the join can produce at most one match per row.

A database design may enforce this using primary-key and UNIQUE constraints, depending on the relationship.

### One-to-many relationship

One customer can have many orders.

- One customer row can match many order rows.
- Each matching order appears as a separate result row.

This is the relationship in our sample data.

### Many-to-many relationship

A student can enroll in many courses, and a course can contain many students.

A junction table such as `enrollments(student_id, course_id)` stores these associations.

Joining students to enrollments and then to courses can produce one row per student-course enrollment.

### Why constraints matter

A join condition alone does not guarantee uniqueness.

If you expect one matching row but the data contains several matching rows, the result can be larger than expected. Primary keys, UNIQUE constraints, foreign keys, and a correct join predicate help express and protect the intended data model.

### Avoid hiding the problem with DISTINCT

This query may hide repeated output values:

```sql
SELECT DISTINCT c.customer_name
FROM customers AS c
JOIN orders AS o
ON c.customer_id = o.customer_id;
```

It returns each customer name once, but it also discards the order-level detail.

Use DISTINCT only when deduplicating the selected result is actually the goal. If you need one row per customer with a count of orders, use aggregation instead:

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

COUNT(o.order_id) is zero for Meera because no matching order ID exists. COUNT(*) would count the preserved LEFT JOIN row and return one for Meera, which is not the desired order count.

Be careful when joining tables without a condition.

Consider:

```sql
SELECT
c.customer_name,
o.order_id
FROM customers AS c
CROSS JOIN orders AS o;
```

A CROSS JOIN returns every possible combination of rows from the two tables.

Our sample has three customers and three orders, so the result contains:

[
3 \times 3 = 9
]

rows.

This is called a Cartesian product.

A Cartesian product is not inherently wrong. It is useful when every combination is intended, such as generating combinations of sizes and colors. However, it is usually incorrect when you intended to match customers to their own orders.

An older query style may list tables separated by commas:

```sql
SELECT c.customer_name, o.order_id
FROM customers AS c, orders AS o
WHERE c.customer_id = o.customer_id;
```

This query uses a condition to match the tables, but explicit JOIN syntax is usually clearer and easier to maintain.

### A safe habit

Whenever you add a table to a query, ask:

1. How is this table related to the existing tables?
2. What columns or expressions define a valid match?
3. Could one row match multiple rows?
4. Is a Cartesian product actually intended?

Do not assume that SQL will infer the intended relationship from foreign-key constraints. You must specify the join logic in your query.

Real applications often need information from three or more tables.

Suppose the database also has a products table and an order_items table:

- customers stores customer details.
- orders stores orders.
- order_items stores products and quantities for each order.
- products stores product details.

A typical relationship path is:

customers → orders → order_items → products

For example, the query below lists the customer, order, product, and quantity.

```sql
SELECT
c.customer_name,
o.order_id,
p.product_name,
oi.quantity
FROM customers AS c
JOIN orders AS o
ON c.customer_id = o.customer_id
JOIN order_items AS oi
ON o.order_id = oi.order_id
JOIN products AS p
ON oi.product_id = p.product_id
ORDER BY o.order_id, p.product_name;
```

This example assumes these columns and relationships exist:

- orders.customer_id references customers.customer_id.
- order_items.order_id references orders.order_id.
- order_items.product_id references products.product_id.
- products has product_id and product_name.

Those tables are illustrative; they are not created by the earlier setup statements.

### Follow the relationship chain

Each JOIN adds another table to the query. Its ON condition should connect the new table to the appropriate existing table.

If you omit or use an incorrect condition, you may create unrelated row combinations or multiply results unexpectedly.

### Think about the grain of the result

The grain is what one result row represents.

In this query, a row represents a matching order-item/product combination for a customer. An order with three different order items may therefore appear in three rows.

Before writing a multi-table query, state the intended grain in plain language. It makes it easier to check whether the query returns the correct level of detail.

Correctness comes first. After the result is correct, investigate performance if the query is slow.

### Indexes on join columns

Primary keys are indexed automatically in PostgreSQL. A foreign key does not automatically create an index on the referencing column.

For example, customers.customer_id is indexed because it is the primary key. You might also consider an index on orders.customer_id:

```sql
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);
```

This index may help queries that search or join orders by customer ID. Whether it improves a particular query depends on table size, data distribution, selectivity, and the chosen execution plan.

### Inspect the execution plan

PostgreSQL supports:

```sql
EXPLAIN
SELECT
c.customer_name,
o.order_id,
o.amount
FROM customers AS c
JOIN orders AS o
ON c.customer_id = o.customer_id;
```

EXPLAIN shows the planned operations without executing the query.

For actual execution measurements, PostgreSQL also provides EXPLAIN ANALYZE, which executes the statement. Use care with statements that modify data, because their effects can occur during execution.

### Join algorithms

PostgreSQL may choose different join strategies, including:

- **Nested loop join:** repeatedly finds matches for rows from one input using the other input.
- **Hash join:** builds a hash structure from one input and probes it with the other.
- **Merge join:** processes inputs in join-key order.

You generally should not force a particular algorithm without evidence. The database optimizer chooses a plan based on available statistics, costs, indexes, and query details.

**Remember:** An index does not guarantee that a query will be faster or that the optimizer will use it. Measure realistic queries before making tuning decisions.

Use the customers and orders tables created earlier.

### Exercise 1: Show each order with its customer

Return the customer name, order ID, and order amount. Show only matching rows and sort by order ID.

Try it before viewing the solution.

### Exercise 2: Show every customer

Return every customer, including those who have never placed an order. Display the customer name and order ID.

### Exercise 3: Count orders for every customer

Return the customer ID, customer name, and number of orders. Customers with no orders should have a count of zero.

### Exercise 4: Find customers with no orders

Return the IDs and names of customers who have no matching orders.

### Exercise 5: Filter which orders can match

Return every customer, but only match orders whose amount is greater than 1000. Keep customers who have no qualifying order.

### Exercise 6: Predict the row count

There are 4 customers and 6 orders. Every order references an existing customer. One customer has no orders.

How many rows will an INNER JOIN between customers and orders return?

### Exercise 7: Identify the problem

A developer joins customers and orders without an ON condition and gets many more rows than expected. Explain why.

### Exercise 8: Choose the right join

You need a list of all products, including products that have never been ordered. Which table should be on the left side of a LEFT JOIN?

### Solution 1: Each order with its customer

```sql
SELECT
c.customer_name,
o.order_id,
o.amount
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id
ORDER BY o.order_id;
```

INNER JOIN returns only matching customer-order pairs.

### Solution 2: Every customer

```sql
SELECT
c.customer_name,
o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;
```

LEFT JOIN preserves every customer.

### Solution 3: Count orders per customer

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

COUNT(o.order_id) counts matching non-NULL order IDs. A customer without an order receives zero.

### Solution 4: Customers without orders

```sql
SELECT
c.customer_id,
c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
```

This works because order_id is a primary key and therefore cannot be NULL in an actual order row. The NULL here identifies an unmatched right-side row.

An alternative is NOT EXISTS:

```sql
SELECT
c.customer_id,
c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);
```

### Solution 5: Keep every customer, matching only qualifying orders

```sql
SELECT
c.customer_name,
o.order_id,
o.amount
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
AND o.amount > 1000
ORDER BY c.customer_id, o.order_id;
```

The amount condition belongs in ON because customers without a qualifying order must remain.

### Solution 6: Predict the row count

The INNER JOIN returns six rows, assuming every order matches exactly one customer. Each order contributes one matching pair. The customer with no orders contributes no result row.

### Solution 7: Missing join condition

Without a matching condition, the query can produce every combination of customer and order. This is a Cartesian product. Use an appropriate ON clause or CROSS JOIN only when all combinations are intended.

### Solution 8: Products that have never been ordered

Put products on the left side:

```sql
FROM products AS p
LEFT JOIN order_items AS oi
ON p.product_id = oi.product_id
```

This preserves every product. Products without matching order items have NULL values for the order-item columns.

Choose the best answer for each question. Review the explanations to understand why the correct option works.

### 1. What is a SQL JOIN?

A JOIN combines rows from two or more tables based on a condition, allowing related information to appear in one query result.

### 2. Why are joins important in relational databases?

Related data is often stored in separate tables to reduce unnecessary duplication and represent relationships. Joins retrieve related information across those tables.

### 3. What is a join condition?

It is a predicate that determines which row combinations match. It is commonly written in the ON clause.

### 4. What is the difference between ON and WHERE?

ON determines which rows match during a join. WHERE filters rows from the resulting query input. Their placement can change the result, particularly for outer joins.

### 5. What is an INNER JOIN?

It returns row combinations that satisfy the join condition. Rows without matches are not returned.

### 6. What is a LEFT JOIN?

It returns all rows from the left input and matching rows from the right input. When no match exists, right-side columns contain NULL.

### 7. What is a Cartesian product?

It is the set of all combinations of rows from two inputs. CROSS JOIN explicitly produces this result.

### 8. Why can a join return more rows than a table contains?

A row may match multiple rows in the other table. In a one-to-many relationship, each matching pair contributes a result row.

### 9. Does a foreign key automatically create an index on its referencing column in PostgreSQL?

No. The foreign key enforces the reference relationship, but an index on the referencing column may need to be created separately.

### 10. Can a JOIN use columns other than primary keys and foreign keys?

Yes. The ON condition can use other appropriate columns or expressions. The query must still express the intended relationship correctly.

### 11. What is a table alias?

An alias is a shorter name for a table within a query. It improves readability and helps distinguish columns with identical names.

### 12. What happens when a join condition evaluates to UNKNOWN?

It does not qualify as a match. For example, comparing NULL to a value with ordinary equality returns UNKNOWN.

### 13. How can you find customers who have no orders?

Use a LEFT JOIN followed by a check that a non-nullable right-side key is NULL, or use NOT EXISTS.

### 14. Why should you be careful when filtering right-side columns after a LEFT JOIN?

A WHERE condition can remove rows that have NULL values from unmatched right-side records, eliminating the preservation that the LEFT JOIN was intended to provide.

### 15. How do you count matching child rows after a LEFT JOIN?

Use COUNT on a non-nullable child key, such as COUNT(o.order_id). COUNT(*) also counts the NULL-extended row for a parent without children.

### 16. Does JOIN automatically remove duplicate values?

No. Joins return matching row combinations. Multiple legitimate matches can produce repeated values in selected columns.

### 17. Should you always add DISTINCT after a JOIN?

No. Use it only when unique selected rows are the intended result. Investigate unexpected row multiplication instead of masking it.

### 18. What is the difference between a one-to-many and a many-to-many relationship?

In a one-to-many relationship, one row can relate to many rows on the other side. In a many-to-many relationship, rows on both sides can relate to multiple rows, often through a junction table.

### 19. How can you investigate a slow JOIN in PostgreSQL?

Inspect the execution plan with EXPLAIN, consider EXPLAIN ANALYZE for measured execution, and evaluate table statistics, row counts, indexes, and the query's filtering and join conditions.

### 20. Does creating an index guarantee that PostgreSQL will use it?

No. The optimizer chooses a plan based on estimated costs and available information. An index may not be useful for every query or data distribution.

## What you learned

- A JOIN combines related rows from tables in a query.
- Normalized tables store different entities separately, and joins retrieve related information.
- The ON clause defines the join condition.
- INNER JOIN returns matching row combinations.
- LEFT JOIN preserves every left-side row and uses NULLs when no right-side match exists.
- One row can match several rows, so joins can multiply output rows.
- CROSS JOIN produces all possible row combinations.
- ON and WHERE have different roles, particularly with outer joins.
- Multiple JOINs can follow a chain of table relationships.
- PostgreSQL does not automatically index the referencing side of a foreign key.
- EXPLAIN helps you inspect a query's planned execution.
- DISTINCT should not be used to conceal an incorrect join condition.

## Before you move to the next lesson

Make sure you can:

- [ ] Explain why data is divided across related tables.
- [ ] Write an INNER JOIN with a clear ON condition.
- [ ] Explain why an unmatched customer disappears from an INNER JOIN.
- [ ] Write a LEFT JOIN that preserves every customer.
- [ ] Predict how multiple matching rows affect the output.
- [ ] Explain the difference between ON and WHERE in an outer join.
- [ ] Recognize a Cartesian product.
- [ ] Count child rows correctly after a LEFT JOIN.
- [ ] Describe how indexes and EXPLAIN can help investigate performance.

**Next step:** Practice the same join patterns with employees and departments, students and enrollments, or products and order items. Focus on what one output row represents before adding more tables.

Avoid these mistakes when joining related tables.