MySQL LEFT JOIN

A LEFT JOIN returns every row from the left table and matching rows from the right table.

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

All students are displayed. If a student does not have a matching course, the course columns contain NULL.

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

Here, table1 is the left table and table2 is the right table.

The order of the tables is important because LEFT JOIN keeps all rows from the left table.

Suppose we have a students table:

And a courses table:

Rahul has course_id 999, but no matching course exists.

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

Result:

Rahul is still displayed because students is the left table.

In this query:

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

students is the left table because it appears after FROM.

courses is the right table because it appears after LEFT JOIN.

Aliases make LEFT JOIN queries shorter.

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

Here, s represents students and c represents courses.

You can select columns from both tables.

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

This displays all students along with their course information when available.

If a row in the left table has no matching row in the right table, the right-table columns contain NULL.

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

This makes LEFT JOIN useful for finding records that do not have related data.

You can use LEFT JOIN with IS NULL to find students without a matching course.

SELECT
    s.student_id,
    s.name
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id
WHERE c.id IS NULL;

This returns students whose course_id does not match a course record.

WHERE can be used to filter the final result.

SELECT
    s.name,
    c.course_name
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id
WHERE s.status = 'Active';

This keeps all matching course information while filtering students to active students.

ORDER BY can sort a LEFT JOIN result.

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

The result is sorted alphabetically by student name.

LIMIT restricts the number of rows returned.

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

This returns a maximum of 10 rows.

LEFT JOIN can be used with more than two tables.

SELECT
    s.name,
    c.course_name,
    p.amount
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id
LEFT JOIN payments p
ON s.student_id = p.student_id;

This keeps every student while showing course and payment information when matching records exist.

LEFT JOIN is useful for counting related records while keeping rows that have zero matches.

SELECT
    c.course_name,
    COUNT(s.student_id) AS total_students
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
GROUP BY c.id, c.course_name;

Because COUNT(s.student_id) ignores NULL values, courses with no students can show a count of 0.

You can calculate payment totals using LEFT JOIN.

SELECT
    s.student_id,
    s.name,
    COALESCE(SUM(p.amount), 0) AS total_paid
FROM students s
LEFT JOIN payments p
ON s.student_id = p.student_id
GROUP BY s.student_id, s.name;

COALESCE() changes NULL totals to 0.

GROUP BY can be used to create summaries while keeping categories that have no matching records.

SELECT
    c.course_name,
    COUNT(s.student_id) AS total_students
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
GROUP BY c.id, c.course_name;

A course with no students can still appear with a count of 0.

HAVING can filter grouped LEFT JOIN results.

SELECT
    c.course_name,
    COUNT(s.student_id) AS total_students
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
GROUP BY c.id, c.course_name
HAVING COUNT(s.student_id) > 0;

This displays only courses having at least one matching student.

The ON clause can contain additional conditions.

SELECT
    c.course_name,
    s.name
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
AND s.status = 'Active';

This keeps every course but matches only active students to each course.

Consider this query:

SELECT
    c.course_name,
    s.name
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
AND s.status = 'Active';

All courses remain in the result. Only active students are matched.

But with:

SELECT
    c.course_name,
    s.name
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
WHERE s.status = 'Active';

courses with no matching active student are removed because the WHERE condition rejects NULL.

LEFT JOIN can find students who have not made a payment.

SELECT
    s.student_id,
    s.name
FROM students s
LEFT JOIN payments p
ON s.student_id = p.student_id
WHERE p.payment_id IS NULL;

This returns students without a matching payment record.

Suppose an assignments table stores assignments submitted by students.

SELECT
    s.student_id,
    s.name,
    a.assignment_id
FROM students s
LEFT JOIN assignments a
ON s.student_id = a.student_id
WHERE a.assignment_id IS NULL;

This can be used to find students without a matching assignment record.

SELECT
    c.course_name,
    COUNT(s.student_id) AS total_students
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
GROUP BY c.id, c.course_name
ORDER BY total_students DESC;

This report includes courses even when no student is enrolled in them.

You can calculate values from joined tables.

SELECT
    s.name,
    c.course_name,
    c.fee,
    COALESCE(SUM(p.amount), 0) AS paid_amount,
    c.fee - COALESCE(SUM(p.amount), 0) AS due_amount
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id
LEFT JOIN payments p
ON s.student_id = p.student_id
GROUP BY
    s.student_id,
    s.name,
    c.id,
    c.course_name,
    c.fee;

This creates a student-wise fee report while keeping students without payment records.

DISTINCT can be used when you need unique combinations from a joined result.

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

DISTINCT removes duplicate result rows having the same selected values.

A common mistake is accidentally converting a LEFT JOIN into an INNER JOIN by filtering the right table in WHERE.

For example:

SELECT
    c.course_name,
    s.name
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
WHERE s.status = 'Active';

The WHERE condition removes rows where s.status is NULL.

If you want every course and only active students matched, use:

SELECT
    c.course_name,
    s.name
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
AND s.status = 'Active';

You can join records using dates or filter joined records by date.

SELECT
    s.name,
    p.amount,
    p.payment_date
FROM students s
LEFT JOIN payments p
ON s.student_id = p.student_id
AND p.payment_date >= '2026-01-01'
AND p.payment_date < '2026-02-01';

All students remain in the result, while only payments from January 2026 are matched.

A simplified logical processing order is:

FROM
LEFT JOIN
ON
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT

Understanding this order helps explain why a WHERE condition can remove NULL-extended rows produced by a LEFT JOIN.

CREATE TABLE courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(100),
    fee DECIMAL(10,2)
);

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    course_id INT,
    city VARCHAR(100),
    status VARCHAR(20)
);

CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    amount DECIMAL(10,2),
    payment_date DATE
);

INSERT INTO courses
(course_name, fee)
VALUES
('Python', 15000),
('Java', 18000),
('PHP', 12000),
('SQL', 10000);

INSERT INTO students
(name, course_id, city, status)
VALUES
('Amit Kumar', 1, 'Patna', 'Active'),
('Priya Singh', 2, 'Gaya', 'Active'),
('Rahul Sharma', 1, 'Patna', 'Active'),
('Neha Kumari', 3, 'Patna', 'Inactive');

INSERT INTO payments
(student_id, amount, payment_date)
VALUES
(1, 5000, '2026-01-10'),
(1, 5000, '2026-02-10'),
(2, 18000, '2026-03-15');

-- Show all courses and matching students
SELECT
    c.course_name,
    s.name
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id;

-- Show courses without students
SELECT
    c.course_name
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
WHERE s.student_id IS NULL;

-- Show all students and their courses
SELECT
    s.name,
    c.course_name
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id;

-- Show all students and their total payments
SELECT
    s.student_id,
    s.name,
    COALESCE(SUM(p.amount), 0) AS total_paid
FROM students s
LEFT JOIN payments p
ON s.student_id = p.student_id
GROUP BY s.student_id, s.name;

-- Course-wise student count
SELECT
    c.course_name,
    COUNT(s.student_id) AS total_students
FROM courses c
LEFT JOIN students s
ON c.id = s.course_id
GROUP BY c.id, c.course_name;

This example demonstrates how LEFT JOIN can preserve all courses or all students while displaying matching information whenever it exists.