Query Not using 'Not IN' in sql

Question

How do i write Below query not using Not IN. Because of the perfomence reasons IN, Not in are not desirable.

select d.cert_serial_number as str from mdm_device d where d.client_id in (:CLIENT_IDS) and d.cert_serial_number not in (Select cert_serial_number from mdm_device where client_id not in (:CLIENT_IDS))

Table columns of interest:

| cert_serial_number | client_id  |
+--------------------+------------+
|                102 | 1073741835 | 
|                102 | 1073741836 | 
|                102 | 1073741837 |
|                102 | 1073741838 | 
|                102 | 1073741839 | 
|                103 | 1073741840 | 
|                103 | 1073741831 | 
+--------------------+------------+------------+

If the cliend ids input are 1073741835,1073741836 output should be empty.
If the cliend ids input are 1073741835,1073741836,1073741837,1073741838,1073741839 output should be 102.

EDIT: I ended up using below query: SELECT d.cert_serial_number AS str FROM mdm_device d where d.client_id in (:CLIENT_IDS) AND NOT EXISTS (SELECT 1 FROM mdm_device e WHERE d.cert_serial_number = e.cert_serial_number AND d.client_id != e.client_id)

What you have written it seems it should work? Whats your SQL says — , May 20 '18 at 18:01

score 0 · Answer 1 · answered May 20 '18 at 18:16

Assuming your query is correct, you can use not exists:

select d.cert_serial_number as str
from mdm_device d
where d.client_id in (:CLIENT_IDS) and
      not exists (select 1
                  from mdm_device d2
                  where d2.cert_serial_number = d.cert_serial_number and
                        d2.client_id not in (:CLIENT_IDS)
                 );

Or you can can use aggregation:

select d.cert_serial_number as str
from mdm_device d
group by d.cert_serial_number
having sum(d2.client_id in (:CLIENT_IDS)) = count(*);

That is, only return devices that have those serial numbers and no others.

score 0 · Answer 2 · answered May 20 '18 at 21:51

Your problem is performance wise : no query will solve your performance problem if you're missing valuable indexes or important column that would capture an essential requirement of your system.

Where those clients you don't want the cert_serial_number be included in your results come from ? Is that possible that you've missed a column that would tremendously help discriminate those clients certificates from your results ?

My solution : I suggest you get back to your requirements and revaluate. I am pretty sure that one of those will generate a column somewhere and will help you get the result with the performance you're expecting.

Query Not using 'Not IN' in sql

2 Answers2