INNER JOIN

## The basic idea

An INNER JOIN combines rows from two tables when those rows satisfy a specified condition.

Suppose an online store has two tables.

**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 contains customer details. The orders table contains order details and the ID of the customer who placed each order.

We want to display each order with the customer's name.

The customer's name and order amount are stored in different tables, so we need to join them.

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

### What does the query do?

1. Reads rows from customers.
2. Reads rows from orders.
3. Compares the customer IDs using the ON condition.
4. Returns the combinations that match.
5. Displays the selected columns.

### Expected output

| customer_name | order_id | amount |
|---|---:|---:|
| Asha | 101 | 1500 |
| Asha | 102 | 800 |
| Rahul | 103 | 2200 |

Meera does not appear because no order has customer_id 3.

Asha appears twice because two orders match her customer ID.

**Remember:** INNER JOIN does not mean one row per customer. It means one result row per matching row combination.

The general syntax is:

```sql
SELECT
table1.column_name,
table2.column_name
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
```

Understand each part:

- **SELECT:** Chooses the columns to display.
- **FROM:** Specifies the first table.
- **INNER JOIN:** Adds another table and keeps matching row combinations.
- **ON:** Specifies the condition used to match rows.

### A shorter version

In PostgreSQL, JOIN without a specified join type means INNER JOIN.

These queries are equivalent:

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

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

Using INNER JOIN explicitly is useful while learning because it makes the join type clear.

### Why is ON important?

This condition:

```sql
ON c.customer_id = o.customer_id
```

tells the database which customer is associated with each order.

Without an appropriate matching condition, the query may produce unrelated combinations or incorrect results.

The ON condition does not have to compare a primary key directly with a foreign key in every possible query. However, using the intended relationship between the tables is a common and reliable pattern.

Run the following setup in a PostgreSQL practice database to reproduce the examples in this lesson.

These statements create new tables. If tables with these names already exist, use a separate practice database or rename the example tables before running the script.

### Step 1: Create customers

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

### Step 2: Create orders

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

Here, each order must reference an existing customer because customer_id is both NOT NULL and a foreign key.

### Step 3: Insert 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);
```

### Step 4: Run the join

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

The output should contain three rows.

### Why use a practice database?

It lets you experiment with queries without risking important application data. You can also change the sample data and predict how the result will change before running the query.

A table alias is a temporary name used for a table within a query.

For example:

```sql
FROM customers AS c
INNER JOIN orders AS o
```

Here:

- c refers to customers.
- o refers to orders.

Instead of writing:

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

You can write:

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

Both queries express the same join.

### Why use aliases?

**1. Less typing**

Long table names can make complex queries difficult to read.

**2. Clear column ownership**

Different tables may contain columns with the same name, such as customer_id or created_at.

Writing c.customer_id makes it clear which table you mean.

**3. Easier multi-table joins**

When a query joins several tables, aliases help you follow the relationships.

### Can you use any alias?

Yes, provided it follows the SQL identifier rules and does not conflict with another name in the same query scope.

For example:

```sql
FROM customers AS customer
INNER JOIN orders AS order_record
ON customer.customer_id = order_record.customer_id
```

You can also omit the AS keyword for a table alias:

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

Using AS is often clearer for beginners.

**Important:** An alias does not rename the underlying database table. It applies within the query.

After joining tables, you can select columns from either table.

For example:

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

This returns customer information and order information together.

### Why qualify column names?

Imagine both tables have a column called customer_id.

Writing:

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

may cause an ambiguous-column error because PostgreSQL cannot determine which table's customer_id you intended.

Instead, write:

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

Now both columns have clear names in the result.

### Should you use SELECT *?

You can write:

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

This returns columns from both tables, including both copies of customer_id.

It can be useful when exploring a small practice dataset, but explicitly selecting the needed columns is usually easier to maintain in application code and reports.

### Output aliases

You can also give output columns more descriptive names:

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

These output aliases affect the column labels in the query result. They do not change the table schema.

Many real applications require information from multiple related tables.

Suppose the database contains four tables:

**customers**

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

**orders**

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

**order_items**

| order_id | product_id | quantity |
|---:|---:|---:|
| 101 | 501 | 2 |
| 101 | 502 | 1 |
| 102 | 501 | 3 |

**products**

| product_id | product_name |
|---:|---|
| 501 | Keyboard |
| 502 | Mouse |

We want to show the customer name, order ID, product name, and quantity.

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

### How does this query work?

**First JOIN:** Connect each order to its customer.

```sql
INNER JOIN orders AS o
ON c.customer_id = o.customer_id
```

**Second JOIN:** Connect each order to its order items.

```sql
INNER JOIN order_items AS oi
ON o.order_id = oi.order_id
```

**Third JOIN:** Connect each order item to its product.

```sql
INNER JOIN products AS p
ON oi.product_id = p.product_id
```

Each additional JOIN introduces another table and a condition defining its relationship to the existing rows.

### Expected output

| customer_name | order_id | product_name | quantity |
|---|---:|---|---:|
| Asha | 101 | Keyboard | 2 |
| Asha | 101 | Mouse | 1 |
| Rahul | 102 | Keyboard | 3 |

### Important: Understand the result's grain

Each row represents an order item, not just an order.

Order 101 appears twice because it contains two different products.

If you need one row per order, you may need to aggregate the order items. If you need one row per customer, you may need to aggregate by customer.

Always decide what one output row should represent before joining multiple tables.

You can combine INNER JOIN with WHERE to filter the matching rows.

Suppose you want only orders whose amount is greater than 1000.

```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
WHERE o.amount > 1000
ORDER BY o.amount DESC;
```

**Expected output**

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

Order 102 is excluded because its amount is 800.

### Filter by a 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
WHERE c.customer_name = 'Asha';
```

