SQL JOINs Explained

Example Tables

We will use two sample tables for our JOIN examples:

Students Table

IDName
1Alice
2Bob
3Charlie

Courses Table

CourseIDStudent_IDCourse
1011Math
1022Science
1034History

1. INNER JOIN

Definition: Returns only the rows that have matching values in both tables.

ABB 
SELECT students.name, courses.course
FROM students
INNER JOIN courses
ON students.id = courses.student_id;

Result:

NameCourse
AliceMath
BobScience

2. LEFT JOIN

Returns all rows from the left table and matched rows from the right table. NULL if no match.

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

Result:

NameCourse
AliceMath
BobScience
CharlieNULL

3. RIGHT JOIN

Returns all rows from the right table and matched rows from the left table. NULL if no match.

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

Result:

NameCourse
AliceMath
BobScience
NULLHistory

4. FULL OUTER JOIN

SELECT students.name, courses.course
FROM students
FULL OUTER JOIN courses
ON students.id = courses.student_id;

Result:

NameCourse
AliceMath
BobScience
CharlieNULL
NULLHistory

5. CROSS JOIN

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

Result: 3 students × 3 courses = 9 rows