Conditionals in WHEREs or JOINs?

Question

Lets say I have the following query:

SELECT occurs.*, events.*
FROM occurs
    INNER JOIN events ON (events.event_id = occurs.event_id)
WHERE event.event_state = 'visible'

Another way to do the same query and get the same results would be:

SELECT occurs.*, events.*
FROM occurs
    INNER JOIN events ON (events.event_id = occurs.event_id
        AND event.event_state = 'visible')

My question. Is there a real difference? Is one way faster than the other? Why would I choose one way over the other?

do an execution plan on both queries if there are difference the database will show you! — Jorge Campos, Sep 02 '15 at 16:24
2nd one is faster. Especially if you have `event.event_state` indexed — Alex, Sep 02 '15 at 16:24
Add `EXPLAIN` before each query. It'll show you the how the query optimizer is running it and the index(es) being used. P.S. I'm pretty sure these are identical. — gen_Eric, Sep 02 '15 at 16:29
Related: [INNER JOIN ON vs. WHERE clause](https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) — Damian Yerrick, Oct 22 '15 at 14:54

Damian Yerrick · Accepted Answer · 2015-12-17T03:06:53.740

For an INNER JOIN, there's no conceptual difference between putting a condition in ON and in WHERE. It's a common practice to use ON for conditions that connect a key in one table to a foreign key in another table, such as your event_id, so that other people maintaining your code can see how the tables relate.

If you suspect that your database engine is mis-optimizing a query plan, you can try it both ways. Make sure to time the query several times to isolate the effect of caching, and make sure to run ANALYZE TABLE occurs and ANALYZE TABLE events to provide more info to the optimizer about the distribution of keys. If you do find a difference, have the database engine EXPLAIN the query plans it generates. If there's a gross mis-optimization, you can create an Oracle account and file a feature request against MySQL to optimize a particular query better.

But for a LEFT JOIN, there's a big difference. A LEFT JOIN is often used to add details from a separate table if the details exist or return the rows without details if they do not. This query will return result rows with NULL values for b.* if no row of b matches both conditions:

SELECT a.*, b.*
FROM a
LEFT JOIN b
ON (condition_one
    AND condition_two)
WHERE condition_three

Whereas this one will completely omit results that do not match condition_two:

SELECT a.*, b.*
FROM a
LEFT JOIN b ON some_condition
WHERE condition_two
  AND condition_three

_{Code in this answer is dual licensed: CC BY-SA 3.0 or the MIT License as published by OSI.}

Conditionals in WHEREs or JOINs?

1 Answers1