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
Advertisement
Answer
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.