Overview

This article explores the behavioral differences between placing filtering conditions in the ON clause of a LEFT JOIN versus the WHERE clause. The examples use two tables: a (with data) and b (empty).

Tables Setup

Table a

CREATE TABLE product_catalog (
    record_id INT AUTO_INCREMENT PRIMARY KEY,
    item_name VARCHAR(255) NOT NULL,
    item_code VARCHAR(50) NOT NULL,
    parent_code VARCHAR(50),
    is_deleted TINYINT(1) NOT NULL DEFAULT 0
);

Table b

CREATE TABLE inventory_ref (
    ref_id INT AUTO_INCREMENT PRIMARY KEY,
    ref_code VARCHAR(50) NOT NULL,
    extra_data VARCHAR(255),
    is_deleted TINYINT(1) NOT NULL DEFAULT 0
);

Sample Insert for product_catalog

INSERT INTO product_catalog (item_name, item_code, parent_code, is_deleted) VALUES
('Product Alpha', 'P001', '100', 1),
('Product Beta',  'P001', '100', 1),
('Product Gamma', 'P001', '100', 1),
('Product Delta', 'P004', '100', 1);

inventory_ref table remains empty.

Query Comparison

First Query: Filter in WHERE

SELECT
    pc.item_name
FROM
    product_catalog pc
    LEFT JOIN inventory_ref ir ON pc.item_code = ir.ref_code
WHERE
    pc.parent_code = '100'
    AND pc.is_deleted = 1
    AND ir.is_deleted = 1;

Result: No rows returned.

Second Query: Filter in ON

SELECT
    pc.item_name
FROM
    product_catalog pc
    LEFT JOIN inventory_ref ir
        ON pc.item_code = ir.ref_code
        AND pc.is_deleted = 1
        AND ir.is_deleted = 1
WHERE
    pc.parent_code = '100';

Result:

item_name
Product Alpha
Product Beta
Product Gamma
Product Delta

Why the Difference?

The behavior stems from how LEFT JOIN operates and the order in which clauses are evaluated.

Mechanics of the First Query

1. LEFT JOIN matches rows from product_catalog with inventory_ref based on item_code = ref_code. Since inventory_ref is empty, all pc rows get NULL for ir columns.
2. WHERE applies after the join. The condition ir.is_deleted = 1 fails because ir.is_deleted is NULL, and NULL compared to any value evaluates to NULL (treated as false in a boolean context). Thus, all rows are filtered out, yielding an empty set.

Mechanics of the Second Query

1. LEFT JOIN attempts to match rows on three conditions combined with AND: pc.item_code = ir.ref_code, pc.is_deleted = 1, and ir.is_deleted = 1. Because inventory_ref is empty, no match occurs, but the left join still preserves all product_catalog rows, leaving ir columns as NULL.
2. WHERE only contains pc.parent_code = '100'. Since all product_catalog rows satisfy this, they are returned regardless of the join outcome.

Key Takeaways

• Conditions in the ON clause affect how tables are joined. They do not remove left-side rows when a right-side match fails.
• Conditions in WHERE filter the final result set and can inadvertently exclude left-side rows if they reference right-side columns that are NULL due to a failed join.
• To apply optional filters on the right table in a LEFT JOIN without losing left table rows, place the right-table conditions inside the ON clause.

Understanding this distniction helps in writing accurate SQL queries and avoiding unexpected empty results when outer joining tables.