Tough joins in the FROM versus in the WHERE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Tough joins in the FROM versus in the WHERE

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-22 : 08:24:23

Dave writes "WHY DO THE TWO QUERIES BELOW PRODUCE TWO DIFFERENT SETS OF RESULTS?

Thanks,
Dave

#1)

SELECT distinct z.memberid
FROM (SELECT m.memberid, mc.membercaseid
FROM member m left outer join membercase mc on m.memberid = mc.memberid
join memberpolicy mp on m.memberpolicyid = mp.memberpolicyid
join clientsubgroup csg on mp.clientsubgroupid = csg.clientsubgroupid
WHERE m.eligibilitystatuscd = 'active'
AND csg.name like '%xyzcompany%') as z

WHERE z.membercaseid is null
ORDER by z.memberid

------------------------ VERSUS-------------------
#2)

SELECT distinct z.memberid
FROM (SELECT m.memberid, mc.membercaseid
FROM member m, membercase mc, memberpolicy mp, clientsubgroup csg
WHERE m.memberid *= mc.memberid
AND m.memberpolicyid = mp.memberpolicyid
AND mp.clientsubgroupid = csg.clientsubgroupid
AND m.eligibilitystatuscd = 'active'
AND csg.name like '%xyzcompany%') as z

Where z.membercaseid is null
Order by z.memberid;"

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-12-22 : 09:12:03

The first query gives the correct result, T-SQL acts funny with NULLs. Stick to the ANSI syntax and you will not have this problem.

________________
Make love not war!

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-12-22 : 09:21:25

I agree, although I would have said the problem was not in the use of NULLs but in the command difference between LEFT OUTER JOIN and m.memberid *= mc.memberid.

LEFT OUTER JOIN will return each row in member once, regardless of whether there is a matching memberid row in membercase.

m.memberid *= mc.memberid will return multiple of copies of each row in member, one for each row in membercase where the memberid is different.

Raymond

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-12-22 : 09:45:59

Try this :

[CODE]
CREATE TABLE TBLA (MYID INT, COL2 INT, COL3 INT)
INSERT INTO TBLA VALUES (1, 3, 3)
INSERT INTO TBLA VALUES (2, 23, 1978)
INSERT INTO TBLA VALUES (3, 25, 8)
INSERT INTO TBLA VALUES (4, 1124, 645)

CREATE TABLE TBLB (MYID INT, COL4 INT, COL5 INT)
INSERT INTO TBLB VALUES (3, 25, 8)
INSERT INTO TBLB VALUES (4, 1124, NULL)

SELECT A.COL2, A.COL3, B.COL4, B.COL5
FROM TBLA A,
TBLB B
WHERE A.MYID *= B.MYID
AND COL5 IS NULL

SELECT A.COL2, A.COL3, B.COL4, B.COL5
FROM TBLA A
LEFT OUTER JOIN TBLB B
ON A.MYID = B.MYID
WHERE B.COL5 IS NULL
[/CODE]

________________
Make love not war!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources