LEFT JOIN and RIGHT JOIN

## The problem with INNER JOIN

In the previous lesson, you learned that INNER JOIN returns only matching row combinations.

But what if you need to show every customer, including customers who have never placed an order?

Consider these tables.

**customers**

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

**orders**

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

Asha has two orders, Rahul has one, and Meera and Karan have none.

An INNER JOIN would exclude Meera and Karan because they have no matching orders.

For a customer report, that may be undesirable. We want all customers to appear.

This is where LEFT JOIN becomes useful.

### What is an outer join?

An outer join returns matching rows and also preserves unmatched rows from one designated side.

- **LEFT JOIN:** Preserves all rows from the left input.
- **RIGHT JOIN:** Preserves all rows from the right input.

The side that is preserved determines which unmatched records remain in the result.

### Important terminology

LEFT OUTER JOIN is equivalent to LEFT JOIN.

RIGHT OUTER JOIN is equivalent to RIGHT JOIN.

The word OUTER is optional in PostgreSQL.

## Basic syntax

```sql
SELECT
left_table.column_name,
right_table.column_name
FROM left_table
LEFT JOIN right_table
ON left_table.matching_column = right_table.matching_column;
```

The left table is the table immediately after FROM. The right table is the table introduced by LEFT JOIN.

### Example: Show every customer and their orders

```sql
SELECT
c.customer_id,
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_id | customer_name | order_id | amount |
|---:|---|---:|---:|
| 1 | Asha | 101 | 1500 |
| 1 | Asha | 102 | 800 |
| 2 | Rahul | 103 | 2200 |
| 3 | Meera | NULL | NULL |
| 4 | Karan | NULL | NULL |

### Understand the result

**Asha:** Two matching orders exist, so she appears twice.

**Rahul:** One matching order exists, so he appears once.

**Meera:** No order matches customer_id 3. Her customer row remains, and the order columns contain NULL.

**Karan:** No order matches customer_id 4. His customer row also remains.

### The key rule

LEFT JOIN preserves the left-side row even if no right-side match exists.

It does not invent an actual order for Meera or Karan. The NULL values indicate that no matching order row was found.

Use this setup in a PostgreSQL practice database to run the lesson examples.

If you already have tables named customers or orders, use a separate practice database or rename the example tables before running this setup.

### 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 NOT NULL
REFERENCES customers(customer_id),
amount NUMERIC(10, 2) NOT NULL
);
```

The foreign key ensures that each non-NULL order customer ID references an existing customer. Here, NOT NULL additionally requires every order to have a customer.

### Insert sample data

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

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

### Verify the tables

```sql
SELECT * FROM customers ORDER BY customer_id;

SELECT * FROM orders ORDER BY order_id;
```

These are the four customers and three orders used throughout the lesson.

If you change the sample data, your query output will change too. That is useful for testing your understanding of matched and unmatched rows.

RIGHT JOIN works like LEFT JOIN, but it preserves every row from the table on the right.

### Syntax

```sql
SELECT
left_table.column_name,
right_table.column_name
FROM left_table
RIGHT JOIN right_table
ON left_table.matching_column = right_table.matching_column;
```

### Example

Suppose you want to preserve every order even if the corresponding customer information is unavailable.

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

With our sample data, all three orders have matching customers, so the result contains three rows.

### What does RIGHT JOIN preserve?

It preserves every row from orders, because orders is on the right side of RIGHT JOIN.

If an order has no matching customer, its order information still appears, while the customer columns become NULL.

In our practice schema, the foreign key prevents an order from referencing a nonexistent customer. However, RIGHT JOIN is still useful to understand, and it can be helpful when joining tables without such constraints or when working with legacy data.

### LEFT JOIN versus RIGHT JOIN

| LEFT JOIN | RIGHT JOIN |
|---|---|
| Preserves every left-side row | Preserves every right-side row |
| Unmatched right-side columns are NULL | Unmatched left-side columns are NULL |
| Often easier to read from the main entity outward | Can be less familiar in complex queries |

A common convention is to use LEFT JOIN and reorder the tables when the query becomes easier to understand that way.

For a simple two-table join, you can often rewrite a RIGHT JOIN as a LEFT JOIN by reversing the table order and keeping the same matching relationship.

Consider this query:

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

An equivalent LEFT JOIN formulation is:

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

Both queries preserve every order and return matching customer information where available.

### Why is LEFT JOIN commonly preferred?

LEFT JOIN makes it natural to place the main entity first and then add related tables.

For example:

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

This reads from the customer outward to related records.

