asked on

Compare two SQL queries

I have two version of the code below:

Ver 1:
Select
p.facnum, 
count(distinct case when m.contr = 3 then p.resnum else NULL end),
count(distinct p.resnum)
from patients p, patients_meds pm, meds m
where p.resnum=pm.resnum and pm.pmcode=m.pmcode
group by p.facnum

Ver 2:
Select
p.facnum,
      sum(case when exists (
            Select *
            from patients_meds pm
            inner join meds m on pm.pmcode=m.pmcode
            where p.resnum=pm.resnum and m.contr = 3) then 1 else 0 end),
count(p.resnum)
from patients p
group by p.facnum

Open in new window

The result is different a little bit. Below is data I extract from the result. First 3 columns for V1 of code and next 3 columns is for V2 of code. Next 3 columns is for V1 of code but do not have the distinct in count
data.xls

Why there is the different? I don't understand first is about:
Query1: count(distinct p.resnum)
Query2: count(p.resnum)
If I remove distinct in the first version of the code (Query 1) the result will change so much
There is no change if I include distinct in V2 of code (Query 2)

I think maybe the different if we use "then p.resnum else NULL end" in V1 and "then 1 else 0 end" in V2 is also a problem.

Do you have any ideas?

cyberkiwi

I think I was mislead by your diagram which showed

patient : 1/1-1/M : patients_med : 1/M-1/1 : med

But in fact, I can tell that the data is actually

patient : 1/1-0/M : patients_med : 1/M-?/1 : med (notice the 0 and ?)

The 2nd one:
It will list ALL patients. This is regardless of whether they have patients_med records.

The 1st one:
The joins between the 3 tables will result in ONLY patients that have a link to patients_med and from there to med. If a patient has no record in patients_med, it will disappear from the 3rd column count.

The 3rd column counts the number of patients, so depending on which query is used, you get a different result.
The 2nd column count will always be the same because it counts only records that exist.

SOLUTION

Raja Jegan R

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

cyberkiwi

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

cyberkiwi

rrjegan17,

sum(case when m.contr = 3 then 1 else 0 end)

That won't give the right result because it will count the same person multiple times due to the cartesian product from joining across the 3 tables.

Raja Jegan R

>> That won't give the right result because it will count the same person multiple times

Yes, it won't and that's what I tried to explain that COUNT would count all values even NULL values in the ver 1.

cyberkiwi

>> Yes, it won't and that's what I tried to explain that COUNT would count all values even NULL values in the ver 1.

Are you sure NULLs are counted?