Check For Not In Table Values (16 Different Ways And Counting)

How many times did you see a question like this:
how do I select all rows from a table where the column value is not 'ValueA' and not 'ValueB'
Well I decided to figure out how many different ways there are to do such a query
I came up with 16 different ways so far, some of them will cause a table scan, some of them will cause a index seek instead
You can test them out for yourself to see which ones perform the best

CREATE TABLE #TestTable (
Objects VARCHAR(20) PRIMARY KEY)

INSERT INTO #TestTable
VALUES ('boat')

INSERT INTO #TestTable
VALUES('ship')

INSERT INTO #TestTable
VALUES('car')

INSERT INTO #TestTable
VALUES('bus')

INSERT INTO #TestTable
VALUES ('airplane')


-- #1 <>
SELECT *,1
FROM #TestTable
WHERE Objects <> 'boat'
AND Objects <> 'ship'

-- #2 !=
SELECT *,2
FROM #TestTable
WHERE Objects != 'boat'
AND Objects != 'ship'


-- #3 NOT
SELECT *,3
FROM #TestTable
WHERE NOT (Objects = 'boat'
OR Objects = 'ship')


-- #4 NOT IN
SELECT *,4
FROM #TestTable
WHERE Objects NOT IN ('boat',
'ship')


-- #5 ALL
SELECT *,5
FROM #TestTable
WHERE Objects <> ALL (SELECT 'boat'
UNION ALL
SELECT 'ship')


-- #6 ANY
SELECT *,6
FROM #TestTable
WHERE NOT Objects = ANY (SELECT 'boat'
UNION ALL
SELECT 'ship')


-- #7 SOME
SELECT *,7
FROM #TestTable
WHERE NOT Objects = SOME (SELECT 'boat'
UNION ALL
SELECT 'ship')


-- #8 NOT IN with subquery
SELECT *,8
FROM #TestTable
WHERE Objects NOT IN (SELECT 'boat'
UNION ALL
SELECT 'ship')


-- #9 NOT EXISTS
SELECT *,9
FROM #TestTable T
WHERE NOT EXISTS (SELECT *
FROM (SELECT 'boat' AS Objects
UNION ALL
SELECT 'ship') F
WHERE F.Objects = T.Objects)

-- #10 LEFT OUTER JOIN
SELECT T.*,10
FROM #TestTable T
LEFT OUTER JOIN (SELECT 'boat' AS Objects
UNION ALL
SELECT 'ship') F
ON T.Objects = F.Objects
WHERE F.Objects IS NULL


-- #11 CHARINDEX
SELECT *,11
FROM #TestTable
WHERE CHARINDEX('-' + Objects + '-','-boat-ship-') = 0


-- #12 RIGHT OUTER JOIN
SELECT T.*,12
FROM (SELECT 'boat' AS Objects
UNION ALL
SELECT 'ship') F
RIGHT OUTER JOIN #TestTable T
ON T.Objects = F.Objects
WHERE F.Objects IS NULL
AND T.Objects IS NOT NULL


-- #13 FULL OUTER JOIN
SELECT T.*,13
FROM (SELECT 'boat' AS Objects
UNION ALL
SELECT 'ship') F
FULL OUTER JOIN #TestTable T
ON T.Objects = F.Objects
WHERE F.Objects IS NULL


-- #14 PATINDEX
SELECT *,14
FROM #TestTable
WHERE PATINDEX('%-%' + Objects + '%-%','-boat-ship-') = 0


-- #15 PARSENAME
SELECT *,15
FROM #TestTable
WHERE Objects <> PARSENAME('boat.ship',1)
AND Objects <> PARSENAME('boat.ship',2)


-- #16 REVERSE and NOT IN
SELECT *,16
FROM #TestTable
WHERE REVERSE(Objects) NOT IN ('taob','pihs')

-- And here is another one that BugsBunny suggested in a comment
-- #17 NOT LIKE
SELECT *,17
FROM #TestTable
WHERE ',' + 'boat,ship' + ',' NOT LIKE '%,' + Objects + ',%'


DROP TABLE #TestTable

So that's it, if you know of another way, leave me a comment or email me and I will add it to this list