Same sub-query used multiple times in a single query

Question

I am running a query that contains same sub-query used multiple times in a WHERE clause.

I have a table having tow fields client_id, buyer_id.

The sub-query returns the list of dates to be excluded from the result.

This is how I am using it.

SELECT
  id, client_id, buyer_id
FROM relation
WHERE
  client_id NOT IN (SELECT <some_id> FROM <some_table> WHERE ...)
  AND buyer_id NOT IN (SELECT <some_ids> FROM <some_table> WHERE ...)

This is working as expected but what bothers me that there are two same sub-queries. I wonder if there is a way that I can use it once and use the result for both places.

Thanks.

Answer 1

You can write this using NOT EXISTS:

SELECT
    id, client_id, buyer_id
FROM relation AS r
WHERE NOT EXISTS
      ( SELECT 1 
        FROM <some_table> 
        WHERE (r.client_id = <some_id> OR r.buyer_id = <some_id>) 
          AND ...
      ) ;