Not able to understand this SQL Query --CodePudding

SELECT inp.payorid InpPayorID,
       outp.payorid
INTO   #payor
FROM   analysis.dimpayor inp
       JOIN analysis.dimpayor outp
         ON inp.payorcode = outp.payorcode
            AND outp.facilityid = inp.facilityid
            AND outp.payortype = inp.payortype
            AND outp.inouttype = 'Outpatient'
WHERE  inp.inouttype = 'Inpatient'

I know This is a self-join and a temporary table is used but not able to understand the conditions. Please explain in detail

CodePudding user response：

Given that it is JOIN (and not a LEFT or RIGHT JOIN), the ON clause has the same effect as if it were in the WHERE clause.

So it is finding all of the rows where inouttype = 'Inpatient' then finding all of the rows matching on the other columns but with inouttype = 'Outpatient'.

This will have the same result set:

SELECT inp.payorid InpPayorID,
       outp.payorid
INTO   #payor
FROM   analysis.dimpayor inp
       JOIN analysis.dimpayor outp
         ON inp.payorcode = outp.payorcode
 WHERE  inp.inouttype = 'Inpatient'   
           AND outp.facilityid = inp.facilityid
           AND outp.payortype = inp.payortype
           AND outp.inouttype = 'Outpatient'

CodePudding user response：

Let's say your table looked something like this:

dimpayor

PayorID PayorCode FacilityID PayorType InOutType
------- --------- ---------- --------- ----------
1       1          1         A         Inpatient
2       1          1         A         Outpatient
1       9          100       B         Inpatient
2       9          100       B         Outpatient

In this table, for the same payor code facility payor type you have different in and out type. You want to know what the payor ID is for both.

So, you would first ask - give me all the info about Inpatient only. To do that, you'd write

select inp.* from analysis.dimpayor inp where inp.inouttype = 'Inpatient'

Great. Now, you may ask: For each of the records about, find a matching Outpatient record as long as payor code facility payor type matches.

So, you'd then write:

select inp.payorid, outp.payorid
from analysis.dimpayor inp
join analysis.dimpayor outp            -- <--- note, we are joining the same table because your in/out patient is in the same table
  on inp.payorcode = outp.payorcode    -- match combination
  and inp.facilityid = outp.facilityid -- match combination
  and inp.payortype = outp.payortype   -- match combination
  and outp.inouttype = 'Outpatient'    -- need only outpatient
where inp.inouttype = 'Inpatient'

What's happening here? It's somewhat like this - The database first looks at your FROM and WHERE clause and extracts data for InPatient.

this is inp

PayorID PayorCode FacilityID PayorType InOutType
------- --------- ---------- --------- ----------
1       1          1         A         Inpatient -- row 1
1       9          100       B         Inpatient -- row 2

Then, then database looks at the JOIN and says: ah, each record above needs to be joined to data that contains Outpatient.

Then it asks, what's the join condition? That's payor code facility payor type and for Outpatient.

It takes row 1. Then, it looks at the same table and asks it: give me all records with payor code 1, facility 1, payorcode A but inouttype should be outpatient. And it finds 1 record.

this is outp

PayorID PayorCode FacilityID PayorType InOutType
------- --------- ---------- --------- ----------
2       1          1         A         Outpatient -- related/joined to row 1

Lovely! Then, the query asks for selecting inp's payorid, which is 1 and outp's payorid, which is 2. So the first result is 1, 2.

Then, the database looks at row 2 and ask the same question, retrieves the answer, selects the payor ids and displays it.