SQL select with ON clause moved to end of query

Question

0.00/5 (No votes)

See more:

Hi
I have a stored procedure that i'm trying to understand which has some sql (i'm using sql server, so tsql) which uses an unusual syntax. Or at least syntax that I have never come across before.
The particular section that i'm confused about is an update statement. This update statement has a where clause with a few conditions one of which is an NOT EXISTS(). The sub-query within this NOT EXISTS() is a select statement with four joins to four different tables. The ON clause of one of the joins looks to be out of place to where I would normally expect it to be. It has been moved to the bottom of the select statement. Hopefully the code below will communicate the issue.

This is the "normal" syntax used when joining four tables

-- Normal location for ON clauses 
select * from Table1 a
full outer join Table2 b on a.id=b.id 
full outer join Table3 c on b.id=c.id 
full outer join Table4 d on c.id=d.id

This is the syntax I see in the select statement

-- Second ON clause moved to end of query
select * from Table1 a
full outer join Table2 b on a.id=b.id 
full outer join Table3 c 
full outer join Table4 d on c.id=d.id 
on b.id=c.id

I'm wondering what the difference is? In this mock example I'm getting the same results returned for both queries above but the actual stored procedure i'm investigating does display a difference in the results returned depending on the location of the ON clause.

Any help appreciated.

Regards, J

Posted 28-Aug-15 0:16am

Jonathan Keaveney

Updated 28-Aug-15 0:22am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Accepted Answer · 2015-08-28T00:52:00

It changes the order of joining.

This:

SQL

select * from Table1 a
full outer join Table2 b on a.id=b.id 
full outer join Table3 c on b.id=c.id 
full outer join Table4 d on c.id=d.id

is equivalent to

SQL

select *
FROM
(
    (
        Table1 a full outer join Table2 b on a.id=b.id
    )
    full outer join Table3 c on b.id=c.id
)
full outer join Table4 d on c.id=d.id

While

SQL

select * from Table1 a
full outer join Table2 b on a.id=b.id 
full outer join Table3 c 
full outer join Table4 d on c.id=d.id 
on b.id=c.id

equals

SQL

select *
FROM
(
    (
        Table1 a full outer join Table2 b on a.id=b.id
    )
    full outer join 
    (
        Table3 c full outer join Table4 d on c.id=d.id 
    ) on b.id=c.id
)

This doesn't matter when you are doing inner joins, and in that case the optimizer may also reorder the joins for efficiency.
This is not the case for outer joins.