In SQL, can I reference multiple columns in a single WHERE clause?-CodePudding

Suppose I've got a table called SAMPLE_TABLE, and its columns include USERCODE1, USERCODE2, ... USERCODE6, and that I need to run a query that excludes any rows where any of these USERCODEs = 25. I know I can write a query like this:

SELECT * FROM SAMPLE_TABLE
WHERE USERCODE1 <> 25 AND USERCODE2 <> 25 AND USERCODE3 <> 25
AND USERCODE4 <> 25 AND USERCODE5 <> 25 AND USERCODE6 <> 25

Is there a way to group all those USERCODE columns together in the WHERE clause without all the ANDs? Something along these lines:

SELECT * FROM SAMPLE_TABLE
WHERE (USERCODE1, USERCODE2, USERCODE3, USERCODE4, USERCODE5, USERCODE6) <> 25

I'm simplifying this -- there are actually 40 USERCODE columns in the real data set, which is why I'm looking for something more concise. Any thoughts? Thanks!

EDIT: Right after I posted this, I came up with something that works, although it's a bit clunky:

SELECT *
FROM SAMPLE_TABLE
WHERE CONCAT_WS('-', SERVICE1, SERVICE2, SERVICE3, SERVICE4, SERVICE5, SERVICE6) NOT LIKE '%%'

CodePudding user response：

You don't mention the specific database so I'll assume it's PostgreSQL. You can use 25 NOT IN (col1, col2, ...) as in:

create table t (a int, b int, c int, d int, e int, f int);

insert ...

select * from t where 25 not in (a, b, c, d, e, f)

Result:

 a   b   c   d   e   f  
 --- --- --- --- --- -- 
 1   2   3   4   5   6  
 11  12  13  14  15  16

See running example at db<>fiddle.

CodePudding user response：

there is no straightforward way to group all the columns together in a single comparison in the way you're describing but you can simplify your query using an INNER JOIN with a subquery to exclude any rows where any of the 40 USERCODE columns have a value of 25