Select rows from SQL where column doesn't match something in a string array?

Question

Let's say I have a table, Product, with a column called ProductName, with values like:

Lawnmower
Weedwacker
Backhoe
Gas Can
Batmobile

Now, I have a list, in Notepad, of products that should be excluded from the result set, i.e.:

Lawnmower
Weedwacker
Batmobile

In my real-life problem, there are tens of thousands of records, and thousands of exclusions. In SQL Studio Manager, how can I construct a query similar to the following pseudocode that will just return Backhoe and Gas Can as results?:

declare @excludedProductNames varchar(MAX) =
'Lawnmower
Weedwacker
Batmobile'

SELECT ProductName FROM Product
WHERE ProductName isn't in the list of @excludedProductNames

This is just a one-time report, so I don't care about performance at all.

Answer 1

Create a temp table, load all your exclusions there and select all rows that do not exist in the temp table.

-- create temp table #exclusions
select ProductName into #exclusions
from Product
where 1 = 2

# run a bunch of inserts
insert into #exclusions (ProductName) values ('LawnMower')
-- as many as needed...

# run your select
select * from Product
where ProductName not in (select Product from #exclusions)

drop table #exclusions

As an alternative to running a ton of inserts, use bcp to upload a csv file containing the ProductNames into a non temp table.

Answer 2

First thing is getting those words into SSMS - you can construct a derived table using UNION ALL:

SELECT 'Lawnmower' AS word
UNION ALL
SELECT 'Weedwacker'
UNION ALL
SELECT 'Batmobile'

This will return a table with a single column, named "word":

word
--------
Lawnmower
Weedwacker
Batmobile

Caveat

You'll need to escape any single quotes in your data. IE: O'Brian needs to be changed to O''Brian--just double up the single quote to escape it.

Now, to the real query...

Using NOT IN

Some databases limit the number of clauses in the IN, somewhere in the thousands IIRC so NOT EXISTS or LEFT JOIN/IS NULL might be better alternatives.

SELECT p.*
  FROM PRODUCT p
 WHERE p.productname NOT IN (SELECT 'Lawnmower' AS word
                             UNION ALL
                             SELECT 'Weedwacker'
                             UNION ALL
                             SELECT 'Batmobile'
                             ...)

Using NOT EXISTS

SELECT p.*
  FROM PRODUCT p
 WHERE NOT EXISTS (SELECT NULL
                     FROM (SELECT 'Lawnmower' AS word
                           UNION ALL
                           SELECT 'Weedwacker'
                           UNION ALL
                           SELECT 'Batmobile'
                           ...) x
                   WHERE x.word = p.productname)

Using LEFT JOIN/IS NULL

   SELECT p.*
     FROM PRODUCT p
LEFT JOIN (SELECT 'Lawnmower' AS word
           UNION ALL
           SELECT 'Weedwacker'
           UNION ALL
           SELECT 'Batmobile'
           ...) x ON x.word = p.productname
    WHERE x.word IS NULL

Which is The Most Efficient/Fastest?

If the columns compared are not nullable, NOT IN or NOT EXIST are the best choice.

Answer 3

i think you're best to use some text editor tricks to accomplish this. replace newlines with ', ' for example, and you can easily go for a select * from product where ProductName not in ('...', '...') query.