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?
YANKAUSKASAsked:
Who is Participating?
 
cyberkiwiCommented:
If you turn ver1 around to left joins:

Select
p.facnum,
count(distinct case when m.contr = 3 then p.resnum else NULL end),
count(distinct p.resnum)
from patients p
left join patients_meds pm on p.resnum=pm.resnum
left join meds m on pm.pmcode=m.pmcode
group by p.facnum

You should now get exactly the same result as ver 2.
0
 
cyberkiwiCommented:
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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> Query1: count(distinct p.resnum)
Query2: count(p.resnum)

Having DISTINCT keyword inside would count only the unique / distinct resnum values. Say if you have 2 resnum records with values 1, 1 & 2, then
count(distinct p.resnum)  = 2
count(p.resnum) = 3

Therefore resultset would have difference based upon that.

>> count(distinct case when m.contr = 3 then p.resnum else NULL end),
>> sum(case when exists ( ) then 1 else 0 end),

On the same logic, count distinct would count all values of p.resnum if m.contr = 3 even if it is decoded as NULL.
two values are NULL are not equal and hence those will also be counted and hence the correct version should be

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

Hope this clarifies.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
cyberkiwiCommented:
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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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.
0
 
cyberkiwiCommented:
>> 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?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.