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

The teacher needs a class report that includes students whose exam result has not been entered.

A LEFT JOIN preserves every row from its left source. Matching right rows are attached; missing right matches produce NULL values for the right-side fields.

The ON condition decides whether a right row matches. For a left student with no match, the joined result still contains that student, with missing right fields represented by NULL. A later WHERE condition can remove this row if it requires those right fields to contain particular non-null values.

Related concepts: INNER JOIN and Missing Values and NULL and Filter with WHERE.

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.

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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);

SQL · portable concepts; preserved examples identify their engine

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.

SQL · portable concepts; preserved examples identify their engine

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

SQL · portable concepts; preserved examples identify their engine

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

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.

• 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.

WHERE r.marks >= 80 removes unmatched rows because their mark comparison is unknown. To preserve students while restricting acceptable matches, place the intended right-side match restriction in ON. Several right matches can still create several output rows.

Include all students and their Maths results, including Diya’s missing result.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_results (roll_no INTEGER, subject VARCHAR(30), marks INTEGER);
INSERT INTO practice_results VALUES (1,'Maths',85),(3,'Maths',85),(4,'Maths',92);

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');
CREATE TABLE practice_results (roll_no INTEGER, subject VARCHAR(30), marks INTEGER);
INSERT INTO practice_results VALUES (1,'Maths',85),(3,'Maths',85),(4,'Maths',92);

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

SELECT s.name, r.marks FROM practice_students AS s LEFT JOIN practice_results AS r ON s.roll_no = r.roll_no AND r.subject = 'Maths' ORDER BY s.roll_no;

The subject restriction belongs to the match rule. Diya survives the join and her missing right-side mark is displayed as NULL.

A LEFT JOIN preserves every row from its left source. Matching right rows are attached; missing right matches produce NULL values for the right-side fields.

The subject restriction belongs to the match rule. Diya survives the join and her missing right-side mark is displayed as NULL.

Continue your understanding with INNER JOIN and Missing Values and NULL and Filter with WHERE.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.