CODEWITHSUNDEEP
sqlsql-server
Virtual Penman
Virtual Penman

Reputation: 163

A reverse IN statement?

I feel like I am either overthinking this or it's not possible but is there a way to do something like a reverse IN statement in SQL?

Instead of saying:

WHERE column_name NOT IN (x, y, z)

I want to have three columns exclude the same value like:

WHERE column1 NOT LIKE 'X' AND column2 NOT LIKE 'X' AND column3 NOT LIKE 'X'

Is it possible to do this more efficiently with less code?

Edit: I am using a string value. Instead of nulls our DB has a space value, ''.

I used the suggested comment and changed to:

WHERE '' NOT IN (column1, column2, column3)

and it worked perfectly

Upvotes: 2

Views: 576

Answers (4)

tgralex
tgralex

Reputation: 814

You can use following technique:

DECLARE @A VARCHAR(10) = 'A', @B VARCHAR(10) = 'A', @C VARCHAR(10) = 'B'
SELECT id, COUNT(*) FROM (VALUES (@A), (@B), (@C)) T (id) GROUP BY Id HAVING COUNT(*) > 1

Now, you could have rewritten your where statement as following:

WHERE NOT EXISTS(SELECT id, COUNT(*) FROM (VALUES (column1), (column2), (column3)) T (id) GROUP BY Id HAVING COUNT(*) > 1)

Not sure if it simplifies things, but if the above WHERE statement is true, that will ensure that all 3 columns have different values.

Upvotes: 0

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

Operator LIKE uses pattern matching, while IN does not.

You can use NOT LIKE or NOT IN but you cannot substitute IN by LIKE or vice versa.

Upvotes: 0

Lajos Arpad
Lajos Arpad

Reputation: 76804

A possibility is to concatenate the columns, like

CONCAT(column1, column2, column3) NOT LIKE '%X%'

another one is to use the suggestion of IronMan and Gordon Linoff, like

'X' not in (column1, column2, column3)

The first approach works in most cases, but not when any of the columns is null (isnull is a remedy for that problem, but makes the code less appealing). The second approach should work in all cases, except when the left operand being part of any of the items in the right operand values (instead of being equal to any of them).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Is it possible to do this more efficiently with less code?

You can shorten the expression to:

where ' ' not in (column_1, column_2, column_3)

But in most databases, this will have little impact on performance. Such a construct will probably not use an index.

I cannot readily think of a way of expressing this that will use an index (in most databases). Obviously, if this is something you often need to do, you could use a function-based index.

Upvotes: 1

Related Questions