SQL LEFT JOIN
beginnerjoins
2 min read
What is LEFT JOIN?
LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. If there is no match, the right-side columns contain NULL.
Syntax
SELECT columns
FROM left_table
LEFT JOIN right_table ON left_table.col = right_table.col;When to Use
- Listing all students, even those not enrolled in any course
- Finding records in one table that have no match in another
- Reports where you need every row from the primary table
Key Points
- All Left Rows — Every row from the left table appears in the result, regardless of matches.
- NULLs for No Match — When there is no matching row in the right table, all right-side columns are NULL.
- Finding Orphans —
LEFT JOIN ... WHERE right.id IS NULLfinds left-table rows with no match. - LEFT OUTER JOIN — Identical to LEFT JOIN. The keyword OUTER is optional.
- Order Matters — The table listed first (left) keeps all its rows.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL LEFT JOIN Challenge
Write a query that solve this task: list every student and their enrollments, including students with no enrollments.
Expected result
All students appear. Students without enrollments show NULL for course_id.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is LEFT JOIN?
Initializing database...Each run starts from fresh sample data.
More Examples
Find students without enrollments
Identify students who have not enrolled in any course.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What is the difference between LEFT JOIN and INNER JOIN?
INNER JOIN only returns rows with matches in both tables. LEFT JOIN returns all rows from the left table, filling in NULLs where there is no match on the right.
How do I find rows with no match using LEFT JOIN?
Add WHERE right_table.primary_key IS NULL to your query. This returns only left-table rows that have no corresponding right-table row.