> ## Documentation Index
> Fetch the complete documentation index at: https://fastapi2day.codewithsiva.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Joins: Combining Data Across Relational Tables

> Learn how INNER JOIN and LEFT JOIN work to combine related tables, with practical examples using the employee and department tables.

In a well-designed relational database, related data is deliberately spread across multiple tables to eliminate redundancy. The `employee` table stores a `department_id` rather than the full department name, because storing the name in every employee row would mean updating hundreds of rows every time a department was renamed. **JOINs** are the mechanism that bring those separate tables back together when you need to query them — letting you retrieve the employee's name and the department's name in a single result set. This chapter covers the two most important join types: `INNER JOIN` and `LEFT JOIN`.

## Reference Tables

All examples in this chapter use these tables.

**Department:**

| Department ID | Department Name |
| ------------- | --------------- |
| 1             | Engineering     |
| 2             | HR              |
| 3             | Sales           |
| 4             | Finance         |

**Employee:**

| Employee ID | Employee Name | Salary | Department ID |
| ----------- | ------------- | ------ | ------------- |
| 101         | Rahul         | 65000  | 1             |
| 102         | Anitha        | 55000  | 2             |
| 103         | Kiran         | 72000  | 1             |
| 104         | Sneha         | 50000  | 3             |
| 105         | Ajay          | 80000  | NULL          |

<Note>
  **Ajay** has no department assigned (`NULL`). His row will behave differently depending on which join type you use — watch for this in the examples below.
</Note>

## Why Do We Need JOINs?

Without a JOIN, a query against the `employee` table returns only the numeric `department_id`, not the human-readable department name:

```sql theme={null}
SELECT * FROM employee;
```

| Employee ID | Employee Name | Salary | Department ID |
| ----------- | ------------- | ------ | ------------- |
| 101         | Rahul         | 65000  | 1             |
| 102         | Anitha        | 55000  | 2             |
| 103         | Kiran         | 72000  | 1             |
| 104         | Sneha         | 50000  | 3             |
| 105         | Ajay          | 80000  | NULL          |

To see the department name alongside each employee, you JOIN the two tables on their shared key column.

## JOIN Syntax

```sql theme={null}
SELECT columns
FROM table1
JOIN table2
ON table1.common_column = table2.common_column;
```

* `JOIN` names the second table to include.
* `ON` specifies the matching condition — usually a **foreign key** in one table equalling a **primary key** in the other.
* Table aliases (like `e` and `d`) are commonly used to keep queries concise.

<Accordion title="Practice: Which clause specifies the matching condition?">
  The `ON` clause specifies how two tables should be matched.

  ```sql theme={null}
  ON employee.department_id = department.department_id
  ```
</Accordion>

## INNER JOIN

An `INNER JOIN` returns **only the rows that have a matching value in both tables**. If a row in either table has no match in the other, it is excluded from the result entirely.

### Example 1: Employee names with department names

```sql theme={null}
SELECT
    e.employee_name,
    d.department_name
FROM employee e
INNER JOIN department d
ON e.department_id = d.department_id;
```

**Result:**

| Employee Name | Department Name |
| ------------- | --------------- |
| Rahul         | Engineering     |
| Anitha        | HR              |
| Kiran         | Engineering     |
| Sneha         | Sales           |

Notice that **Ajay is not included** — his `department_id` is NULL, so there is no matching row in the Department table.

### Example 2: High-earners with their department

```sql theme={null}
SELECT
    e.employee_name,
    e.salary,
    d.department_name
FROM employee e
INNER JOIN department d
ON e.department_id = d.department_id
WHERE e.salary > 60000;
```

**Result:**

| Employee Name | Salary | Department Name |
| ------------- | ------ | --------------- |
| Rahul         | 65000  | Engineering     |
| Kiran         | 72000  | Engineering     |

<Accordion title="Practice: Employees in the HR department">
  Display all employees who work in HR.

  ```sql theme={null}
  SELECT
      e.employee_name,
      d.department_name
  FROM employee e
  INNER JOIN department d
  ON e.department_id = d.department_id
  WHERE d.department_name = 'HR';
  ```
</Accordion>

<Accordion title="Practice: Employees earning more than ₹70,000 with departments">
  ```sql theme={null}
  SELECT
      e.employee_name,
      e.salary,
      d.department_name
  FROM employee e
  INNER JOIN department d
  ON e.department_id = d.department_id
  WHERE e.salary > 70000;
  ```
</Accordion>

## LEFT JOIN

A `LEFT JOIN` returns **all rows from the left table**, plus any matching rows from the right table. Where no match exists in the right table, SQL fills the right table's columns with `NULL`.

### Example 1: All employees including unassigned ones

```sql theme={null}
SELECT
    e.employee_name,
    d.department_name
FROM employee e
LEFT JOIN department d
ON e.department_id = d.department_id;
```

**Result:**

| Employee Name | Department Name |
| ------------- | --------------- |
| Rahul         | Engineering     |
| Anitha        | HR              |
| Kiran         | Engineering     |
| Sneha         | Sales           |
| Ajay          | NULL            |

**Ajay now appears** with `NULL` for `department_name`, because LEFT JOIN guarantees every row from the left table (`employee`) is included.

### Example 2: All employees with salary and department

```sql theme={null}
SELECT
    e.employee_name,
    e.salary,
    d.department_name
FROM employee e
LEFT JOIN department d
ON e.department_id = d.department_id;
```

### Finding employees with no department

Combining LEFT JOIN with an `IS NULL` filter lets you find records that have no match in the right table — a common real-world need.

```sql theme={null}
SELECT
    e.employee_name
FROM employee e
LEFT JOIN department d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;
```

Alternatively, since `department_id` is directly on the employee table:

```sql theme={null}
SELECT employee_name
FROM employee
WHERE department_id IS NULL;
```

<Accordion title="Practice: Display all employees with department names">
  ```sql theme={null}
  SELECT
      e.employee_name,
      d.department_name
  FROM employee e
  LEFT JOIN department d
  ON e.department_id = d.department_id;
  ```
</Accordion>

<Accordion title="Practice: How does a LEFT JOIN differ from an INNER JOIN?">
  * **INNER JOIN** returns only rows where both tables have a matching key.
  * **LEFT JOIN** returns *all* rows from the left table, plus matching rows from the right. Where there is no match, the right-table columns contain `NULL`.

  Use an INNER JOIN when you only want complete, matched records. Use a LEFT JOIN when you want to keep all rows from the left table regardless of whether a match exists on the right.
</Accordion>

## INNER JOIN vs. LEFT JOIN — Side by Side

| Feature               | INNER JOIN                   | LEFT JOIN                      |
| --------------------- | ---------------------------- | ------------------------------ |
| Rows from left table  | Only matching rows           | **All** rows                   |
| Rows from right table | Only matching rows           | Only matching rows             |
| Unmatched left rows   | Excluded                     | Included with NULL             |
| Use when…             | You only want complete pairs | You want all left-side records |

<Tip>
  A useful mental model: think of INNER JOIN as an *intersection* (only shared records) and LEFT JOIN as *left table plus intersection* (everything from the left, matched where possible).
</Tip>