lesson1Title

lesson2Title

lesson3Title

lesson4Title

lesson5Title

lesson6Title

lesson7Title

lesson8Title

lesson9Title

lesson10Title

lesson11Title

sqlIntroAdvancedChapter1Title

sqlIntroAdvancedChapter2Title

lesson12Title

sqlIntroAdvancedChapter3Title

-- FULL OUTER JOIN example (not supported in SQLite directly)
-- This query returns all students and all enrollments.
-- Where matches exist, rows are combined.
-- Where no match exists, NULLs are used for missing values.

-- View the students table
SELECT * FROM students;

-- View the enrollments table
SELECT * FROM enrollments;

SELECT students.name, enrollments.class_name
FROM students
FULL OUTER JOIN enrollments
ON students.student_id = enrollments.student_id;

# FULL OUTER JOIN

A `FULL OUTER JOIN` returns **all rows** from both the left and right tables, combining them into a single result set.

Where there is a match, the result combines the rows. 

Where no match exists, the missing values are represented with `NULL`.

<br/>

## Syntax

Here is the syntax for a `FULL OUTER JOIN`:

```sql title="FULL OUTER JOIN Syntax"
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
```

> Conceptually, this join behaves like a combination of `LEFT JOIN` and `RIGHT JOIN`.

<br/>

## Example: Students and Enrollments

Below is an example of a `FULL OUTER JOIN` between the `students` and `enrollments` tables.

*students*

| student_id | name        |
| ----------- | ----------- |
| 1           | John Miller |
| 2           | Lisa Brown  |
| 3           | Michael Johnson |
| 4           | Emily Davis |
| 5           | Sarah Thompson |


*enrollments*

| student_id | class_name |
| ----------- | ----------- |
| 1           | Math        |
| 1           | Science     |
| 2           | History     |
| 3           | Art         |

```sql title="FULL OUTER JOIN example"
SELECT students.name, enrollments.class_name
FROM students
FULL OUTER JOIN enrollments
ON students.student_id = enrollments.student_id;
```

### Result:

| name        | class_name |
| ----------- | ----------- |
| John Miller | Math        |
| John Miller | Science     |
| Lisa Brown  | History     |
| Emily Davis | NULL        |
| NULL        | Art         |

>  The result set includes matched rows as well as unmatched students and classes.

<br/>

## When is `FULL OUTER JOIN` the right choice?

Use `FULL OUTER JOIN` when:

- You need a **complete dataset** from both tables  
- You're checking for **missing relationships**  
- You need to **audit or reconcile** records across tables  

> Keep in mind: SQLite and MySQL do *not* support `FULL OUTER JOIN` directly. You can emulate it using a `UNION` of a `LEFT JOIN` and a `RIGHT JOIN`.

A FULL OUTER JOIN in SQL returns all rows from both the left and right tables. When there is no match between the tables, it fills the missing data with NULLs. This join is useful for ensuring a complete dataset, capturing all possible relationships, and highlighting missing connections between datasets.

student_id	name
1	John Miller
2	Lisa Brown
3	Michael Johnson
4	Emily Davis
5	Sarah Thompson

name	class_name
John Miller	Math
John Miller	Science
Lisa Brown	History
Emily Davis	NULL
NULL	Art

FULL OUTER JOIN

Syntax

Example: Students and Enrollments

Result:

When is FULL OUTER JOIN the right choice?

What does a FULL OUTER JOIN return in a SQL query?

When is `FULL OUTER JOIN` the right choice?