What’s the different between these 2 sql’s — a condition to put into WHERE or ON

Q: What’s the different between these 2 queries?

left outer join CodeLegend c on spv.StatusUid=c.CodeLegendUid
where c.FullName=’Active’
———————————————————————— ———-
left outer join CodeLegend c on spv.StatusUid=c.CodeLegendUid
and c.FullName=’Active’

%%A: all rows from outer table will show up in intermediate table. The “Active” condition becomes “and (c.FullName=’Active’ or c.FullName is null)” if put into ON clause

If in doubt, I usually put conditions in WHERE (not ON) because WHERE is less implicit and more explicit. Intermediate table tends to show more meaningful data this way. Not sure about this case though.

See http://searchoracle.techtarget.com/answer/LEFT-OUTER-JOIN-with-ON-condition-or-WHERE-condition

http://blog.sqlauthority.com/2009/03/15/sql-server-interesting-observation-of-on-clause-on-left-join-how-on-clause-effects-resultset-in-left-join/ explains right-table predicate in ON-clause

The more obscure issue is Outer table predicate in ON-clause, explained in other blog post…

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s