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

# LEFT JOIN in SQL

A `LEFT JOIN` (or `LEFT OUTER JOIN`) in SQL is used to combine rows from two or more tables based on a related column between them. It returns all rows from the left table and the matching rows from the right table. If there is no match, `NULL` values are returned for columns from the right table. This type of join ensures that all records from the left table are included in the result set, regardless of whether they have a corresponding match in the right table.

## Key Points

1. **All Rows from Left Table**: `LEFT JOIN` returns all rows from the left table, even if there are no matching rows in the right table.
2. **Matching Rows from Right Table**: If there is a match, the corresponding rows from the right table are included.
3. **NULL Values for Non-Matching Rows**: If there is no match, `NULL` values are returned for columns from the right table.
4. **Alias Support**: You can use table aliases to simplify queries.

## Syntax

```sql theme={"system"}
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column;
```

* `column1, column2, ...`: The columns you want to retrieve.
* `table1`: The left table (all rows from this table are included).
* `table2`: The right table (only matching rows from this table are included).
* `common_column`: The column that relates the two tables.

## Examples

Suppose you have two tables: `Employees` and `Departments`.

Table: Employees

| EmployeeID | Name    | City       | DepartmentID |
| ---------- | ------- | ---------- | ------------ |
| 1          | Anand   | Chennai    | 101          |
| 2          | Bala    | Coimbatore | 102          |
| 3          | Kavitha | Karaikal   | 103          |
| 4          | Raj     | Madurai    | 104          |
| 5          | Kumar   | Trichy     | NULL         |

Table: Departments

| DepartmentID | DepartmentName |
| ------------ | -------------- |
| 101          | HR             |
| 102          | Finance        |
| 103          | IT             |
| 104          | Marketing      |

Query
To retrieve all employees along with their department names (if available):

```sql theme={"system"}
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
```

Result

| Name    | DepartmentName |
| ------- | -------------- |
| Anand   | HR             |
| Bala    | Finance        |
| Kavitha | IT             |
| Raj     | Marketing      |
| Kumar   | NULL           |

Notice that `Kumar` does not have a department, so the `DepartmentName` is `NULL`.

### Using LEFT JOIN with WHERE Clause

You can combine `LEFT JOIN` with the `WHERE` clause to filter the results further. For example, retrieve employees who do not belong to any department.

```sql theme={"system"}
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
WHERE Departments.DepartmentID IS NULL;
```

| Name  | DepartmentName |
| ----- | -------------- |
| Kumar | NULL           |

### Using LEFT JOIN with Multiple Tables

You can use `LEFT JOIN` to join more than two tables. Suppose you have an additional table named `Projects`.

Table: Projects

| ProjectID | ProjectName | DepartmentID |
| --------- | ----------- | ------------ |
| 201       | Project A   | 101          |
| 202       | Project B   | 102          |
| 203       | Project C   | 103          |

To retrieve all employees, their department names, and the projects they are working on (if available):

```sql theme={"system"}
SELECT Employees.Name, Departments.DepartmentName, Projects.ProjectName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID
LEFT JOIN Projects ON Departments.DepartmentID = Projects.DepartmentID;
```

| Name    | DepartmentName | ProjectName |
| ------- | -------------- | ----------- |
| Anand   | HR             | Project A   |
| Bala    | Finance        | Project B   |
| Kavitha | IT             | Project C   |
| Raj     | Marketing      | NULL        |
| Kumar   | NULL           | NULL        |

## Practical Use Case

Suppose you have a table named `Students` and another table named `Courses`.

Table: Students

| StudentID | Name    | City       | CourseID |
| --------- | ------- | ---------- | -------- |
| 1         | Ram     | Chennai    | 101      |
| 2         | Karthik | Coimbatore | 102      |
| 3         | David   | Bangalore  | NULL     |

Table: Courses

| CourseID | CourseName  |
| -------- | ----------- |
| 101      | Mathematics |
| 102      | Science     |
| 103      | English     |

To retrieve all students along with the courses they are enrolled in (if available):

```sql theme={"system"}
SELECT Students.Name, Courses.CourseName
FROM Students
LEFT JOIN Courses ON Students.CourseID = Courses.CourseID;
```

| Name    | CourseName  |
| ------- | ----------- |
| Ram     | Mathematics |
| Karthik | Science     |
| David   | NULL        |

## Key Takeaways

1. `LEFT JOIN` returns all rows from the left table and matching rows from the right table.
2. If there is no match, `NULL` values are returned for columns from the right table.
3. It ensures that all records from the left table are included in the result set.
4. You can use `LEFT JOIN` with the `WHERE` clause to filter results further.
5. `LEFT JOIN` can be used to join multiple tables in a single query.