LEFT JOIN in MySQL

The LEFT JOIN (also called LEFT OUTER JOIN) in MySQL is used to return all records from the left table and matching records from the right table. If there is no match, NULL is returned for columns from the right table.

1. Syntax

SELECT table1.column1, table2.column2, ...FROM table1LEFT JOIN table2 ON table1.common_column = table2.common_column;

✅ Returns all records from table1 (left table).
✅ Returns matching records from table2 (right table).
✅ If no match is found, NULL is returned for table2 columns.

2. Example: Using LEFT JOIN

Tables: employees & departments

employees Table (Left Table)

departments Table (Right Table)

Query: Get Employee Names and Their Departments

SELECT employees.name, departments.department_nameFROM employeesLEFT JOIN departments ON employees.department_id = departments.id;

✅ Output:

+---------+---------------+| name    | department_name |+---------+---------------+| Alice   | IT            || Bob     | HR            || Charlie | Sales         || David   | NULL          |+---------+---------------+

🚀 Explanation:

• Alice, Bob, and Charlie have matching departments.
• David has no department_id, so department_name is NULL.

3. LEFT JOIN with Filtering (WHERE Clause)

You can filter results after performing the LEFT JOIN.

SELECT employees.name, departments.department_nameFROM employeesLEFT JOIN departments ON employees.department_id = departments.idWHERE departments.department_name = 'IT';

✅ Gets only employees from the "IT" department.
⚠️ Be careful! If you want employees without a department, use:

WHERE departments.department_name IS NULL;

4. LEFT JOIN with Aggregation (COUNT, AVG, etc.)

You can count employees per department, including empty departments.

SELECT departments.department_name, COUNT(employees.id) AS employee_countFROM departmentsLEFT JOIN employees ON employees.department_id = departments.idGROUP BY departments.department_name;

✅ Includes departments even if no employees exist.

5. Difference Between LEFT JOIN and INNER JOIN

Example:

-- INNER JOINSELECT employees.name, departments.department_nameFROM employeesINNER JOIN departments ON employees.department_id = departments.id;

✅ Excludes employees without a department (David).

6. LEFT JOIN Performance Tips

⚡ Use Indexing – Index ON columns (department_id, id) for faster joins.
⚡ Filter Early – Use WHERE conditions to reduce unnecessary data.
⚡ Check NULL Values – Always handle NULL cases in results.

7. Key Takeaways

✅ LEFT JOIN returns all rows from the left table and matching rows from the right table.
✅ If no match is found, NULL appears in the right table columns.
✅ Use WHERE ... IS NULL to find unmatched records.