Does the location of the AND part of a join clause affect the data returned

Question

Post reply

Does the location of the AND part of a join clause affect the data returned

Tom_Hogan

Hall of Fame

Points: 3903
More actions
November 5, 2007 at 9:25 am

#199628

I've come across some old code that does an update with a join like this
[font="Courier New"]fromdbo.Orderso
joindbo.Accountacctwith (nolock)onh.AccountID = acct.AccountID
joinsourceOrdersaawith (nolock)onh.InvoiceNumber = aa.SourceInvoiceNumber
and aa.LineType = 'AA'
joinsourceOrdersabwith (nolock)onh.InvoiceNumber = ab.SourceInvoiceNumber
and acct.Acct = aa.SourceAccount
and ab.LineType = 'AB'[/font]
Notice that as part of the ab join these is this and acct.Acct = aa.SourceAccount.
I would think this should be part of the aa join. My question is, will the joins as written above affect the data I get back?
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

You better put it IN the JOIN or use WHERE. example:

from dbo.Orders o

join dbo.Account acct with (nolock) on h.AccountID = acct.AccountID

join sourceOrders aa with (nolock) on h.InvoiceNumber = aa.SourceInvoiceNumber

and aa.LineType = 'AA' AND acct.Acct = aa.SourceAccount

join sourceOrders ab with (nolock) on h.InvoiceNumber = ab.SourceInvoiceNumber

and ab.LineType = 'AB'

OR

from dbo.Orders o

join dbo.Account acct with (nolock) on h.AccountID = acct.AccountID

join sourceOrders aa with (nolock) on h.InvoiceNumber = aa.SourceInvoiceNumber

and aa.LineType = 'AA'

join sourceOrders ab with (nolock) on h.InvoiceNumber = ab.SourceInvoiceNumber

WHERE acct.Acct = aa.SourceAccount

and ab.LineType = 'AB'

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

My experience tells me that no - it doesn't matter that it's in the "wrong" JOIN statement. It just makes it harder to read later on....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

I agree with Matt, the optimizer is going to give you the same execution plan regardless of where you put this piece of criteria.

Putting it near the join for the table will make it far easier to read later though.

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Because of the types of joins involved it will not affect your resultset in any manner. Had this been LEFT or RIGHT joins it would. But for readability and better comprehension you may want to alter as previously suggested.

Tom_Hogan Hall of Fame Points: 3903 More actions · Answer 5

Thanks for the replies.

As I said I was worried that it was affecting the data returned.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

Heh... if you're worried that it will affect the data returned, you need to test!

And the position of the AND somecol = someval code does make a difference if you ever happen to run into an outer join.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Jeff -

Do you have a good example of that? I'd be curious to see one. (the outer join getting messed up due to position of the AND)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Antares686 SSC Guru Points: 125444 More actions · Answer 8

SET NOCOUNT ON

DECLARE @a TABLE (A1 int, A2 char(1), A3 int, A4 varchar(10))

DECLARE @b-2 TABLE (B1 int, B2 char(1), B3 varchar(10))

DECLARE @C TABLE (C1 int, C2 varchar(10))

INSERT @a (A1, A2, A3, A4) VALUES (1,'A', 1, 'Row 1')

INSERT @a (A1, A2, A3, A4) VALUES (2,'A', 1, 'Row 2')

INSERT @a (A1, A2, A3, A4) VALUES (3,'B', 1, 'Row 3')

INSERT @a (A1, A2, A3, A4) VALUES (4,'B', 2, 'Row 4')

INSERT @a (A1, A2, A3, A4) VALUES (5,'A', 2, 'Row 5')

INSERT @a (A1, A2, A3, A4) VALUES (6,'A', 3, 'Row 6')

INSERT @a (A1, A2, A3, A4) VALUES (7,'C', 4, 'Row 7')

INSERT @a (A1, A2, A3, A4) VALUES (8,'B', 2, 'Row 8')

INSERT @b-2 (B1,B2,B3) VALUES (1,'A','B Row 1')

INSERT @b-2 (B1,B2,B3) VALUES (3,'B','B Row 2')

INSERT @b-2 (B1,B2,B3) VALUES (2,'A','B Row 3')

INSERT @b-2 (B1,B2,B3) VALUES (1,'A','B Row 4')

INSERT @b-2 (B1,B2,B3) VALUES (7,'C','B Row 5')

INSERT @b-2 (B1,B2,B3) VALUES (1,'A','B Row 6')

INSERT @b-2 (B1,B2,B3) VALUES (8,'A','B Row 7')

INSERT @b-2 (B1,B2,B3) VALUES (8,'B','B Row 8')

INSERT @C (C1,C2) VALUES (1,'C Row 1')

INSERT @C (C1,C2) VALUES (2,'C Row 2')

INSERT @C (C1,C2) VALUES (3,'C Row 3')

INSERT @C (C1,C2) VALUES (4,'C Row 4')

INSERT @C (C1,C2) VALUES (5,'C Row 5')

INSERT @C (C1,C2) VALUES (6,'C Row 6')

/* -- Demonstrates Inner Joins make no difference in position of a specific join item.

SELECT * FROM

@a A

INNER JOIN

@b-2 B

ON

A.A1 = B.B1

INNER JOIN

@C C

ON

A.A2 = B.B2 AND

A.A3 = C.C1

SELECT * FROM

@a A

INNER JOIN

@b-2 B

ON

A.A2 = B.B2 AND

A.A1 = B.B1

INNER JOIN

@C C

ON

A.A3 = C.C1

*/

-- Demonstrates when an Outer Join involved it can.

SELECT * FROM

@a A

INNER JOIN

@b-2 B

ON

A.A2 = B.B2 AND

A.A1 = B.B1

LEFT JOIN

@C C

ON

A.A3 = C.C1

SELECT * FROM

@a A

INNER JOIN

@b-2 B

ON

A.A1 = B.B1

LEFT JOIN

@C C

ON

A.A2 = B.B2 AND

A.A3 = C.C1

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

A picture is worth 1000 words....Thanks!

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 10

Thanks, Antares!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)