This returns Asha's matching orders.

### Can a join have multiple conditions?

Yes. You can use AND in the ON condition when several requirements define a match.

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

For an INNER JOIN, a condition like this can often be placed in WHERE instead without changing the result, provided the expression is moved without changing its meaning. Keeping relationship conditions in ON and general result filters in WHERE can make the query easier to understand.

Do not assume the same placement is interchangeable for LEFT JOIN. Outer joins have different row-preservation rules.

A frequent beginner mistake is expecting every customer to appear exactly once.

Consider Asha:

| customer_id | customer_name |
|---:|---|
| 1 | Asha |

Her orders:

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

When these tables are joined, Asha matches both order rows. Therefore, the output contains two rows for Asha.

This is expected behavior.

### One-to-one

Each row matches at most one row on the other side.

### One-to-many

One customer can match many orders. This is our example.

### Many-to-many

A student may take multiple courses, and each course may have multiple students. A junction table such as enrollments connects them.

Joining these tables can produce multiple rows per student.

### How can you verify the result?

Count the joined rows:

```sql
SELECT COUNT(*) AS joined_row_count
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;
```

Count the matching orders for each customer:

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

This second query returns only customers with at least one matching order because it uses INNER JOIN.

### What if you need all customers?

Use LEFT JOIN when customers without orders must also appear. That distinction is covered in the preceding lesson on understanding SQL joins.

### Error 1: Joining on the wrong columns

Incorrect:

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

This compares a customer ID with an order ID. The query may run, but it expresses the wrong relationship and may return incorrect results.

Correct:

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

### Error 2: Forgetting a join condition

A missing or incomplete relationship condition can generate far more rows than expected.

Always identify how the tables are related.

### Error 3: Ambiguous columns

Incorrect:

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

Correct:

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

### Error 4: Using DISTINCT to hide a problem

If a query returns too many rows, investigate the join condition and relationship cardinality first.

DISTINCT removes duplicate selected rows. It does not repair a faulty relationship condition.

### Error 5: Assuming unmatched rows will appear

INNER JOIN excludes unmatched rows. If every customer or every product must appear regardless of matches, choose an appropriate outer join instead.

### Error 6: Selecting every column unnecessarily

SELECT * may return repeated column names and more data than needed. Choose explicit columns for a clear and stable result.

After confirming that a query returns the correct result, investigate performance if needed.

### Check the execution plan

PostgreSQL's EXPLAIN shows the planned operations:

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

EXPLAIN does not execute the SELECT query. EXPLAIN ANALYZE executes the query and reports actual measurements, so use it thoughtfully, particularly for statements that modify data.

### Consider indexes on join columns

The customers.customer_id primary key is indexed automatically.

The referencing column orders.customer_id is not automatically indexed just because it is a foreign key in PostgreSQL.

Depending on the workload, this index may help:

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

### Will the index always be used?

No. PostgreSQL may choose a sequential scan or another plan when that is estimated to be cheaper.

Performance depends on factors such as:

- Table size.
- Number of matching rows.
- Data distribution.
- Available indexes.
- Statistics and estimated costs.
- Columns selected and additional filters.

Learn to read execution plans before making assumptions about query speed.

Use the customers and orders tables from this lesson.

### Exercise 1: Basic join

Display customer_name, order_id, and amount for matching customer-order pairs.

Sort the output by order ID.

### Exercise 2: Select one customer

Display all matching orders for the customer named Rahul.

Return the customer name, order ID, and amount.

### Exercise 3: Use aliases

Rewrite Exercise 1 using the aliases c for customers and o for orders.

### Exercise 4: Filter by amount

Display matching orders whose amount is greater than 1000.

Show the customer's name, order ID, and amount.

### Exercise 5: Join three tables

Assume products and order_items exist with the columns described earlier.

Display customer name, order ID, product name, and quantity.

### Exercise 6: Count orders

Display each customer who has at least one order and the number of orders they placed.

### Exercise 7: Predict the result

A customer has four matching orders. How many rows can that customer contribute to a standard customer-to-orders INNER JOIN?

### Exercise 8: Find the bug

A developer writes:

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

Explain the problem and correct it.

Try to solve the exercises yourself before checking the solutions.

### Solution 1: Basic join

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

### Solution 2: Orders for Rahul

