? MySQL LEFT JOIN

? Quick Overview

The LEFT JOIN keyword in MySQL returns all records from the left table and the matching records from the right table. If there's no match, the right table's columns will contain NULL.

? Key Concepts

All left rows: LEFT JOIN always keeps all rows from the left table.
Nullable right values: When no match exists, the right-side columns become NULL.
Use cases: Listing entities with optional related data (employees without departments, products without orders, etc.).

⚙️ Syntax / Theory

? View Code Example

// Syntax for LEFT JOIN: keep all rows from table1 and match table2 where possible
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

? Code Example(s)

? View Code Example

// Get all employees and their department names (include employees with no department)
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

? View Code Example

// Get all products and their related order quantities (include products never ordered)
SELECT products.product_name, orders.quantity
FROM products
LEFT JOIN orders
ON products.product_id = orders.product_id;

? Live Output / Explanation

What to expect

If an employees row has no matching departments row, department_name will be NULL.
LEFT JOIN preserves all rows from the left table while selectively pulling data from the right table.
Use WHERE cautiously — applying a WHERE clause to a right-table column can effectively turn the LEFT JOIN into an INNER JOIN unless you account for NULLs (e.g. WHERE departments.department_name IS NULL to find unmatched rows).

✅ Tips & Best Practices

Prefer LEFT JOIN when you need to include unmatched left-table rows in the result.
Always verify join conditions (matching keys and data types) to avoid accidental Cartesian results.
To filter for unmatched rows, test for IS NULL on a right-table column.

? Try It Yourself / Practice Tasks

Write a query to display all customers and their orders, including customers who haven’t placed any orders.
Use LEFT JOIN to list all products and their order quantities, including products that haven’t been ordered.
Retrieve all employees and their department names, including employees without departments.