Which is Better ? Filter condition in JOIN clause (OR) in WHERE clause ?

pts.
Tags:
SQL
SQL Server
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 ?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following