Interesting Observation of ON Clause on LEFT JOIN

home
database
view story

14 Kicks

Interesting Observation of ON Clause on LEFT JOIN(blog.sqlauthority.com)

submitted by pinaldave(9662) 3 years, 2 months ago

Very simple explanation of : Although the placement of such predicates does not make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join.

5 comments |category: Database |Views: 330
Tweet

tags: SQLAuthority pinaldave SQLServer Database SQL another

Everyones tags:

Your Tags:

Please login or signup.

Add a live kick counter to your blog >> liveImage

You can even customize the image by choosing your own colors, and then clicking the button below to update the preview and the html code:

"Kick It" text
"Kick It" background
kick count text
kick count background
border

Simply copy and paste this HTML into your blog post.

Users who kicked this story:

jeet(0) -

jadedgeek(15) -

dripiot(125) -

vivekamar(6106)

More who kicked this story...

Comments:

posted by pinaldave(9662) 3 years, 2 months ago 0

I am eager to know what SQL Experts and DotNet Experts think of this blog article.

I did my best to write in as simple words as I can.

posted by pinaldave(9662) 3 years, 2 months ago 0

I would really like to get more feedback from users. So far I have not got much response on this, as it is quite important to know if this kind of articles user like or does not like.

posted by just3ws(560) 3 years, 2 months ago 0

@PinalDave I had always ordered my joins in the direction of the join (Left Table first on Left Join, Right Table first on Right Join) but didn't know it *actually* mattered. As always good stuff, and very educational for those of us not versed in the minutiae of Sql Server.

posted by powerrush(3873) 3 years, 2 months ago 0

Can this idea be summed up as: "Unlike ON predicates, the WHERE clause is evaluated AFTER the join. In other words, the WHERE clause is evaluated against the joined result." ?

posted by powerrush(3873) 3 years, 2 months ago 0

Something that may be interesting to point out is that this behavior also affects performance. Depending on the data involved, I have seen INNER JOIN queries run faster when predicated in the ON vs. in the WHERE. I would recommend you write an article on that subject if you can reproduce the behavior I saw with testing (differring plan, slowness).

Most Active Users

What's this?