INNER, LEFT, RIGHT JOIN

JOINs combine rows from related tables.

Types

• INNER JOIN � matching rows only
• LEFT JOIN � all left rows + matches
• RIGHT JOIN � all right rows + matches

ON Clause

Specifies the relationship between tables.

Syntax

MYSQL

1
2
3
4
5
6
7

SELECT u.name, o.total FROM users u
INNER JOIN orders o ON u.id = o.user_id;

SELECT u.name, COALESCE(SUM(o.total), 0) AS spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

SQL JOINs

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- INNER JOIN (only matches)
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- LEFT JOIN (all users, even without orders)
SELECT u.name, COALESCE(o.amount, 0) AS amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- RIGHT JOIN
SELECT o.id, u.name
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

-- JOIN with 3 tables
SELECT u.name, p.title
FROM users u
JOIN enrollments e ON e.user_id = u.id
JOIN courses p ON p.id = e.course_id;

Try It Yourself

Practice

1

Exercise

Find users who never ordered.

Answer

SELECT u.name FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

Quick Quiz

1

Which JOIN returns all rows from both tables?

INNER

LEFT

RIGHT

FULL OUTER

FULL OUTER JOIN returns all rows from both tables.

Retry Quiz

Interview Questions

LEFT vs INNER JOIN?

INNER returns only matching rows. LEFT returns all left rows, with NULLs for non-matching right rows.

Next Lesson

Self Join & UNION