lesson1Title

lesson2Title

lesson3Title

lesson4Title

lesson5Title

lesson6Title

lesson7Title

lesson8Title

lesson9Title

lesson10Title

lesson11Title

sqlIntroAdvancedChapter1Title

sqlIntroAdvancedChapter2Title

lesson12Title

sqlIntroAdvancedChapter3Title

-- View the students table
SELECT * FROM students;

-- View the days table
SELECT * FROM days;

-- View the employees table
SELECT * FROM employees;

-- CROSS JOIN example: generate all student-day pairs
SELECT students.name, days.day
FROM students
CROSS JOIN days;

-- SELF JOIN example: find each employee’s manager
SELECT employees.name AS employee, managers.name AS manager
FROM employees
LEFT JOIN employees AS managers
ON employees.manager_id = managers.id;

# CROSS JOIN & SELF JOIN

SQL includes some advanced join types that are useful in specific scenarios:

- *CROSS JOIN*: returns every possible pair of rows between two tables  
- *SELF JOIN*: joins a table to itself to compare rows within it

<br/>

## CROSS JOIN

A `CROSS JOIN` returns the **Cartesian product** of two tables, where every row in the first table is combined with every row in the second.

```sql title="CROSS JOIN"
SELECT students.name, days.day
FROM students
CROSS JOIN days;
```

> Be cautious: the number of rows equals `rows_in_students × rows_in_days`, which can grow very quickly.

Use a `CROSS JOIN` when:

* Generating *all possible pairs* (e.g., schedules, combinations)
* Running *pairwise comparisons*
* Creating *grids or test cases*

<br/>

## SELF JOIN

A `SELF JOIN` occurs when a table is joined with **itself**. This is useful for comparing or relating rows within the same table.

### Example: Employees and Managers

Here is an example of a `SELF JOIN` on the employees table.

*employees*

| id | name  | manager_id |
| -- | ----- | ----------- |
| 1  | Alice | NULL        |
| 2  | Bob   | 1           |
| 3  | Carol | 1           |
| 4  | Dave  | 2           |

To find each employee's manager, use a `SELF JOIN`:

```sql title="SELF JOIN to find managers"
SELECT employees.name AS employee, managers.name AS manager
FROM employees
LEFT JOIN employees AS managers
ON employees.manager_id = managers.id;
```

### Result:

| employee | manager |
| -------- | ------- |
| Alice    | NULL    |
| Bob      | Alice   |
| Carol    | Alice   |
| Dave     | Bob     |

> We use aliases (`employees` and `managers`) to distinguish the two roles of the same table.

<br/>

## Why use `CROSS JOIN` or `SELF JOIN`?

These joins are valuable in specialized cases:

- *CROSS JOIN* → generating all possible matchups, building schedules, or creating grids/test cases  
- *SELF JOIN* → modeling hierarchical relationships, comparing rows within the same table, or linking employees to managers

A CROSS JOIN in SQL does indeed return the Cartesian product of two tables, meaning it pairs every row from the first table with every row from the second table. This method is useful for generating all possible combinations, such as schedules or pairwise comparisons, but be cautious as the number of results can grow rapidly with large tables.

### In SQL, a CROSS JOIN returns the Cartesian product of two tables, resulting in all possible pairs of rows between them.

CROSS JOIN & SELF JOIN

CROSS JOIN

SELF JOIN

Example: Employees and Managers

Result:

Why use CROSS JOIN or SELF JOIN?

In SQL, a CROSS JOIN returns the Cartesian product of two tables, resulting in all possible pairs of rows between them.

Why use `CROSS JOIN` or `SELF JOIN`?