Mastering SQL Joins - Understanding LEFT JOIN

Introduction

LEFT JOIN is like looking at your main list (left table) and trying to find matching information from another list (right table). If there’s no match, you still keep the item from your main list, just with empty spaces for the missing information.

Why LEFT JOIN Matters?

Imagine these everyday scenarios:

• Finding which students haven’t submitted their assignments
• Checking which products haven’t been sold
• Seeing which customers haven’t made any purchases

Let’s learn through simple examples.

Main Content: Understanding LEFT JOIN

Let’s use these simple tables:

-- Create two simple tables
CREATE TABLE students (
    student_id INT,
    student_name VARCHAR(50)
);

CREATE TABLE assignments (
    assignment_id INT,
    student_id INT,
    subject VARCHAR(50)
);

-- Insert sample data
INSERT INTO students VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'Diana');

INSERT INTO assignments VALUES
(1, 1, 'Math'),
(2, 1, 'Science'),
(3, 2, 'Math'),
(4, 3, 'Science');

Example 1: Basic LEFT JOIN

Let’s find all students and their assignments:

SELECT 
    s.student_name,
    a.subject
FROM students s
LEFT JOIN assignments a 
    ON s.student_id = a.student_id;

This simple query:

1. Takes all students from the students table
2. Matches assignments where possible
3. Shows NULL for students with no assignments

Results:

student_name | subject
-------------|----------
Alice        | Math
Alice        | Science
Bob          | Math
Charlie      | Science
Diana        | NULL

Example 2: Finding Missing Records

Let’s find students who haven’t submitted any assignments:

SELECT 
    s.student_name,
    'No Assignment' as status
FROM students s
LEFT JOIN assignments a 
    ON s.student_id = a.student_id
WHERE a.assignment_id IS NULL;

This query:

1. Joins students with assignments
2. Uses WHERE to find unmatched records
3. Shows only students with no assignments

Results:

student_name | status
-------------|---------------
Diana        | No Assignment

Example 3: Simple Counting with LEFT JOIN

Let’s count assignments per student, including those with zero:

SELECT 
    s.student_name,
    COUNT(a.assignment_id) as assignment_count
FROM students s
LEFT JOIN assignments a 
    ON s.student_id = a.student_id
GROUP BY s.student_name;

This query:

1. Counts assignments for each student
2. Keeps all students in the results
3. Shows 0 for students with no assignments

Results:

student_name | assignment_count
-------------|------------------
Alice        | 2
Bob          | 1
Charlie      | 1
Diana        | 0

Best Practices

1. Table Order Matters

   • Put the main table (the one you want all records from) on the left
   • Example: FROM students LEFT JOIN assignments

2. Clear Join Conditions

   • Always use clear and correct join columns
   • Example: ON students.student_id = assignments.student_id

3. Table Aliases

   • Use meaningful short aliases
   • Example: ‘s’ for students, ‘a’ for assignments

4. NULL Handling

   • Remember that unmatched records will have NULL values
   • Use IS NULL to find missing records

5. Column Selection

   • Be specific about which columns you need
   • Use table aliases to clarify column sources

Remember:

• LEFT JOIN keeps all records from the left table
• Unmatched records show as NULL in the right table
• It’s perfect for finding missing or unmatched records

Happy querying! 🚀