MySQL : LEFT JOIN Keyword

In MySQL, the LEFT JOIN keyword is used to retrieve all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match.

Here's the basic syntax of the LEFT JOIN:


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

  • columns: The columns you want to retrieve from the tables.
  • table1: The left table.
  • table2: The right table.
  • column_name: The column(s) that exist in both tables and are used to join them.

Example:

Let's say we have two tables: employees and departments. The employees table contains information about employees, including their department ID, and the departments table contains department names and their corresponding department IDs.

We want to retrieve the names of all employees along with their department names. If an employee does not belong to any department, we still want to include their information in the result set, with the department name as NULL.


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

In this query:

  • employees is the left table.
  • departments is the right table.
  • We specify the join condition using the ON keyword, which matches the department_id column from the employees table with the department_id column from the departments table.
  • The LEFT JOIN ensures that all rows from the employees table are included in the result set, regardless of whether there is a match in the departments table.
  • If an employee does not belong to any department (i.e., there is no matching record in the departments table), the department name will be NULL in the result set.

LEFT JOIN is useful when you want to include all records from the left table in the result set, even if there are no matches in the right table. It's commonly used in scenarios where you want to retrieve data from one table and optionally match it with related data from another table.