SQL LEFT JOIN – Syntax and Examples

SQL LEFT JOIN

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

The LEFT JOIN is useful when you want to include all entries from one table and match them with data from another table if it exists.

In this tutorial, we will go through SQL LEFT JOIN, its syntax, and how to use this join in SQL statements, with the help of well detailed examples.

---

Syntax of SQL LEFT JOIN

The basic syntax of the SQL LEFT JOIN clause is:

SELECT table1.column1, table2.column2, ...
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Each part of this syntax has a specific purpose:

• SELECT: Specifies the columns to retrieve from each table.
• FROM: Specifies the first (left) table in the join operation.
• LEFT JOIN: Specifies the second (right) table, which will be joined with the first table.
• ON: Specifies the condition for the join, which is the common column shared between the two tables.

Step-by-Step Examples with MySQL

We’ll go through examples demonstrating the LEFT JOIN in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use sample students and grades tables with fields student_id, name, age, and subject.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a students table:

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT
);

4. Create a grades table:

CREATE TABLE grades (
    student_id INT,
    subject VARCHAR(50),
    grade CHAR(1),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

5. Insert sample data into the students and grades tables:

INSERT INTO students (name, age)
VALUES
('Alice', 14),
('Bob', 15),
('Charlie', 14),
('David', 16);

INSERT INTO grades (student_id, subject, grade)
VALUES
(1, 'Math', 'A'),
(2, 'Math', 'B'),
(3, 'Science', 'A');

---

Examples: Using LEFT JOIN in Queries

Now, let’s explore different scenarios of using SQL LEFT JOIN with these tables.

---

1. Retrieving All Students with Their Grades

To retrieve a list of all students and their grades, including students without grades:

SELECT students.name, grades.subject, grades.grade
FROM students
LEFT JOIN grades
ON students.student_id = grades.student_id;

This query returns all students and their grades. For students who don’t have a grade, NULL will be displayed for the subject and grade columns.

---

2. Filtering LEFT JOIN Results with WHERE Clause

To retrieve only students without grades, use a WHERE clause to filter for NULL values:

SELECT students.name, grades.subject, grades.grade
FROM students
LEFT JOIN grades
ON students.student_id = grades.student_id
WHERE grades.subject IS NULL;

This query returns only students who do not have any grades recorded in the grades table.

Reference: SQL WHERE Clause

---

3. LEFT JOIN with Additional Conditions

To find all students along with their grades in “Math” specifically:

SELECT students.name, grades.subject, grades.grade
FROM students
LEFT JOIN grades
ON students.student_id = grades.student_id
WHERE grades.subject = 'Math' OR grades.subject IS NULL;

This query returns all students, showing their “Math” grades if available. Students without any grades in “Math” will show NULL for subject and grade.

---

FAQs for SQL LEFT JOIN

1. What does the SQL LEFT JOIN do?

The LEFT JOIN returns all rows from the left table and matches from the right table. Rows in the left table without a match in the right table will have NULL values for columns from the right table.

2. How is LEFT JOIN different from INNER JOIN?

LEFT JOIN includes all rows from the left table even without a match in the right table, whereas INNER JOIN only includes rows with matches in both tables.

3. Can LEFT JOIN be used with multiple tables?

Yes, you can use LEFT JOIN to join multiple tables by specifying additional LEFT JOIN clauses and conditions.

4. Can LEFT JOIN have conditions in the WHERE clause?

Yes, you can use the WHERE clause with LEFT JOIN to filter the result set further, including conditions on columns from either table.

5. What happens if there is no match in LEFT JOIN?

If there is no match, LEFT JOIN returns NULL for columns from the right table for those rows from the left table without matches.