Outer Joins, Cross Joins, and Self Joins in SQL

Continuing my journey through the DataCamp SQL courses, I’ve delved into the second chapter of “Joining Data in SQL,” which focuses on Outer Joins, Cross Joins, and Self Joins. Here’s an overview of what I’ve learned, complete with examples and explanations.

1. LEFT JOIN

A LEFT JOIN returns all rows from the left table and the matched rows from the right table. Unmatched rows from the right table will return NULL.

Example:
Let’s consider two tables, students and enrollments.

students

student_id	name
1	Alice
2	Bob
3	Carol

enrollments

enrollment_id	student_id	course_name
1	1	Math
2	2	Science

SELECT students.student_id, students.name, enrollments.course_name
FROM students
LEFT JOIN enrollments
ON students.student_id = enrollments.student_id;

Result:

student_id	name	course_name
1	Alice	Math
2	Bob	Science
3	Carol	NULL

2. RIGHT JOIN

A RIGHT JOIN returns all rows from the right table and the matched rows from the left table. Unmatched rows from the left table will return NULL.

Example:
Using the same tables as above:

SELECT students.student_id, students.name, enrollments.course_name
FROM students
RIGHT JOIN enrollments
ON students.student_id = enrollments.student_id;

Result:

student_id	name	course_name
1	Alice	Math
2	Bob	Science
NULL	NULL	NULL

3. LEFT JOIN vs RIGHT JOIN

The difference between LEFT JOIN and RIGHT JOIN lies in which table’s rows are all returned. LEFT JOIN returns all rows from the left table, while RIGHT JOIN returns all rows from the right table.

4. FULL JOIN

A FULL JOIN returns all rows when there is a match in either left or right table. Unmatched rows will return NULL.

Example:

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

Result:

student_id	name	course_name
1	Alice	Math
2	Bob	Science
3	Carol	NULL
NULL	NULL	NULL

5. CROSS JOIN

A CROSS JOIN returns the Cartesian product of the two tables, meaning every row from the first table is combined with every row from the second table.

Example:
With the same students table and a courses table:

courses

course_name
Math
Science

SELECT students.name, courses.course_name
FROM students
CROSS JOIN courses;

Result:

name	course_name
Alice	Math
Alice	Science
Bob	Math
Bob	Science
Carol	Math
Carol	Science

6. SELF JOIN

A SELF JOIN is a regular join but the table is joined with itself.

Example:
Consider a table employees:

employee_id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Carol	1

SELECT e1.name AS employee, e2.name AS manager
FROM employees AS e1
LEFT JOIN employees AS e2
ON e1.manager_id = e2.employee_id;

Result:

employee	manager
Alice	NULL
Bob	Alice
Carol	Alice

Summary Table

Concept	Description	Example
LEFT JOIN	All rows from the left table and matched rows from the right	`LEFT JOIN enrollments ON students.student_id = enrollments.student_id`
RIGHT JOIN	All rows from the right table and matched rows from the left	`RIGHT JOIN enrollments ON students.student_id = enrollments.student_id`
FULL JOIN	All rows when there is a match in either table	`FULL JOIN enrollments ON students.student_id = enrollments.student_id`
CROSS JOIN	Cartesian product of the two tables	`CROSS JOIN courses`
SELF JOIN	Join a table with itself	`LEFT JOIN employees AS e2 ON e1.manager_id = e2.employee_id`

Understanding Outer Joins, Cross Joins, and Self Joins in SQL has significantly expanded my understanding of how to manipulate and analyze data from multiple tables effectively. Each type of join offers unique advantages for different scenarios, making them invaluable tools in SQL.