SQL LEFT JOIN

A LEFT JOIN combines records from two tables while keeping all records from the left table.

SELECT *
FROM students
LEFT JOIN courses
ON students.course_id = courses.id;

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

The basic syntax is:

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

The first table is called the left table, and the second table is called the right table.

Suppose we have students and courses tables.

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

Every student is displayed, even if a matching course does not exist.

The ON clause defines how the two tables are related.

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

Here, students.course_id is matched with courses.id.

It is better to select only the required columns when working with larger tables.

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

Table aliases make LEFT JOIN queries shorter and easier to read.

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

Here, s represents the students table and c represents the courses table.

If there is no matching record in the right table, the right table columns contain NULL.

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

A student without a matching course will have NULL in course_name.

LEFT JOIN can be used to find records that do not have a matching record in another table.

SELECT
    s.student_name
FROM students AS s
LEFT JOIN courses AS c
ON s.course_id = c.id
WHERE c.id IS NULL;

This returns students who do not have a matching course.

A WHERE condition can be applied to the result of a LEFT JOIN.

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

This keeps all matching course information for active students.

The result of a LEFT JOIN can be sorted using ORDER BY.

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

Multiple conditions can be placed in the ON clause.

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

The LEFT JOIN still keeps every student, while only active courses are matched.

LEFT JOIN can combine more than two tables.

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

All students are kept, even when course or payment records are missing.

Several related tables can be combined using LEFT JOIN.

SELECT
    s.student_name,
    c.course_name,
    p.amount,
    a.attendance_date
FROM students AS s
LEFT JOIN courses AS c
ON s.course_id = c.id
LEFT JOIN payments AS p
ON s.id = p.student_id
LEFT JOIN attendance AS a
ON s.id = a.student_id;

The students table remains the main table in this query.

LEFT JOIN is useful when counting related records while keeping categories with zero records.

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

Courses without students will still appear with a count of zero.

GROUP BY can be used with LEFT JOIN to create reports.

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

Every course is displayed, including courses with no students.

HAVING can filter grouped LEFT JOIN results.

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

This finds courses that currently have no students.

LEFT JOIN can show every student along with payment information when available.

SELECT
    s.student_name,
    p.amount,
    p.payment_date
FROM students AS s
LEFT JOIN payments AS p
ON s.id = p.student_id;

Students without payment records are still displayed.

We can find students who have no payment record by checking for NULL.

SELECT
    s.student_name
FROM students AS s
LEFT JOIN payments AS p
ON s.id = p.student_id
WHERE p.id IS NULL;

This is a common use of LEFT JOIN.

CASE can be used to display a meaningful message when a matching record is missing.

SELECT
    s.student_name,
    CASE
        WHEN c.id IS NULL THEN 'Course Not Assigned'
        ELSE c.course_name
    END AS course_status
FROM students AS s
LEFT JOIN courses AS c
ON s.course_id = c.id;

COALESCE() can replace NULL values with a default value.

SELECT
    s.student_name,
    COALESCE(c.course_name, 'Not Assigned') AS course
FROM students AS s
LEFT JOIN courses AS c
ON s.course_id = c.id;

If no course exists, Not Assigned is displayed instead of NULL.

Date conditions can be placed in the ON clause.

SELECT
    s.student_name,
    p.amount,
    p.payment_date
FROM students AS s
LEFT JOIN payments AS p
ON s.id = p.student_id
AND p.payment_date >= '2026-01-01';

Every student remains in the result, while only payments from the specified date are matched.

The IN operator can be used in the ON clause.

SELECT
    s.student_name,
    c.course_name
FROM students AS s
LEFT JOIN courses AS c
ON s.course_id = c.id
AND c.course_name IN ('Python', 'Java', 'PHP');

Every student remains in the result, but only the specified courses are matched.

Calculations can be performed using values from the joined table.

SELECT
    s.student_name,
    c.fee,
    COALESCE(s.paid_fee, 0) AS paid_fee,
    c.fee - COALESCE(s.paid_fee, 0) AS pending_fee
FROM students AS s
LEFT JOIN courses AS c
ON s.course_id = c.id;

This calculates the pending fee when course information is available.

LEFT JOIN is commonly used with primary key and foreign key relationships.

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

The foreign key in the students table is matched with the primary key in the courses table.

LEFT JOIN: Returns all records from the left table and matching records from the right table.

INNER JOIN: Returns only records that have matching values in both tables.

-- LEFT JOIN
SELECT *
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id;

Use LEFT JOIN when records from the left table must not be lost.

LEFT JOIN keeps all rows from the left table, while RIGHT JOIN keeps all rows from the right table.

-- LEFT JOIN
SELECT *
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id;

Many RIGHT JOIN queries can be rewritten as LEFT JOIN by changing the order of the tables.

A common mistake is putting a condition on the right table in the WHERE clause when you want to preserve unmatched rows.

-- Can remove unmatched rows
SELECT
    s.student_name,
    c.course_name
FROM students s
LEFT JOIN courses c
ON s.course_id = c.id
WHERE c.status = 'Active';

If you want to keep all students while matching only active courses, put the condition in the ON clause.

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

LEFT JOIN can be chained to create detailed reports.

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

All students remain in the report even when course or payment information is missing.

A course report can show every course and the number of students enrolled.

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

Courses with no students are also included in the result.

The following example creates a complete student report using students, courses, and payments.

SELECT
    s.student_id,
    s.student_name,
    s.mobile,
    COALESCE(c.course_name, 'Not Assigned') AS course,
    COALESCE(c.fee, 0) AS course_fee,
    COALESCE(p.amount, 0) AS paid_amount,
    CASE
        WHEN c.id IS NULL THEN 'Course Not Assigned'
        WHEN p.id IS NULL THEN 'Payment Pending'
        ELSE 'Payment Available'
    END AS status
FROM students AS s
LEFT JOIN courses AS c
ON s.course_id = c.id
LEFT JOIN payments AS p
ON s.id = p.student_id
ORDER BY s.student_name;

This query keeps every student and displays available course and payment information. Missing information is handled using COALESCE() and CASE.