SQL LEFT JOIN Tutorial

Example Tables

We will use the following tables named employees and departments for our examples:

    CREATE TABLE employees (
        employee_id INT PRIMARY KEY,
        employee_name VARCHAR(50),
        department_id INT
    );
    
    CREATE TABLE departments (
        department_id INT PRIMARY KEY,
        department_name VARCHAR(50)
    );
    
    INSERT INTO employees (employee_id, employee_name, department_id) VALUES
    (1, 'John Doe', 1),
    (2, 'Jane Smith', 2),
    (3, 'Emily Davis', NULL),
    (4, 'Michael Brown', 3);
    
    INSERT INTO departments (department_id, department_name) VALUES
    (1, 'HR'),
    (2, 'Finance'),
    (4, 'IT');
    

Employees Table

employee_id	employee_name	department_id
1	John Doe	1
2	Jane Smith	2
3	Emily Davis	NULL
4	Michael Brown	3

Departments Table

department_id	department_name
1	HR
2	Finance
4	IT

Using SQL LEFT JOIN

The SQL LEFT JOIN keyword returns all records from the left table (employees), and the matched records from the right table (departments). The result is NULL from the right side, if there is no match.

Example: Left Join

To perform a left join on the employees and departments tables, use the following query:

    SELECT employees.employee_name, departments.department_name
    FROM employees
    LEFT JOIN departments
    ON employees.department_id = departments.department_id;
    

Result:

employee_name	John Doe
department_name	HR
	Jane Smith
	Finance
	Emily Davis
	NULL
	Michael Brown
	NULL