Difference Between Using JOIN and WHERE to Join Tables in MySQL
In MySQL, tables can be joined using either the JOIN clause (with ON) or by putting the join condition inside the WHERE clause. While both methods can produce similar results for INNER JOINs, they behave differently for OUTER JOINs and readability.
For INNER JOINs, using JOIN ... ON or using WHERE to join tables produces identical results.
Using JOIN with ON:
SELECT *
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
Using WHERE to join:
SELECT *
FROM users u, orders o
WHERE u.id = o.user_id;
• Both return only matching rows.
When using LEFT JOIN or RIGHT JOIN, the WHERE clause can change or break the expected results.
Correct LEFT JOIN (keeps non-matching rows):
SELECT *
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
Incorrect LEFT JOIN with WHERE (removes NULL rows):
SELECT *
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id = u.id; -- This cancels the LEFT JOIN
• The WHERE clause filters out NULL rows, turning the LEFT JOIN into an INNER JOIN accidentally.
• JOIN + ON clearly separates join conditions from filter conditions.
• WHERE can unintentionally remove NULL records in OUTER JOINs.
• JOIN syntax is preferred for readability and correctness.
• WHERE joins are considered outdated and not recommended in modern SQL.
In summary: for INNER JOINs, JOIN and WHERE behave the same, but for OUTER JOINs, WHERE can break the logic. Always use JOIN with ON for clarity and correctness.