MySQL LEFT JOIN

A LEFT JOIN in MySQL is a type of join operation that combines rows from two or more tables based on a specified condition and includes unmatched rows from the left table. This means that even if there is no match in the right table, all the rows from the left table will still be included in the result set, with NULL values in the columns from the right table where there is no match.

Syntax

The basic syntax of a LEFT JOIN in MySQL is as follows:

SELECT *
FROM left_table
LEFT JOIN right_table 
ON left_table.column = right_table.column;

In this syntax:

left_table is the table from which you want to retrieve all rows.
right_table is the table that you want to match with the left table based on a specified condition.
ON left_table.column = right_table.column is the condition that defines how the two tables are related.

Example

Here’s a simple example to illustrate the concept. Consider two tables, employees and departments:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255),
    department_id INT
);

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

INSERT INTO employees VALUES (1, 'John Doe', 1);
INSERT INTO employees VALUES (2, 'Jane Smith', 2);
INSERT INTO employees VALUES (3, 'Bob Johnson', NULL);

INSERT INTO departments VALUES (1, 'HR');
INSERT INTO departments VALUES (2, 'IT');

Now, let’s use a LEFT JOIN to retrieve a list of all employees and their respective department names:

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

The result would look like this:

+-------------+---------------+------------------+
| employee_id | employee_name | department_name |
+-------------+---------------+------------------+
|           1 | John Doe      | HR               |
|           2 | Jane Smith    | IT               |
|           3 | Bob Johnson   | NULL             |
+-------------+---------------+------------------+

In this example, all rows from the employees table are included in the result set, and matching rows from the departments table are included based on the specified condition. If there is no match, the columns from the departments table will contain NULL values. The LEFT JOIN ensures that all rows from the left table are included, regardless of whether there is a match in the right table.