Is there a single SQL (or its variations) function to check not equals for multiple columns at once?

Question

Just as I can check if a column does not equal one of the strings given in a set.

SELECT * FROM table1 WHERE column1 NOT IN ('string1','string2','string3');

Is there a single function that I can make sure that multiple columns does not equal a single string? Maybe like this.

SELECT * FROM table1 WHERE EACH(column1,column2,column3) <> 'string1';

Such that it gives the same effect as:

SELECT * FROM table1 WHERE column1 <> 'string1' 
                       AND column2 <> 'string1' 
                       AND column3 <> 'string1';

If not, what's the most concise way to do so?

Answer 1

I believe you can just reverse the columns and constants in your first example:

SELECT * FROM table1 WHERE 'string1' NOT IN (column1, column2, column3);

This assumes you are using SQL Server.

UPDATE:

A few people have pointed out potential null comparison problems (even though your desired query would have the same potential problem). This could be worked around by using COALESCE in the following way:

SELECT * FROM table1 WHERE 'string1' NOT IN (
     COALESCE(column1,'NA'), 
     COALESCE(column2,'NA'), 
     COALESCE(column3,'NA')
);

You should replace 'NA' with a value that will not match whatever 'string1' is. If you do not allow nulls for columns 1,2 and 3 this is not even an issue.

Answer 2

No, there is no standard SQL way to do this. Barring any special constraints on what the string fields contain there's no more concise way to do it than you've already hit upon (col1 <> 'String1' AND col2 <> 'String2').

Additionally, this kind of requirement is often an indication that you have a flaw in your database design and that you're storing the same information in several different columns. If that is true in your case then consider refactoring if possible into a separate table where each column becomes its own row.

Answer 3

The most concise way to do this is

SELECT * FROM table1 WHERE column1 <> 'string1' 
                       AND column2 <> 'string1' 
                       AND column3 <> 'string1';

Yes, I cut & pasted that from your original question. :-)

I'm more concerned why you're wanting to compare against all three columns. It sounds like you might have a table that needs normalization. What are the actual columns of column1, column2 and column3. Are they something like phone1, phone2, and phone3? Perhaps those three columns should actually be in a subtable.