MySQL LEFT JOIN

MySQL LEFT JOIN

The LEFT JOIN clause in MySQL retrieves all rows from the left table and the matching rows from the right table. If no match is found, NULL is returned for columns from the right table.

Syntax

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

• table1: The left table.
• table2: The right table.
• ON: Specifies the condition to match rows between the two tables.

Example Tables

Table: employees

+----+--------+---------------+
| id | name   | department_id |
+----+--------+---------------+
| 1  | Alice  | 1             |
| 2  | Bob    | 2             |
| 3  | Carol  | NULL          |
| 4  | Dave   | 4             |
+----+--------+---------------+

Table: departments

+----+-----------+
| id | name      |
+----+-----------+
| 1  | HR        |
| 2  | IT        |
| 3  | Finance   |
+----+-----------+

1. Basic LEFT JOIN

Retrieve all employees and their department names, including employees without a department:

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

Result

+----------------+------------------+
| employee_name  | department_name  |
+----------------+------------------+
| Alice          | HR               |
| Bob            | IT               |
| Carol          | NULL             |
| Dave           | NULL             |
+----------------+------------------+

• Explanation: Employees "Carol" and "Dave" do not have a matching department in the departments table, so their department_name is NULL.

2. LEFT JOIN with Additional Conditions

Retrieve only employees with no department assigned:

SELECT e.name AS employee_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;

Result

+----------------+
| employee_name  |
+----------------+
| Carol          |
| Dave           |
+----------------+

3. LEFT JOIN Across Multiple Tables

Consider another table, projects:

Table: projects

+----+------------+-------------+
| id | name       | employee_id |
+----+------------+-------------+
| 1  | Project A  | 1           |
| 2  | Project B  | 2           |
| 3  | Project C  | 3           |
+----+------------+-------------+

Retrieve employees, their department names, and projects, including those not assigned to any project:

SELECT e.name AS employee_name, d.name AS department_name, p.name AS project_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
LEFT JOIN projects p ON e.id = p.employee_id;

Result

+----------------+------------------+----------------+
| employee_name  | department_name  | project_name   |
+----------------+------------------+----------------+
| Alice          | HR               | Project A      |
| Bob            | IT               | Project B      |
| Carol          | NULL             | Project C      |
| Dave           | NULL             | NULL           |
+----------------+------------------+----------------+

4. Aggregated LEFT JOIN

Count the number of employees in each department, including departments with no employees:

SELECT d.name AS department_name, COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.name;

Result

+------------------+----------------+
| department_name  | employee_count|
+------------------+----------------+
| HR               | 1             |
| IT               | 1             |
| Finance          | 0             |
+------------------+----------------+

5. LEFT JOIN with Aliases

Using aliases can improve query readability:

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

This produces the same result as the basic example but is easier to read and maintain.

6. LEFT JOIN with Self-Referencing Tables

A self-join is used when you want to reference the same table multiple times. For example, finding employees and their managers (assuming a manager_id column in the employees table):

SELECT e.name AS employee_name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Best Practices for LEFT JOIN

1. Filter Early:

   • Use conditions in the ON clause to reduce the number of rows processed.

2. Avoid Excessive NULLs:

   • If NULL values are not useful, apply a WHERE filter to exclude them.

3. Index Key Columns:

   • Index the columns used in the ON condition for better performance.

4. Test with Sample Data:

   • Test your query with edge cases to ensure it behaves as expected, especially when dealing with NULL values.

Let me know if you'd like additional examples or assistance with LEFT JOIN queries!