The preference is mainly about consistency and readability, not because RIGHT JOIN is inherently slower or incorrect.

**Important:** When rewriting a complex query, do not blindly reverse one join. Review each join's preserved side, the order of operations, and any ON or WHERE conditions. Outer joins are sensitive to these details.

One of the most useful applications of LEFT JOIN is finding records that do not have a related record.

Suppose you want to find customers who have never placed an order.

### Query

```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
ORDER BY c.customer_id;
```

### Expected output

| customer_id | customer_name |
|---:|---|
| 3 | Meera |
| 4 | Karan |

### Why does this work?

1. LEFT JOIN keeps every customer.
2. Customers with orders have matching order IDs.
3. Customers without orders receive NULL for o.order_id.
4. WHERE o.order_id IS NULL selects the unmatched customers.

The query uses order_id because it is the primary key of orders and therefore cannot be NULL in an actual order row. This makes it a reliable indicator of an unmatched row.

### Other practical uses

The same pattern can find:

- Products that have never been ordered.
- Students who have not enrolled in a course.
- Employees who have not been assigned to a project.
- Departments with no employees.
- Accounts without transactions.

For each case, choose the left table containing the records you want to preserve, then check a suitable non-nullable key from the right table.

### An alternative: NOT EXISTS

PostgreSQL also supports an anti-join pattern using 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
)
ORDER BY c.customer_id;
```

Both forms express the requirement to find customers with no matching orders. The best choice depends on readability and the broader query. The optimizer may produce similar plans, but that is not guaranteed.

This is one of the most important outer-join concepts.

The ON clause determines which rows match. WHERE filters the rows that remain after the join.

### Example A: Condition in ON

Show every customer, but only match orders greater than 1000.

```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;
```

Expected result:

| customer_name | order_id | amount |
|---|---:|---:|
| Asha | 101 | 1500 |
| Rahul | 103 | 2200 |
| Meera | NULL | NULL |
| Karan | NULL | NULL |

Asha's order 102 is not eligible to match because its amount is 800. Meera and Karan remain because LEFT JOIN preserves every customer.

### Example B: Condition in WHERE

```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
WHERE o.amount > 1000
ORDER BY c.customer_id, o.order_id;
```

This returns only the qualifying matching orders. Meera and Karan are removed because their NULL amounts do not satisfy the WHERE condition.

### The rule

- Use ON when a condition should limit which right-side rows qualify as matches while preserving left-side rows.
- Use WHERE when a condition should filter the resulting rows.

Do not assume moving a condition between ON and WHERE preserves the result of an outer join.

LEFT JOIN is often combined with GROUP BY to create reports.

Suppose you want the number of orders for every customer, including customers with zero orders.

```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;
```

Expected output:

| customer_id | customer_name | order_count |
|---:|---|---:|
| 1 | Asha | 2 |
| 2 | Rahul | 1 |
| 3 | Meera | 0 |
| 4 | Karan | 0 |

### Why COUNT(o.order_id), not COUNT(*)?

For a customer with no orders, LEFT JOIN still creates one output row for that customer. The columns from orders are NULL in that row.

- COUNT(*) counts the output row, producing 1 for a customer without orders.
- COUNT(o.order_id) counts non-NULL order IDs, producing 0.

This distinction is essential when reporting counts of matching child records.

### A related warning

If a customer is joined to both orders and another one-to-many table, such as support tickets, the resulting rows may multiply.

For example, two orders and three tickets could produce six combinations for the same customer. Counting orders after that join could incorrectly return six.

When joining multiple one-to-many relationships, consider preaggregating each child table separately before joining the summaries.

NULL in a LEFT JOIN result can mean that no matching right-side row was found. However, NULL can also exist in a real database row if the corresponding column permits it.

These situations are not always the same.

### Why checking a key matters

This is a reliable unmatched-row test when order_id is a non-nullable primary key:

```sql
WHERE o.order_id IS NULL
```

But consider this query:

```sql
WHERE o.amount IS NULL
```

It can select unmatched customers, but it could also select an actual matching order if amount were allowed to be NULL.

In our example schema, amount is NOT NULL, so either test would identify unmatched orders. In a different schema, choose the right-side primary key or another column guaranteed to be non-NULL in actual records.

### Can you test NULL with equals?

No. Use IS NULL and IS NOT NULL.

Incorrect:

```sql
WHERE o.order_id = NULL
```

Correct:

```sql
WHERE o.order_id IS NULL
```

Ordinary comparisons involving NULL evaluate to UNKNOWN rather than TRUE or FALSE. IS NULL is the proper test for missing values.

### Does NULL always mean the data is missing?

Not necessarily. NULL may represent an unknown, unavailable, or inapplicable value, depending on the schema. In an outer join, a NULL-extended right-side column specifically indicates that no matching right-side row was found, but only when the query's join logic is understood correctly.

You can use more than one LEFT JOIN in a query.

Suppose you have:

- customers: customer details.
- orders: customer orders.
- support_tickets: customer support requests.

You want every customer, their order IDs, and any support ticket IDs.

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

This example assumes support_tickets contains ticket_id and customer_id.

### Be careful with row multiplication

Suppose Asha has:

- Two orders.
- Three support tickets.

Because both relationships are one-to-many, the join can return six combinations for Asha.

That may be correct if you want every order-ticket combination. It is not correct if you intend to display a single customer row with independent order and ticket counts.

### How to avoid inflated aggregates

Aggregate each relationship separately, then join the summaries:

```sql
WITH order_counts AS (
SELECT
customer_id,
COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
),
ticket_counts AS (
SELECT
customer_id,
COUNT(*) AS total_tickets
FROM support_tickets
GROUP BY customer_id
)
SELECT
c.customer_id,
c.customer_name,
COALESCE(oc.total_orders, 0) AS total_orders,
COALESCE(tc.total_tickets, 0) AS total_tickets
FROM customers AS c
LEFT JOIN order_counts AS oc
ON c.customer_id = oc.customer_id
LEFT JOIN ticket_counts AS tc
ON c.customer_id = tc.customer_id
ORDER BY c.customer_id;
```

Each summary contains at most one row per customer_id, so joining the summaries avoids multiplying order counts by ticket counts.

COALESCE converts the NULL count from an unmatched summary row to zero.

Correctness is the first priority. Once the query returns the intended rows, inspect its performance if necessary.

### Indexes on join columns

PostgreSQL automatically creates an index for a primary key. It does not automatically create an index on a referencing foreign-key column.

For example, this index may help queries that join or filter orders by customer ID:

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

Whether it helps depends on the query, the number of rows, data distribution, and the execution plan.

### Inspect the plan

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

EXPLAIN shows the planned operations without executing the SELECT statement.

EXPLAIN ANALYZE executes the query and reports actual measurements. Use it carefully for statements that modify data, because their effects can occur during execution.

### Remember

- An index does not guarantee that PostgreSQL will use it.
- LEFT JOIN is not automatically slower than INNER JOIN.
- RIGHT JOIN is not automatically slower than LEFT JOIN.
- Readability, correctness, realistic data, and the execution plan matter more than assumptions about syntax.

Use the customers and orders tables from this lesson.

### Exercise 1: Show every customer

Return every customer name and their order ID, including customers who have no orders.

### Exercise 2: Find customers without orders

Return the customer ID and customer name for customers who have never placed an order.

### Exercise 3: Filter matching orders

Return every customer, but only match orders with an amount greater than 1000.

Customers without a qualifying order must remain in the result.

### Exercise 4: Count orders

Return each customer's name and order count. Customers without orders should have a count of zero.

### Exercise 5: Rewrite RIGHT JOIN

Rewrite this query using LEFT JOIN without changing which table's rows are preserved.

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

### Exercise 6: Predict the output

If a customer has two matching orders, how many rows can that customer contribute to a LEFT JOIN between customers and orders?

### Exercise 7: Find the issue

Why might this query fail to return customers without orders?

```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;
```

### Exercise 8: Count correctly

Why is COUNT(o.order_id) usually preferable to COUNT(*) when counting orders after a LEFT JOIN?

### Solution 1: 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;
```

