SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SELF JOING ISSUE LEFT JOIN VS JOIN
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

stevenandler
Starting Member

USA
42 Posts

Posted - 07/08/2013 :  18:36:47  Show Profile  Reply with Quote
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

547 Posts

Posted - 07/09/2013 :  01:06:58  Show Profile  Reply with Quote
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.


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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/09/2013 :  01:22:11  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 07/09/2013 :  08:24:35  Show Profile  Reply with Quote
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.
Go to Top of Page

stevenandler
Starting Member

USA
42 Posts

Posted - 07/09/2013 :  11:50:22  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/09/2013 :  14:11:42  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000