Which is Better ? Filter condition in JOIN clause (OR) in WHERE clause ?
We have an option to filter a result set of a select Query using a filter condition either in the WHERE clause (or) JOIN clause.
Let us consider an example:
ex1: SELECT * FROM TABLE1 INNER JOIN TABLE2 ON
TABLE1.COLUMN1 = TABLE2.COLUMN2 AND
TABLE1.COLUMN3 = 'N' AND
TABLE2.COLUMN4 <> 'N'
The same query can be written in a different manner as stated below:
ex2: SELECT * FROM TABLE1 INNER JOIN TABLE2 ON
TABLE1.COLUMN1 = TABLE2.COLUMN2
WHERE
TABLE1.COLUMN3 = 'N' AND
TABLE2.COLUMN4 <> 'N'
Which one is better ?
Software/Hardware used:
Software/Hardware used:
ASKED:
September 22, 2006 9:00 AM
UPDATED:
September 22, 2006 3:17 PM
Answer Wiki:
Filter conditions in WHERE and JOIN are identical. They are just two different syntaxes for the same thing.
For clarity, though, what I like to do is:
(a) If all joins are inner, use commas and WHERE.
(b) If any join is outer or Exception, I use explicit joins, and put the filtering items in the ON clause.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