```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
WHERE c.customer_name = 'Rahul'
ORDER BY o.order_id;
```

### Solution 3: Use aliases

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

JOIN without a specified type means INNER JOIN.

### Solution 4: Orders above 1000

```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
WHERE o.amount > 1000
ORDER BY o.amount DESC;
```

### Solution 5: Join three tables after orders

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

This assumes the illustrative tables and columns have been created.

### Solution 6: Count orders per customer

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

INNER JOIN excludes customers without orders. GROUP BY produces one summary row per customer who has matching orders.

### Solution 7: Four matching orders

The customer can contribute four result rows because each order forms a separate matching pair.

### Solution 8: Correct the join condition

The original query compares customer_id to order_id. These columns represent different things.

Correct version:

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

The query now matches a customer's ID with the customer ID stored in each order.

Choose the best answer for each question, then read the explanation.

### 1. What is INNER JOIN?

INNER JOIN combines rows from two tables when the join condition evaluates to TRUE. Unmatched rows are excluded.

### 2. What is the difference between JOIN and INNER JOIN?

In PostgreSQL, JOIN without a specified type means INNER JOIN. They have the same behavior in this context.

### 3. What is the purpose of ON?

ON defines the predicate used to determine which row combinations match.

### 4. Why are aliases useful?

Aliases shorten table references, improve readability, and help distinguish columns from different tables.

### 5. Can INNER JOIN combine more than two tables?

Yes. You can add multiple JOIN clauses, each with its own condition connecting the new table to the existing query.

### 6. Why might a customer appear several times after a join?

The customer may have several matching orders. Each matching customer-order pair produces a result row.

### 7. Does INNER JOIN remove duplicate values automatically?

No. It returns matching row combinations. Repeated values in the selected columns can be legitimate.

### 8. What is a Cartesian product?

It is the result of pairing every row from one input with every row from another. CROSS JOIN explicitly produces this result.

### 9. Can you use WHERE with INNER JOIN?

Yes. WHERE filters the rows produced by the join and other FROM-clause operations.

### 10. Can a join condition contain more than one predicate?

Yes. You can combine conditions using AND or OR, provided the resulting logic expresses the intended relationship.

### 11. Why can unqualified column names cause errors?

If multiple tables contain a column with the same name, PostgreSQL may not know which column you intended. Qualify the column with its table name or alias.

### 12. Does INNER JOIN require a foreign key constraint?

No. The join uses the condition written in the query. Foreign keys can enforce data relationships, but SQL can join tables without them.

### 13. What happens if the ON condition is incorrect?

The query may return unrelated combinations, miss valid matches, or produce more or fewer rows than intended.

### 14. How do you find the number of matching row combinations?

Use COUNT(*) over the joined result, or group by an appropriate key when you need counts per entity.

### 15. How is INNER JOIN different from LEFT JOIN?

INNER JOIN returns matching row combinations only. LEFT JOIN also preserves every row from its left input, using NULLs for unmatched right-side columns.

### 16. Does a primary key automatically have an index in PostgreSQL?

Yes. A primary-key constraint automatically creates a unique B-tree index by default, unless a different index method is explicitly specified where supported.

### 17. Does a foreign key automatically index the referencing column?

No. Consider creating an index when the application's queries or referential actions benefit from it.

### 18. How do you investigate a slow join?

Inspect EXPLAIN output, check actual row counts when safe with EXPLAIN ANALYZE, review statistics and indexes, and test with realistic data.

### 19. Why should SELECT * be used carefully in multi-table joins?

It may return unnecessary columns and multiple columns with the same name, making application code and reports harder to maintain.

### 20. What should you identify before writing a multi-table join?

Identify the intended grain of the result, the relationships between the tables, and the condition for every join. This helps prevent incorrect row multiplication.

## Summary

- INNER JOIN retrieves matching row combinations from related tables.
- The ON clause specifies the matching condition.
- JOIN without an explicit type means INNER JOIN in PostgreSQL.
- Table aliases make multi-table queries easier to read.
- Qualify column names when ambiguity is possible.
- Joining three or more tables requires a clear condition for each additional table.
- One-to-many relationships can produce multiple output rows for one entity.
- WHERE filters the joined result.
- DISTINCT should not be used to conceal incorrect join logic.
- EXPLAIN helps you inspect PostgreSQL's planned query execution.
- Indexes can help some joins, but their usefulness depends on the query and data.

## Before moving to the next lesson

- [ ] Write an INNER JOIN between customers and orders.
- [ ] Explain why a customer without an order is excluded.
- [ ] Use aliases for both joined tables.
- [ ] Select columns from multiple tables.
- [ ] Write a query that joins at least three tables.
- [ ] Explain why one customer can appear in several result rows.
- [ ] Identify and correct a wrong join condition.
- [ ] Explain how to investigate a slow join using EXPLAIN.

**Next step:** Learn LEFT JOIN in depth so you can retrieve every row from one table, even when related records do not exist.

Review these pitfalls before using INNER JOIN in application queries.