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

# JOIN in SQL

A `JOIN` operator in SQL is used to combine rows from two or more tables based on a related column between them. It allows you to retrieve data from multiple tables in a single query, making it a powerful tool for working with relational databases.

## Key Points

1. **Combining Tables**: `JOIN` combines rows from two or more tables based on a common column.
2. **Types of JOINs**: The most common types of `JOIN` are:
   * `INNER JOIN`: Returns only matching rows.
   * `LEFT JOIN` (or `LEFT OUTER JOIN`): Returns all rows from the left table and matching rows from the right table.
   * `RIGHT JOIN` (or `RIGHT OUTER JOIN`): Returns all rows from the right table and matching rows from the left table.
   * `FULL JOIN` (or `FULL OUTER JOIN`): Returns all rows when there is a match in either table.
   * `CROSS JOIN`: Returns the Cartesian product of the two tables (all possible combinations).
3. **Alias Support**: You can use table aliases to simplify queries.

## Syntax

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

* `column1, column2, ...`: The columns you want to retrieve.
* `table1, table2`: The tables you want to join.
* `common_column`: The column that relates the two tables.

## Examples

### INNER JOIN

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     | 105          |

Table: Departments

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

To retrieve employee names along with their department names:

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

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

### LEFT JOIN

A `LEFT JOIN` returns all rows from the left table (`Employees`) and matching rows from the right table (`Departments`). If there is no match, `NULL` values are returned for columns from the right table.

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

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

If there were employees without a department, their `DepartmentName` would appear as `NULL`.

### RIGHT JOIN

A `RIGHT JOIN` returns all rows from the right table (`Departments`) and matching rows from the left table (`Employees`). If there is no match, `NULL` values are returned for columns from the left table.

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

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

If there were departments without employees, the `Name` column would appear as `NULL`.

### FULL JOIN

A `FULL JOIN` returns all rows when there is a match in either table. If there is no match, `NULL` values are returned for columns from the table without a match.

**Query:**

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

**Result:**

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

If there were employees without departments or departments without employees, the missing values would appear as `NULL`.

### CROSS JOIN

A `CROSS JOIN` returns the Cartesian product of the two tables, meaning it combines each row of the first table with each row of the second table.

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

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

## Practical Use Case

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

Table: Students

| StudentID | Name    | City       |
| --------- | ------- | ---------- |
| 1         | Ram     | Chennai    |
| 2         | Karthik | Coimbatore |
| 3         | David   | Bangalore  |

Table: Courses

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

To retrieve all possible combinations of students and courses:

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

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

## Key Takeaways

1. `JOIN` combines rows from two or more tables based on a related column.
2. Common types of `JOIN` include `INNER JOIN`, `LEFT JOIN`, `RIGHT JOIN`, `FULL JOIN`, and `CROSS JOIN`.
3. `INNER JOIN` returns only matching rows, while `LEFT JOIN` and `RIGHT JOIN` include non-matching rows with `NULL` values.
4. `CROSS JOIN` returns the Cartesian product of the two tables.
5. Using `JOIN` allows you to retrieve and analyze data from multiple tables in a single query.