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 powerful 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 in the result equals `rows_in_students × rows_in_days`. It grows fast!

Use this 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**, which is useful for comparing or relating rows within the same table.

### Example: Employees and Managers

Here is an example of a `SELF JOIN` between the `employees` table and itself.

*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 alias the same table as `employees` and `managers` to distinguish their roles.

<br/>

## Why These Joins Matter

Use these joins when:

* Building *schedules or matchups* (`CROSS JOIN`)
* Modeling *hierarchical relationships* (`SELF JOIN`)
* Comparing *rows within a table*

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.