SELF JOING ISSUE LEFT JOIN VS JOIN

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 2008 Forums

Transact-SQL (2008)

SELF JOING ISSUE LEFT JOIN VS JOIN

Author

Topic

stevenandler
Starting Member

42 Posts

Posted - 2013-07-08 : 18:36:47

Here is a T-SQL statement I created using a self join

SELECT A.PATIENT_ID AS [RESIDENT ID ],
RTRIM(A.MRN) AS [MRN],
A.PAT_NUMBER AS [PAT NUMBER],
SUBSTRING(A.SSN,1,3) + '-' + SUBSTRING(A.SSN,4,2) + '-' + SUBSTRING(A.SSN,6,4) [SSN ],
RTRIM(A.LAST_NAME) as [LAST NAME ],
RTRIM(A.FIRST_NAME) as [FIRST NAME ],
RTRIM(A.MIDDLE_NAME) as [MIDDLE I ],
COALESCE(CONVERT(VARCHAR(10), A.BIRTH_DATE, 110), '') AS [DOB ],
CASE A.GENDER
WHEN '1' THEN 'M'
WHEN '2' THEN 'F'
END AS [GENDER ],
COALESCE(CONVERT(VARCHAR(10), A.ADMIT_DATE, 110), '') AS [ADMIT DATE ],
COALESCE(CONVERT(VARCHAR(10), A.DISCHARGE_DATE, 110), '') AS [DISCHARGE DATE ]
FROM OGEN.GEN_M_PATIENT_MAST A
--LEFT JOIN -- 52
JOIN -- this returns 33
(
SELECT MRN, max(PAT_NUMBER) LATEST_PATNUMBER
from OGEN.GEN_M_PATIENT_MAST
group by MRN
) ABC
ON A.PAT_NUMBER = ABC.LATEST_PATNUMBER
WHERE A.FACILITY_KEY='ADLU' AND DISCHARGE_DATE is null
ORDER BY A.LAST_NAME, A.FIRST_NAME
when I use a left join the t-sql returns 52 rows. When using join it returns 33 rows. Now if the join was between two different tables, it is clear why a left outer join could yield more results than an inner join. However, since this is a self join, I can't figure out why it should make a difference.

I would really appreciate it if someone could explain this to me.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-09 : 01:06:58

If you run the following queries as two separate select statements, you must get two different data sets, so in a way they are like two different tables.
[CODE]

-- TABLE 1:
SELECT A.PATIENT_ID AS [RESIDENT ID ],
RTRIM(A.MRN) AS [MRN],
A.PAT_NUMBER AS [PAT NUMBER],
SUBSTRING(A.SSN,1,3) + '-' + SUBSTRING(A.SSN,4,2) + '-' + SUBSTRING(A.SSN,6,4) [SSN ],
RTRIM(A.LAST_NAME) as [LAST NAME ],
RTRIM(A.FIRST_NAME) as [FIRST NAME ],
RTRIM(A.MIDDLE_NAME) as [MIDDLE I ],
COALESCE(CONVERT(VARCHAR(10), A.BIRTH_DATE, 110), '') AS [DOB ],
CASE A.GENDER
WHEN '1' THEN 'M'
WHEN '2' THEN 'F'
END AS [GENDER ],
COALESCE(CONVERT(VARCHAR(10), A.ADMIT_DATE, 110), '') AS [ADMIT DATE ],
COALESCE(CONVERT(VARCHAR(10), A.DISCHARGE_DATE, 110), '') AS [DISCHARGE DATE ]
FROM OGEN.GEN_M_PATIENT_MAST A

-- TABLE 2
SELECT MRN, max(PAT_NUMBER) LATEST_PATNUMBER
from OGEN.GEN_M_PATIENT_MAST
group by MRN

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 01:22:11

LEFT JOIN will give you all records from let table (in this case OGEN.GEN_M_PATIENT_MAST ) which satisfies the condition A.FACILITY_KEY='ADLU' AND DISCHARGE_DATE is null so thats what you're getting as 52
Not all these records will find a match from the same table based on condition A.PAT_NUMBER = ABC.LATEST_PATNUMBER as only latest records within each group of MRN column will have date values to match . So when you do an inner join the 33 records you get are the ones with maximum date value for each MRN group. The rest (19 records) missing are ones which have same MRN value within the group with an earlier date.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-09 : 08:24:35

Do a simple experiment. Run the following query:

SELECT  *
FROM    OGEN.GEN_M_PATIENT_MAST
WHERE   MRN IN ( SELECT MRN
                 FROM   OGEN.GEN_M_PATIENT_MAST
                 GROUP BY MRN
                 HAVING COUNT(*) > 1 )
ORDER BY MRN, PAT_NUMBER DESC

This will give you each value of MRN for which there are more than one PAT_NUMBER. Run both versions of your query and observe what happens with any one of those MRN's. When you do the inner join, only one of them will be returned. When you do left join, all of them will be returned.

stevenandler
Starting Member

42 Posts

Posted - 2013-07-09 : 11:50:22

Thank you all for the responses. I took a while but now it is sinking in. the where clause only affects the selection for the first table. so if there are two rows meeting the criteria and the left join is used, both rows will be picked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 14:11:42

quote:
Originally posted by stevenandler

Thank you all for the responses. I took a while but now it is sinking in. the where clause only affects the selection for the first table. so if there are two rows meeting the criteria and the left join is used, both rows will be picked.

Yes..thats correct

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources