sql server 2012 - Understanding Inner Joins

Hi experts i am reading about:Understanding Inner Joins
but i do not understand:
Why filter in ON clause?
Logical separation between filtering for purposes of join and filtering results in WHERE

can explain me with an example, like hands on lab
enrique_aeoAsked:
Who is Participating?
 
LIONKINGCommented:
I think (and apologize if not completely accurate) it means that in order to keep your "logic" cleaner, it's advisable to separate your filter (WHERE clause) from your join (INNER/LEFT/RIGHT).

Since in your join you could something like:

SELECT a.col1, b.col1
FROM a INNER JOIN b ON
a.col2=b.col2 AND a.col3<=100

In that case you're mixing up a "filter" with your join.

That separation would mean that the same statement should be rewritten as:

SELECT a.col1, b.col1
FROM a INNER JOIN b ON
a.col2=b.col2
WHERE a.col3<=100

In that case you're separating the record filter and the joining of the tables.
Hope it helps.
0
 
Umar Topia.Net Full Stack DeveloperCommented:
When you use INNER JOIN, you have to provide the ON clause for your query, which is equivalent to provide WHERE clause.

i.e.

SELECT Column1,Column2 FROM Tbl1 INNER JOIN Tbl2
ON Tbl1.MyId = Tbl2.MyId

It is equivalent to

SELECT Column1,Column2 FROM Tbl1, Tbl2
WHERE Tbl1.MyId = Tbl2.MyId
0
 
enrique_aeoAuthor Commented:
i undertstand, but what the mean?
Logical separation between filtering for purposes of join and filtering results in WHERE
0
 
Scott PletcherSenior DBACommented:
LIONKING is exactly right.

Note that this allows you to copy the JOIN logic for table a to table b and "re-use" it, since the specific condition(s) for that query ("col3 <= 100") are not in the JOIN itself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.