Are these two selects statements the same?

Question

Post reply

Are these two selects statements the same?

Bob Bridges-173967

SSCrazy

Points: 2094
More actions
January 23, 2007 at 8:09 pm

#117078

Greetings to the group!
I need some help understanding the nuances of T-SQL. I have two statements that return the same data. My question is this... are they really the same or am I missing something that they could return different result sets under different situations?
Statement 1:
SELECT BMP2WO, BCYMD, BSHIFT, BCOMP, BCLOCK, BHOURS, BPOST
FROM MP2BATF A, WOE B
WHERE A.BMP2WO = B.WONUM
AND A.BCYMD = B.CLOSEDATE
AND A.BCLOCK = B.EMPCODE
Statement 2:
SELECT BMP2WO, BCYMD, BSHIFT, BCOMP, BCLOCK, BHOURS, BPOST
FROM MP2BATF
WHERE EXISTS (SELECT *
  FROM WOE
  WHERE WONUM = MP2BATF.BMP2WO
  AND CLOSEDATE = MP2BATF.BCYMD
  AND EMPCODE = MP2BATF.BCLOCK)
I appreciate any insight you can provide.
I would also appreciate any recommendations for good books/guides on T-SQL programming that would deal with topics similar to this.
Bob

Viewing 5 posts - 1 through 4 (of 4 total)

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

dclark SSC Eights! Points: 843 More actions · Answer 1

They can return different results.

Statement 1 can return multiple rows for each row in MP2BATF if there are multiple matching rows in WOE.

Statement 2 will return only one row for each row in MP2BATF that has any matching rows in WOE.

shashi kant SSCommitted Points: 1659 More actions · Answer 2

shashi kant

SSCommitted

Points: 1659

January 23, 2007 at 9:19 pm

#685128

yes i agree with dclark

he is right.

Bob Bridges-173967 SSCrazy Points: 2094 More actions · Answer 3

Thanks to both of you for your responses. I see where what you say is true. In my specific case it is not a problem because these fields are part of the primary key.

But your responses made me see where they could be different.

Thanks for helping me to see this!

Bob

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Might also want to check performance and resource usage of both... statement 2 is a correlated subquery and they can be quite greedy depending on the tables involved.

--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)