Is it possible to share the same filter parameters (WHERE CLAUSE) with two more fields in SQL?

user_14586956 0
Viewed 43

Consider below

SELECT * FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE 
t1.A NOT IN ('111111','222222','33333')
AND 
t2.B NOT IN ('111111','222222','33333')

Is there another way to use the same filter parameters for two different fields?

Something like that

SELECT * FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE (t1.A and t2.B) NOT IN ('111111','222222','33333')

Seems pretty simple, but I couldn't find anything in the docs.

sql-in select postgresql sql
2 Answers

You could use the array intersection operator (&&) to simulate that condition - create an array of the columns and an array of the values to test, and have a where clause that checks there's no intersection between them:

SELECT    * 
FROM      table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE     NOT ARRAY[t1.A, t2.B] && ARRAY['111111', '222222', '33333']
user_24227760

A CTE would be one possibility.

WITH cte
AS
(
SELECT c
       FROM (VALUES ('111111'),
                    ('222222'),
                    ('33333')) v
                               (c)
)
SELECT *
       FROM table1 t1
            LEFT JOIN table2 t2
                      ON t1.id = t2.id
       WHERE t1.a NOT IN (SELECT c
                                 FROM cte)
             AND t2.c NOT IN (SELECT c
                                     FROM cte);
user_96614240
Related