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:

</>
Copy
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:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a students table:

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

4. Create a grades table:

</>
Copy
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:

</>
Copy
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');