Demystifying Joins in SQL: Inner vs. Outer (Left, Right, Full)

Inner Join vs. Outer Join

• Inner Join: Returns only matching rows from both tables
• Outer Join: Returns all rows from one or both tables, including non-matches

Example Data

Table 1 (students)

id | name
1  | alice
2  | bob
3  | charlie

Table 2 (grades)

student_id | subject | grade
1          | math    | a
2          | math    | b
4          | science | c

INNER JOIN

SELECT students.name, grades.subject, grades.grade
FROM students
INNER JOIN grades ON students.id = grades.student_id;

This query returns only the rows where there's a match in both tables.

Result:

name    | subject | grade
--------|---------|------
alice   | math    | a
bob     | math    | b

Explanation: Only Alice and Bob have matching records in both tables.

OUTER JOIN

LEFT (OUTER) JOIN

SELECT students.name, grades.subject, grades.grade
FROM students
LEFT JOIN grades ON students.id = grades.student_id;

This query returns all rows from the left table (students) and matching rows from the right table (grades). If there's no match, NULL values are returned for the right table columns.

Result:

name    | subject | grade
--------|---------|------
alice   | math    | a
bob     | math    | b
charlie | NULL    | NULL

Explanation: Charlie is included even though there's no matching grade.

RIGHT (OUTER) JOIN

SELECT students.name, grades.subject, grades.grade
FROM students
RIGHT JOIN grades ON students.id = grades.student_id;

This query returns all rows from the right table (grades) and matching rows from the left table (students). If there's no match, NULL values are returned for the left table columns.

Result:

name    | subject | grade
--------|---------|------
alice   | math    | a
bob     | math    | b
NULL    | science | c

Explanation: The science grade for student_id 4 is included, even though there's no matching student.

FULL (OUTER) JOIN

SELECT students.name, grades.subject, grades.grade
FROM students
FULL JOIN grades ON students.id = grades.student_id;

This query returns all rows when there's a match in either table. If there's no match, NULL values are returned for the columns from the table without a match.

Result:

name    | subject | grade
--------|---------|------
alice   | math    | a
bob     | math    | b
charlie | NULL    | NULL
NULL    | science | c

Explanation: This includes all students and all grades, even when there's no match between the tables.

When to use

• inner join: when you only need matching data from both tables
• outer join: when you need all data from one table, even without matches