CODEWITHSUNDEEP
sqlwhere-clause
user3229801
user3229801

Reputation: 73

Multiple WHERE conditions WITH NOT IN

I am working on tuning a stored procedure. The stored procedure is doing a SELECT and has around 50 WHERE conditions. The SQL is similar to below. Can you please let me know if there is a better way to check these conditions

SELECT * FROM table A 
JOIN table B
ON A.ID = B.ID
WHERE
  (
            (
            A.name = 'abc'
            and B.AID not in ( 111, 222) 

            )
        or

        (A.name = 'def'
        and B.AID not in ( 222,1113,111,654,199,43,243,643,754244,2434) 
        )
        or

        (
        A.name = 'etd'
        and B.AID not in ( 111,345,54,34,454) 

        )
        or

        (
        A.name = 'ent'
        and B.AID not in ( 111,188,199,1647,128006) 
        )
        or

        (
        A.name = 'yyy'
        and B.AID not in (111,188,1113,1647) 
        )
        or

        (
        A.name = 'uuu'
        and B.AID not in (111,188,1113,1647) 
        )
        or

        (
        A.name = 'jyf'
        and B.AID not in (111,188,1647,344,45444,645) 
        )
        or

        (
        A.name = 'tut'
        and B.AID not in (111,222,1113,1647) 
        )

)

Upvotes: 1

Views: 121

Answers (1)

D Stanley
D Stanley

Reputation: 152626

  1. Create a table to map the names and IDs:

    Name    AID
------  ------
abc     111
abc     222
def     222
def     1113

..etc

  2. Use a LEFT join to exclude matches:

    SELECT * FROM table A 
JOIN table B 
  ON A.ID = B.ID
LEFT JOIN Exclusions e
  ON A.name = e.name
     and B.AID = e.AID
WHERE e.name IS NULL   -- not in exclusion table

or a NOT EXISTS:

    SELECT * FROM table A 
    JOIN table B 
      ON A.ID = B.ID
    WHERE NOT EXISTS(
        SELECT null FROM Exclusions e
        WHERE A.name = e.name
         and B.AID = e.AID
        )

Upvotes: 1

Related Questions