Match multiple components of a subquery CTE in PostgreSQL I've got an app that grants access to users in varying different ways, and I'm attempting to write a query that determines access in all the different ways, but is readable, by breaking the access out into CTEs.

This is the basic pattern I'm talking about:

WITH user_allowed_data1 AS (
    SELECT u.id user_id, c.id content_id FROM …
),
user_allowed_data2 AS (
    SELECT u.id user_id, c.id content_id FROM …
)
SELECT u.name, c.name FROM user_table
LEFT OUTER JOIN content_table ON 1=1
WHERE u.id, c.id in (SELECT user_id, content_id FROM user_allowed_data_1)
OR u.id, c.id in (SELECT user_id, content_id FROM user_allowed_data_2)


So, two questions:


Is there a better/faster/cleaner way to think about this query?
Is there a way to match multiple fields pulled out of a subquery in a where clause like that?
There are several ways to rewrite the query, even with keeping the CTEs. But please consider that CTEs in Postgres are pre-evaluated, so they might cause efficiency problems.


first, the LEFT join is equivalent to an inner JOIN, as you have a column from the right-side table in the following WHERE clause.
second, the FROM user_table LEFT OUTER JOIN content_table ON 1=1 is awkward. You could use the simple FROM user_table CROSS JOIN content_table. Not any performance gain, just more simple. I think I would prefer even the (frawned upon) FROM user_table, content_table from the LEFT ... ON 1=1.
after doing that, it's easy to see that FROM a CROSS JOIN b WHERE &lt;x&gt; can be replace with FROM a JOIN b ON &lt;x&gt;
the IN (SELECT user_id, content_id FROM user_allowed_data_1) can be replaced with the more elegant IN (TABLE user_allowed_data_1)


Now, to get rid of the OR, one way would be to combine the two ctes in one:

-- query 1 --
WITH user_allowed_data AS (
    SELECT u.id AS user_id, c.id AS content_id FROM …
  UNION DISTINCT
    SELECT u.id AS user_id, c.id AS content_id FROM …
)
SELECT u.name, c.name 
FROM user_table AS u
  JOIN content_table AS c 
    ON (u.id, c.id) IN (TABLE user_allowed_data) ;


But since we did that, we can just use joins in the main query and not the fancy ON ... IN (...):

-- query 2 --
WITH user_allowed_data AS (
    SELECT u.id AS user_id, c.id AS content_id FROM …
  UNION DISTINCT
    SELECT u.id AS user_id, c.id AS content_id FROM …
)
SELECT u.name, c.name 
FROM user_table AS u
  JOIN user_allowed_data AS ad
    ON ad.user_id = u.id
  JOIN content_table AS c 
    ON ad.content_id = c.id ;


Another way - to get rid of the OR - would be to use UNION in the main query instead:

-- query 3 --
WITH user_allowed_data1 AS (
    SELECT u.id user_id, c.id content_id FROM …
),
user_allowed_data2 AS (
    SELECT u.id user_id, c.id content_id FROM …
)
SELECT u.name, c.name 
FROM user_table AS u
  JOIN user_allowed_data1 AS ad
    ON ad.user_id = u.id
  JOIN content_table AS c 
    ON ad.content_id = c.id 
UNION DISTINCT
SELECT u.name, c.name 
FROM user_table AS u
  JOIN user_allowed_data2 AS ad
    ON ad.user_id = u.id
  JOIN content_table AS c 
    ON ad.content_id = c.id ;


Any of the above of course can be written without ctes - which I stress again - can be a barrier to efficiency. So, your original query, slightly altered and without CTEs (keeping the OR):

-- query 1b --
SELECT u.name, c.name 
FROM user_table AS u
  JOIN content_table AS c 
    ON (u.id, c.id) IN (SELECT u.id AS user_id, c.id AS content_id FROM …)
    OR (u.id, c.id) IN (SELECT u.id AS user_id, c.id AS content_id FROM …) ;


The OR variants can also be expressed by replacing the IN with a correlated EXISTS subquery:

-- query 1c --
SELECT uu.name, cc.name 
FROM user_table AS uu
  JOIN content_table AS cc 
    ON EXISTS (SELECT u.id AS user_id, c.id AS content_id FROM …
               WHERE (u.id, c.id) = (uu.id, cc.id) )
    OR EXISTS (SELECT u.id AS user_id, c.id AS content_id FROM …
               WHERE (u.id, c.id) = (uu.id, cc.id) ) ;