In SQL / MySQL, what is the difference between "ON" and "WHERE" in a join statement?

sql

join

performance

best-practices

byNikita Barsukov·Oct 29, 2024

ON in SQL is the decorator aligning tables together, while WHERE is the picky friend choosing from the result. In practice:

SELECT a.*, b.*
FROM TableA a
JOIN TableB b ON a.id = b.foreign_id -- ON says: Hey! you two look the same!
WHERE b.value > 100; -- WHERE says: Ehh.. Can we get any row that's not so cheap!

ON set up the tables and WHERE helps to trim down the gathering.

Under the hood of joins

In JOIN operations, ON clause behaves akin to rules of connection between relevant rows of tables. Contrarily, the WHERE clause, like a surgeon, operates on the final result, meticulously selecting or rejecting rows based on the set condition.

Left outer join - a special case

In LEFT OUTER JOIN, ON clause plays a pivotal role in deciding rows for the final output, even if the ON clause faces a stiff blow, it retains left table rows with NULL right-table columns. However, the WHERE clause, like an overseer, might filter out these NULL values, transforming your left join into an inner approximation.

Performance implication - the hidden speedster

ON and WHERE clause can be your undercover agents rendering performance optimization. Filtering early with ON can reduce the processed data set, leading to expedited execution. Yes, we all love faster queries!

The nitty-gritties of joins

INNER JOIN gives off an illusion of flexibility, making ON and WHERE seem interchangeable. However, OUTER JOINS manifest a significant difference. ON in LEFT OUTER JOIN forms a map displaying potential NULL spots for unmatched rows. Meanwhile, WHERE can further filter these results, effectively altering the size of your map.

Red flags and workarounds

A common pitfall is misjudging ON and WHERE in a LEFT OUTER JOIN. A WHERE clause filtering the right table fields may inadvertently offset left table rows that should have been included. An IS NULL badge or COALESCE within the WHERE clause can help avoid this.

Readable queries are happy queries

To champion readable queries, try to religiously use ON for creating links between tables and WHERE for setting filter conditions. This not only follows good practices but also improvises the maintainability of your code base.