SQL Join Question?

Question

I have 2 tables (TableA and TableB). Can you tell me the difference between:
SELECT * From TableA a JOIN TableB b ON a.ID = b.ID WHERE b.Publish = 'Y' and a.ID > 5

and

SELECT * From TableA a JOIN TableB b ON a.ID = b.ID AND b.Publish = 'Y' WHERE a.ID > 5

I moved the b.Publish to be apart of the JOIN ON expression. Does it make a difference and how?

Tx

Answer 1

the database engine will process each of these differently, but i believe your final result will be the same.
your WHERE clause is processed last and benefits greatly from indexing, as do JOINs, but each row still must be evaluated separately, it's faster, because it's ordered already, but putting the where clause in the JOIN can be beneficial when the evaluated column is not indexed.
so, assuming the ID field is indexed or is a primary key (even better,) and the Publish column is not, i would say the second query should run a bit faster. and on further thinking, i'd say placing an index on the Publish column would enable the first to run faster.

Answer 2

With inner joins, like what you're doing, it doesn't make a difference. However if you are doing outer joins, then it does make a difference.

When writing a left join, if the filter is in the join clause, the result set still might contain records that don't meet the criteria because a left join allows for records in the left table that don't match the join criteria. However, if you put the filter in the where clause, none of the results can ever violate the criteria.

Answer 3

Yes it makes a subtle difference. You should make some trivial sets and work through the statements.