MySQL JOIN: Why ON vs WHERE Placement Changes LEFT JOIN Results

The fundamental difference between ON and WHERE in MySQL joins is that ON determines how tables are connected, while WHERE filters rows after the join. This distinction has major impact in LEFT JOIN and RIGHT JOIN outer joins, but in INNER JOIN the effect is usually equivalent.

ON conditions match rows from the driven table to produce a temporary joined result set. LEFT JOIN retains all rows from the driving table, filling unmatched driven-table columns with NULL. WHERE then filters this temporary result set, removing rows that do not satisfy the condition.

Test Tables

Two tables are used for demonstration: a user table (driving table, left) and an order table (driven table, right).

-- User table (driving table, left)
CREATE TABLE `ysjz_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `ysjz_user` VALUES (1,'张三',18),(2,'李四',25),(3,'王五',30);

-- Order table (driven table, right)
CREATE TABLE `ysjz_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `ysjz_order` VALUES (1,1,100),(2,2,200),(3,2,300),(4,4,500);
-- Note: user_id=4 has no corresponding user

Scenario 1: INNER JOIN — Query users older than 20 and their orders

Method 1: Condition in ON

SELECT u.*, o.* FROM `ysjz_user` u
INNER JOIN `ysjz_order` o ON u.id = o.user_id AND u.age > 20;

Method 2: Condition in WHERE

SELECT u.*, o.* FROM `ysjz_user` u
INNER JOIN `ysjz_order` o ON u.id = o.user_id
WHERE u.age > 20;

Both methods produce identical results and have essentially the same performance.

Scenario 2: LEFT JOIN — Keep all users, show orders only for users older than 20 (orders for younger users appear as NULL)

Method 1: Condition in ON (correct)

SELECT u.*, o.* FROM `ysjz_user` u
LEFT JOIN `ysjz_order` o ON u.id = o.user_id AND u.age > 20;

Method 2: Condition in WHERE (incorrect)

SELECT u.*, o.* FROM `ysjz_user` u
LEFT JOIN `ysjz_order` o ON u.id = o.user_id
WHERE u.age > 20;

Method 2 filters out user 张三 (age 18), failing to keep all users as required.

Scenario 3: LEFT JOIN — Keep all users, show only orders with amount greater than 200 (users without qualifying orders appear with NULL order columns)

Method 1: Condition in ON (correct)

SELECT u.*, o.* FROM `ysjz_user` u
LEFT JOIN `ysjz_order` o ON u.id = o.user_id AND o.amount > 200;

Method 2: Condition in WHERE (incorrect)

SELECT u.*, o.* FROM `ysjz_user` u
LEFT JOIN `ysjz_order` o ON u.id = o.user_id
WHERE o.amount > 200;

Method 2 removes users who have no orders meeting the amount condition.

Scenarios 2 and 3 are essentially the same pattern; the only difference is which table the filter condition applies to.

Best practice: place the join keys (e.g., u.id = o.user_id) in ON. Non-join filter conditions (e.g., u.age > 20) go in ON only when you need to preserve driving-table rows; otherwise they can go in WHERE.

Summary: ON handles the join, WHERE handles filtering; use ON to keep rows in LEFT JOIN, while INNER JOIN placement is a matter of habit.