### Solution 2: 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
ORDER BY c.customer_id;
```

### Solution 3: Only match 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 is in ON, so customers remain even if they have no qualifying order.

### Solution 4: Count orders

```sql
SELECT
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) ignores the NULL order IDs created for unmatched customers.

### Solution 5: Rewrite RIGHT JOIN

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

Orders is now the left table, so every order is preserved.

### Solution 6: Two matching orders

The customer can contribute two rows, one for each matching order.

### Solution 7: The WHERE condition

The condition o.amount > 1000 evaluates to UNKNOWN when o.amount is NULL. WHERE retains only rows for which the condition is TRUE, so unmatched customers are removed.

Put the amount condition in ON if all customers must remain.

### Solution 8: COUNT(o.order_id)

COUNT(*) counts the NULL-extended row for a customer without orders. COUNT(o.order_id) ignores the NULL order ID and correctly returns zero.

Choose the best answer for each question and read the explanation.

### 1. What is LEFT JOIN?

LEFT JOIN returns all rows from the left input and matching rows from the right input. Unmatched right-side columns contain NULL.

### 2. What is RIGHT JOIN?

RIGHT JOIN returns all rows from the right input and matching rows from the left input. Unmatched left-side columns contain NULL.

### 3. What is the difference between LEFT JOIN and LEFT OUTER JOIN?

