Is there a more efficient way to write my filters in this specific context?

user_3115933 0
Viewed 49

I am using SQL Server 2014 and I have a table (t1) in my database which contains a list of numbers (n1 to n6). An extract is given below:

Id   n1   n2   n3   n4   n5   n6
100  3    10   26   31   35   39
101  1    3    11   22   36   40
102  10   19   20   30   39   40
103  6    12   25   27   28   33
...

Assuming I want to filter out this table by excluding rows where numbers 3 and 19 exist, my filtering codes would look like this:

Select * from t1

WHERE [n1] not in (3,19)
AND [n2] not in (3,19)
AND [n3] not in (3,19)
AND [n4] not in (3,19)
AND [n5] not in (3,19)
AND [n6] not in (3,19)

Expected Output:

Id   n1   n2   n3   n4   n5   n6
103  6    12   25   27   28   33
...

Is there a more efficient way to write my filters?

unpivot select tsql sql-server sql
3 Answers

One option uses not exists and values():

select t.*
from mytable t
where not exists (
    select 1
    from (values(n1), (n2), (n3), (n4), (n5), (n6)) x(n)
    where n in (3, 19)
)

This scales better than you original query when the number of columns and/or values in the list increases - although this will not necessarily be more efficient.

Demo on DB Fiddle:

 Id | n1 | n2 | n3 | n4 | n5 | n6
--: | -: | -: | -: | -: | -: | -:
103 |  6 | 12 | 25 | 27 | 28 | 33
user_106767160

Your filter is fine, but you might find it simpler to write as:

where 3 not in (n1, n2, n3, n4, n5, n6) and
      19 not in (n1, n2, n3, n4, n5, n6)

Because you are referencing all six columns with inequalities, you cannot really improve the performance. You could fix the data model so the columns are in separate rows -- allowing an index to be used.

user_11440350

Place the Set of Values not Wanted in a CTE and Use the Except Operator

As others have noted, the data model is less than ideal.

To work around the NOT IN filter inefficiencies, you could use the EXCEPT operator like this:

--sample data
   WITH smple ( id, n1, n2, n3, n4, n5, n6) AS
    ( SELECT 100, 3, 10, 26, 31, 35, 39
    union all
    select 101,1,3,11,22,36,40
    union all
    select
    102,10,19,20,30,39,40
    union all
    select
    103,6,12,25,27,28,33
    ),
--end sample data
    not_vals (n) as (select 3 union all select 19 ) 
    SELECT
        s.*
    FROM
        smple s
    EXCEPT 
    SELECT
        s.*
    FROM
        smple s join not_vals nv on
                nv.n IN ( s.n1, s.n2, s.n3, s.n4, s.n5, s.n6)
     ;

It would be interesting to look at the efficiency of the various approaches one can use from a performance perspective.

The final solution should have good performance, so a correlated subquery probably will not provide that.

Here is a correlated subquery:

--sample data
with smple ( id, n1, n2, n3, n4, n5, n6) AS
( SELECT 100, 3, 10, 26, 31, 35, 39
union all
select 101,1,3,11,22,36,40
union all
select
102,10,19,20,30,39,40
union all
select
103,6,12,25,27,28,33
),
--end sample data
not_vals (n) as (select 3 union all select 19 ) 
SELECT
    s.*
FROM
    smple s
WHERE
    NOT EXISTS (
        SELECT 1 FROM not_vals nv
        WHERE
            nv.n IN ( s.n1, s.n2, s.n3, s.n4, s.n5, s.n6)
    )
;
user_25169440
Related