SQL LEFT JOIN - Include All Left Table Data, Matched or Not

Introduction

Let’s say your school database has a list of students and a separate table with their exam results. But not every student has appeared for the exam yet. If you want to list all students — whether they have marks or not — LEFT JOIN is what you need.

What is LEFT JOIN?

LEFT JOIN returns all records from the left (first) table, and the matched records from the right (second) table. If there’s no match, NULL values are returned for the right table’s columns.

Syntax of LEFT JOIN

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

1. Sample Tables – Students and Results

students

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50),
  class VARCHAR(10)
);

INSERT INTO students VALUES
(1, 'Aarav Sharma', '10A'),
(2, 'Diya Iyer', '9B'),
(3, 'Sneha Patil', '10A'),
(4, 'Mehul Agarwal', '8C'),
(5, 'Saira Bano', '9B');

results

CREATE TABLE results (
  roll_no INT,
  subject VARCHAR(30),
  marks INT
);

INSERT INTO results VALUES
(1, 'Maths', 85),
(2, 'Maths', 92),
(3, 'Maths', 78);

2. Using LEFT JOIN

Let’s list all students, including those without results:

SELECT s.name, s.class, r.subject, r.marks
FROM students s
LEFT JOIN results r
ON s.roll_no = r.roll_no;

name           | class | subject | marks
----------------+--------+---------+-------
Aarav Sharma   | 10A   | Maths   | 85
Diya Iyer      | 9B    | Maths   | 92
Sneha Patil    | 10A   | Maths   | 78
Mehul Agarwal  | 8C    | NULL    | NULL
Saira Bano     | 9B    | NULL    | NULL

Explanation: Mehul and Saira have no results yet, so subject and marks are shown as NULL.

3. Filtering LEFT JOIN Results

If you want only students who don’t have results:

SELECT s.name, s.class
FROM students s
LEFT JOIN results r ON s.roll_no = r.roll_no
WHERE r.roll_no IS NULL;

name           | class
----------------+-------
Mehul Agarwal  | 8C
Saira Bano     | 9B

4. LEFT JOIN with Multiple Subjects

Let’s add a second subject for student 1 and see how LEFT JOIN handles it:

INSERT INTO results VALUES (1, 'Science', 90);

SELECT s.name, r.subject, r.marks
FROM students s
LEFT JOIN results r ON s.roll_no = r.roll_no
ORDER BY s.name, r.subject;

name           | subject | marks
----------------+---------+-------
Aarav Sharma   | Maths   | 85
Aarav Sharma   | Science | 90
Diya Iyer      | Maths   | 92
Sneha Patil    | Maths   | 78
Mehul Agarwal  | NULL    | NULL
Saira Bano     | NULL    | NULL

Real-World Use Case

If you’re generating a report for the school principal showing all students — even those who missed exams — LEFT JOIN gives you a complete picture without losing anyone.

Best Practices

• Use LEFT JOIN when the left table has priority — like students over results.
• Always use IS NULL in WHERE if you want to find unmatched records.
• Be cautious with filters — filtering on the right table’s columns after the join may accidentally convert it into an INNER JOIN behavior.

Summary

The LEFT JOIN clause ensures no one is left behind — even if there’s no match in the right table. Whether you're analyzing student participation, attendance, or grades, LEFT JOIN helps paint the full picture by prioritizing your left-side data.

What’s Next?

Next, we’ll look at RIGHT JOIN — which flips the logic, keeping all rows from the right table instead.