• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

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
0
enrique_aeo
Asked:
enrique_aeo
2 Solutions
 
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
 
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
 
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now