There is no difference in behavior. OUTER is optional.

### 4. Why is LEFT JOIN often preferred over RIGHT JOIN?

It often makes queries easier to read because the main table appears first and related tables are added afterward. This is a style convention, not a performance guarantee.

### 5. How do you find customers who have no orders?

Use LEFT JOIN followed by WHERE o.order_id IS NULL, or use NOT EXISTS with a correlated subquery.

### 6. Why should you check a non-nullable right-side key?

A nullable business column may be NULL in an actual matched row. A non-nullable key lets you distinguish an unmatched row reliably.

### 7. What is the difference between ON and WHERE in a LEFT JOIN?

ON determines which rows qualify as matches. WHERE filters the result and can remove unmatched rows when it tests right-side columns.

### 8. Why can LEFT JOIN return multiple rows for one customer?

A customer can match multiple orders. Each matching customer-order pair contributes a row.

### 9. What is an anti-join?

An anti-join is a query pattern that finds rows with no matching record in another table. LEFT JOIN with an IS NULL check and NOT EXISTS are common ways to express it.

### 10. Is LEFT JOIN always slower than INNER JOIN?

No. Performance depends on the query, data, indexes, and execution plan. Choose the join type based on the required result.

### 11. Does PostgreSQL automatically index a foreign key's referencing column?

No. An index may be useful for joins, lookups, and referential actions, but it is not automatically created on the referencing column.

### 12. Why can COUNT(*) be misleading after LEFT JOIN?

A parent with no matching child still contributes a NULL-extended result row. COUNT(*) counts that row, while COUNT(child_key) ignores the NULL child key.

### 13. Can RIGHT JOIN be rewritten as LEFT JOIN?

Often, yes, by reversing the two table inputs and adjusting the query accordingly. In complex queries, review outer-join semantics carefully.

### 14. What happens when WHERE child.amount > 1000 follows a LEFT JOIN?

Unmatched rows have NULL child amounts, so the comparison is UNKNOWN and WHERE removes them.

### 15. How can you preserve all customers while matching only orders above 1000?

Place the amount condition in the LEFT JOIN's ON clause along with the customer-order relationship.

### 16. What is row multiplication in a join?

It occurs when a row matches several rows in the other table or when multiple one-to-many relationships combine. It can inflate counts and sums if the result grain is not considered.

### 17. How do you avoid inflated counts across multiple child tables?

Aggregate each child relationship at the appropriate grain before joining the summaries, or use another query design that preserves the intended result grain.

### 18. How do you investigate outer-join performance in PostgreSQL?

Use EXPLAIN to inspect the plan, and EXPLAIN ANALYZE for actual execution measurements when safe. Review row counts, statistics, indexes, and filter placement.

## Summary

- LEFT JOIN preserves every row from the left input.
- RIGHT JOIN preserves every row from the right input.
- Unmatched columns on the non-preserved side become NULL.
- LEFT OUTER JOIN and LEFT JOIN mean the same thing.
- RIGHT OUTER JOIN and RIGHT JOIN mean the same thing.
- Use IS NULL on a suitable non-nullable right-side key to find unmatched records.
- Use ON to restrict eligible matches while preserving outer-join rows.
- WHERE filters the result and may remove unmatched rows.
- COUNT(child_key) is usually appropriate for counting matching child rows after LEFT JOIN.
- Multiple one-to-many joins can multiply rows and inflate aggregates.
- LEFT JOIN is often preferred for readability, but neither LEFT nor RIGHT JOIN is inherently faster.
- EXPLAIN helps you inspect PostgreSQL's planned execution strategy.

## Before moving on

- [ ] Write a LEFT JOIN that displays every customer.
- [ ] Explain why customers without orders have NULL order columns.
- [ ] Write a RIGHT JOIN that preserves every order.
- [ ] Rewrite a simple RIGHT JOIN as an equivalent LEFT JOIN.
- [ ] Find customers who have never placed an order.
- [ ] Explain why ON and WHERE can produce different outer-join results.
- [ ] Count matching orders without counting NULL-extended rows.
- [ ] Explain how multiple one-to-many joins can inflate aggregates.

**Next step:** Practice FULL OUTER JOIN, which preserves unmatched rows from both sides of a join.

Review these mistakes before using outer joins in application queries.