SQL LEFT JOIN: Syntax, Usage, and Examples

The LEFT JOIN keyword allows you to combine rows from two tables while preserving all records from the left (or first) table, even if there’s no matching record in the right (or second) table. LEFT JOIN is commonly used to fetch related data while maintaining unmatched entries from the primary dataset.

How to Use LEFT JOIN

The general syntax for a LEFT JOIN is:

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

This syntax returns all rows from table1 and the matched rows from table2. If there is no match, the result is NULL for columns from table2.

Example: Basic LEFT JOIN

SELECT customers.name, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

This query shows all customers, even those who haven’t placed any orders. The order date will be NULL for such customers.

When to Use LEFT JOIN

The LEFT JOIN is ideal when:

• You want to preserve all records from one table, regardless of matching values in the second table
• You need to identify missing relationships (e.g., customers without orders)
• You want to include optional or non-mandatory associations
• You're auditing, debugging, or analyzing relational integrity

It’s commonly used in reporting, data warehousing, and troubleshooting.

Scenario: Find Employees Without Assigned Projects

SELECT employees.name, projects.project_name
FROM employees
LEFT JOIN projects
ON employees.id = projects.employee_id;

Unassigned employees will still appear with NULL for project_name.

LEFT JOIN SQL Examples

Example 1: LEFT JOIN with WHERE Clause

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.order_id IS NULL;

This shows customers who have never placed an order. It's a powerful pattern for detecting gaps in data.

Example 2: LEFT OUTER JOIN

SELECT p.name, c.category_name
FROM products p
LEFT OUTER JOIN categories c
ON p.category_id = c.id;

The LEFT OUTER JOIN is functionally the same as LEFT JOIN—the keyword “OUTER” is optional.

Example 3: LEFT JOIN vs RIGHT JOIN

Understanding the difference between left and right joins is key:

• LEFT JOIN SQL keeps all records from the left table.
• RIGHT JOIN SQL keeps all records from the right table.

-- LEFT JOIN
SELECT a.name, b.info
FROM A a
LEFT JOIN B b ON a.id = b.a_id;

-- RIGHT JOIN (same result, flipped)
SELECT b.info, a.name
FROM B b
RIGHT JOIN A a ON a.id = b.a_id;

Use the one that aligns better with how you structure your query.

Example 4: LEFT JOIN with Multiple Conditions

SELECT s.student_name, e.exam_score
FROM students s
LEFT JOIN exams e
ON s.id = e.student_id AND e.subject = 'Math';

This returns all students and their Math exam scores—NULL if they didn’t take it.

Learn More About LEFT JOIN

LEFT JOIN vs INNER JOIN

An INNER JOIN returns only matching rows from both tables. LEFT JOIN includes all rows from the left table, even without a match.

-- INNER JOIN
SELECT a.name, b.detail
FROM A a
INNER JOIN B b ON a.id = b.a_id;

-- LEFT JOIN
SELECT a.name, b.detail
FROM A a
LEFT JOIN B b ON a.id = b.a_id;

The left join retains all A entries—even if B has no match.

LEFT JOIN vs RIGHT JOIN

These are mirror opposites. If you switch the order of tables, a left join becomes a right join:

-- LEFT JOIN A to B
SELECT A.id, B.info
FROM A
LEFT JOIN B ON A.id = B.a_id;

-- RIGHT JOIN B to A (same outcome)
SELECT A.id, B.info
FROM B
RIGHT JOIN A ON A.id = B.a_id;

Both are useful; choose based on query readability.

LEFT JOIN on Multiple Tables

You can chain left joins to bring data from several tables:

SELECT e.name, d.department_name, l.location
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
LEFT JOIN locations l ON d.location_id = l.id;

This shows employees with their department and location, even if some data is missing.

Filtering After a LEFT JOIN

Be cautious with WHERE clauses. Applying conditions directly after a LEFT JOIN may filter out null-matching records:

-- Might exclude left-only rows
SELECT *
FROM A
LEFT JOIN B ON A.id = B.a_id
WHERE B.type = 'premium';

-- Safer version
SELECT *
FROM A
LEFT JOIN B ON A.id = B.a_id AND B.type = 'premium';

Move filters into the ON clause to preserve left-side rows.

Performance and Indexing Tips

• Use indexes on joined columns to speed up LEFT JOIN queries.
• Avoid unnecessary joins when only looking for unmatched records—consider NOT EXISTS or NOT IN for better performance on large datasets.
• Review execution plans in tools like SSMS or EXPLAIN to identify slow joins.

Use LEFT JOIN in Data Cleanup

This query finds orphaned records—entries in one table with no parent:

SELECT o.id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

Perfect for identifying and deleting invalid or outdated data.

LEFT JOIN with Aggregates

SELECT c.name, COUNT(o.order_id) AS total_orders
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

Even customers with zero orders are included.

LEFT JOIN on Views

You can use LEFT JOIN with views just like with tables:

SELECT e.name, v.salary
FROM employees e
LEFT JOIN employee_summary_view v ON e.id = v.employee_id;

This is useful for abstracting complexity and keeping queries clean.

The LEFT JOIN keyword is a flexible, essential part of querying relational databases. It allows you to maintain visibility into unmatched records, analyze relationships, and build comprehensive reports that reflect all entries from your main dataset.