sql-serveroptimizationcommon-table-expressiontemp-tablesnotin

Use NOT IN instead of CTE or temp table

select distinct patientID
from [dbo]..HPrecords 
where ptprocess = 'refill'
and pTDescription <> 'Success'
and patientID is not null
and patiententrytime > '2021-04-06'
and patientID not in (
    select distinct patientID
    from [dbo]..HPrecords 
    where ptprocess = 'embossing'
    and ptDescription = 'Success'
    and patientID is not null
    and patiententrytime > '2021-04-06'
)

So I want to use a NOT IN feature in SQL to filter out the patients that haven't received their refill medication yet. A patient can be refilled multiple times, the first time can fail, but the second or third time it can be successful. So there can be multiple rows.

So I just want to write a query that will filter out and get me the patientID that DID NOT SUCCEED in getting refill at all no matter how many times.

Is this the best way to write it, my current query is still running, I think the logic is wrong?

I want to try to write this query without CTE or temp table just as an exercise.

Sample output:

PatientID
151761
151759
151757
151764
Solution

  • I personally prefer joins above not-in. Looks neater, reads better and allows one to access information on both tables if you need to analyse anomalies. A colleague and I once did some very basic performance comparisons and there was no notable difference.

    Here's my take on it..

    select  distinct hpr.patientID
from    [dbo].HPrecords hpr
        LEFT OUTER JOIN
            [dbo].HPrecords hpr_val ON
                hpr.patientID = hpr_val.patientID
                AND hpr_val.ptprocess = 'embossing'
                AND hpr_val.ptDescription = 'Success'
                and hpr_val.patiententrytime > '20`enter code here`21-04-06'
where   hpr.ptprocess = 'refill'
and     hpr.pTDescription <> 'Success'
--and       hpr.patientID is not null  -- Not necessary because you will always have records in this table in this scenario
and     hpr.patiententrytime > '2021-04-06'
AND     hpr_Val.PatietID IS NULL

    And on the extra dot in between the schema and table name... As Smor pointed out it is not necessary (Might even break the query) and rather used when you do not want to reference the schema when pointing to a database and table.