SQL Joins – Comprehensive Guide

1. Introduction to SQL Joins

SQL Joins are used to combine rows from two or more tables based on a related column. They are essential for querying data stored across multiple tables in a relational database.

---

2. Types of SQL Joins

2.1 INNER JOIN

Retrieves records that have matching values in both tables.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example: Consider two tables:

• students:

id	name	course_id
1	John	101
2	Jane	102
3	Alice	NULL

• courses:

id	name
101	Math
102	Science
103	History

Query:

SELECT students.name, courses.name AS course
FROM students
INNER JOIN courses ON students.course_id = courses.id;

Result:

name	course
John	Math
Jane	Science

---

2.2 LEFT JOIN (OUTER JOIN)

Retrieves all records from the left table and matching records from the right table. Unmatched rows in the right table result in NULL.

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Example: Query:

SELECT students.name, courses.name AS course
FROM students
LEFT JOIN courses ON students.course_id = courses.id;

Result:

name	course
John	Math
Jane	Science
Alice	NULL

---

2.3 RIGHT JOIN (OUTER JOIN)

Retrieves all records from the right table and matching records from the left table. Unmatched rows in the left table result in NULL.

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Example: Query:

SELECT students.name, courses.name AS course
FROM students
RIGHT JOIN courses ON students.course_id = courses.id;

Result:

name	course
John	Math
Jane	Science
NULL	History

---

2.4 FULL JOIN (FULL OUTER JOIN)

Retrieves all records from both tables, with NULLs where there is no match.

Syntax:

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

Example: Query:

SELECT students.name, courses.name AS course
FROM students
FULL JOIN courses ON students.course_id = courses.id;

Result:

name	course
John	Math
Jane	Science
Alice	NULL
NULL	History

---

2.5 CROSS JOIN

Returns the Cartesian product of two tables. Every row in the first table is paired with every row in the second table.

Syntax:

SELECT columns
FROM table1
CROSS JOIN table2;

Example: Query:

SELECT students.name, courses.name AS course
FROM students
CROSS JOIN courses;

Result:

name	course
John	Math
John	Science
John	History
Jane	Math
Jane	Science
Jane	History
Alice	Math
Alice	Science
Alice	History

---

2.6 SELF JOIN

A table is joined with itself to compare rows within the same table.

Syntax:

SELECT a.column1, b.column2
FROM table a, table b
WHERE condition;

Example: Consider the employees table:

id	name	manager_id
1	John	NULL
2	Jane	1
3	Alice	1
4	Bob	2

Query:

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

Result:

employee	manager
John	NULL
Jane	John
Alice	John
Bob	Jane

---

3. Summary of SQL Joins

Join Type	Description
INNER JOIN	Matches records in both tables.
LEFT JOIN	All records from the left, matched from right.
RIGHT JOIN	All records from the right, matched from left.
FULL JOIN	All records from both tables.
CROSS JOIN	Cartesian product of both tables.
SELF JOIN	Join within the same table.