2. LEFT JOIN

LEFT (OUTER) JOIN: Returns all records from the left table and the matched records from the right table. The result is NULL from the right side if there is no match.

Syntax:

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

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

Example Table Creation and Joins

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY AUTO_INCREMENT,
    department_name VARCHAR(50)
);

INSERT INTO employees (first_name, last_name, department_id)
VALUES 
('John', 'Doe', 1),
('Jane', 'Smith', 2),
('Alice', 'Johnson', 1),
('Bob', 'Brown', 3);

INSERT INTO departments (department_name)
VALUES 
('Engineering'),
('Marketing'),
('HR');

Performing Joins

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;