Join Queries

Status

This note is complete, reviewed, and considered stable.

In relational databases, data is often spread across multiple tables to maintain normalization and reduce redundancy. When we need to retrieve data that spans multiple tables, SQL provides the JOIN operation, which combines rows from two or more tables based on a related column between them.

The most common types of joins are:

• INNER JOIN
• LEFT JOIN (LEFT OUTER JOIN)
• RIGHT JOIN (RIGHT OUTER JOIN)
• FULL OUTER JOIN
• CROSS JOIN
• LATERAL JOIN

Example Setup

Let's use the following two tables throughout our examples:

Table: students

student_id	name	course_id
1	Alice	101
2	Bob	102
3	Charlie	NULL

Table: courses

course_id	course_name
101	Math
102	Science
103	History

INNER JOIN

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

Query:

SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c ON s.course_id = c.course_id;

Output:

name	course_name
Alice	Math
Bob	Science

Explanation: Only Alice and Bob have matching course IDs in both tables. Charlie gets excluded because his course_id is NULL.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table and the matched rows from the right table. If there’s no match, the right side will contain NULL.

Query:

SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c ON s.course_id = c.course_id;

Output:

name	course_name
Alice	Math
Bob	Science
Charlie	NULL

Explanation: All students are returned. For Charlie, there's no course match, so course_name shows up as NULL.

RIGHT JOIN (RIGHT OUTER JOIN)

Returns all rows from the right table and the matched rows from the left table. If there’s no match, the left side will contain NULL.

Query:

SELECT s.name, c.course_name
FROM students s
RIGHT JOIN courses c ON s.course_id = c.course_id;

Output:

name	course_name
Alice	Math
Bob	Science
NULL	History

Explanation: All courses are returned. The "History" course has no enrolled student, so name shows up as NULL.

FULL OUTER JOIN

Returns all rows from both tables. If there’s no match, unmatched columns will contain NULL.

Query:

SELECT s.name, c.course_name
FROM students s
FULL OUTER JOIN courses c ON s.course_id = c.course_id;

Output:

name	course_name
Alice	Math
Bob	Science
Charlie	NULL
NULL	History

Explanation: This combines the results of both LEFT and RIGHT joins - we get everything from both sides.

Note: MySQL doesn't support FULL OUTER JOIN natively. To emulate it, we can use UNION:

SELECT s.name, c.course_name
FROM students s
LEFT JOIN courses c ON s.course_id = c.course_id
UNION
SELECT s.name, c.course_name
FROM students s
RIGHT JOIN courses c ON s.course_id = c.course_id;

CROSS JOIN

Produces a Cartesian product of both tables — every row from the first table is combined with every row from the second table.

Query:

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

Output:

name	course_name
Alice	Math
Alice	Science
Alice	History
Bob	Math
Bob	Science
Bob	History
Charlie	Math
Charlie	Science
Charlie	History

Explanation: Every possible student-course combination is returned - this can get huge fast!

LATERAL JOIN (PostgreSQL & SQL Server)

A LATERAL JOIN allows a subquery in the FROM clause to reference columns from preceding tables in the same FROM clause. It's useful when we want to run a subquery for each row of another table.

Example Scenario: Let's say we have a table marks that stores multiple marks per student.

Table: marks

student_id	mark
1	85
1	90
2	78

We want to select each student along with their highest mark.

Query (PostgreSQL):

SELECT s.name, m.highest_mark
FROM students s,
LATERAL (
    SELECT MAX(mark) AS highest_mark
    FROM marks
    WHERE marks.student_id = s.student_id
) m;

Output:

name	highest_mark
Alice	90
Bob	78
Charlie	NULL

Explanation: For each student, the lateral subquery runs individually and finds their maximum mark. This is different from a regular join - it's evaluated row by row.

Note:

• MySQL 8.0+ supports this using the keyword JOIN LATERAL or CROSS JOIN LATERAL.
• SQL Server supports similar behavior with APPLY (CROSS APPLY, OUTER APPLY).

Summary of Join Differences Across Databases

Join Type	MySQL	PostgreSQL	SQL Server	Oracle
INNER JOIN	✓ Supported	✓ Supported	✓ Supported	✓ Supported
LEFT JOIN	✓	✓	✓	✓
RIGHT JOIN	✓	✓	✓	✓
FULL OUTER JOIN	✗ (use UNION)	✓	✓	✓
CROSS JOIN	✓	✓	✓	✓
LATERAL JOIN	✓ (8.0+)	✓	✓ (as APPLY)	✓ (as CROSS APPLY)

Key Takeaways

• INNER JOIN → Returns only matching rows.
• LEFT JOIN → Keeps all left rows, fills missing right with NULL.
• RIGHT JOIN → Keeps all right rows, fills missing left with NULL.
• FULL OUTER JOIN → Keeps all rows from both sides.
• CROSS JOIN → Cartesian product of both tables.
• LATERAL JOIN → Enables correlated subqueries in FROM.