如何在单个查询中使用不同参数执行多个联接

我有两个表，问题（question_id）和question_exclusion（question_type，question_sub_type，question_id）

如果我指定 Question_type 和 Question_sub_type 我就可以做到。

SELECT *
FROM question AS t1
         LEFT JOIN (SELECT t.question_id
                    FROM question_exclusion as t
                    WHERE t.question_type = 'A'
                      AND t.question_sub_type = 'A_1') AS t2
                   ON t1.question_id = t2.question_id
WHERE t2.question_id is null;

但我想要实现的是在单个查询中获取带有 questions_ids 的所有问题，以查找所有可能的问题question_type and questions_sub_type

question_type and questions_sub_type是动态参数，在查询执行之前我不知道确切的值

更新1：

实际数据：

table: question

question_id|
42
10
2
36
49

table: question_exclusion

question_type|question_sub_type|question_id|
A            | A_1             | 42
A            | A_1             | 10
A            | A_2             | 10
B            | B_1             | 36 
C            | null            | 2

预期结果：

question_type|question_sub_type|question_id
A            | A_1             | 2
A            | A_1             | 36
A            | A_1             | 49
A            | A_2             | 42
A            | A_2             | 2
A            | A_2             | 36
A            | A_2             | 49
B            | B_1             | 42
B            | B_1             | 10
B            | B_1             | 2
B            | B_1             | 49
C            | null            | 42
C            | null            | 10
C            | null            | 36
C            | null            | 49

它就像每个类型和子类型组合的列表列表 考虑排除表

例如：

type=A, sub_type=A_1 -> (select * from questions) - (select * from question_exclusion where type='A' and sub_type='A_1')
+
type=A, sub_type=A_2 -> (select * from questions) - (select * from question_exclusion where type='A' and sub_type='A_2')
+
type=B, sub_type=B_1 -> (select * from questions) - (select * from question_exclusion where type='B' and sub_type='B_2')

当然，我可以查询所有不同的（type，sub_type）并通过与 union 组合进行另一个查询

SELECT *
FROM question AS t1
         LEFT JOIN (SELECT t.question_id
                    FROM question_exclusion as t
                    WHERE t.question_type = 'A'
                      AND t.question_sub_type = 'A_1') AS t2
                   ON t1.question_id = t2.question_id
WHERE t2.question_id is null
UNION
SELECT *
FROM question AS t1
         LEFT JOIN (SELECT t.question_id
                    FROM question_exclusion as t
                    WHERE t.question_type = 'B'
                      AND t.question_sub_type = 'B_1') AS t2
                   ON t1.question_id = t2.question_id
WHERE t2.question_id is null
...
...
N times for all type and sub_type

我正在寻找另一种在单个查询中执行此操作的可靠方法

\i tmp.sql

create table question
        (question_id integer not null primary key)
        ;
INSERT INTO question(question_id) VALUES
( 42) , ( 10) , ( 2) , ( 36) , ( 49) ;

create table question_exclusion
        ( question_type text
        , question_sub_type text
        , question_id integer REFERENCES question( question_id)
        );

INSERT INTO question_exclusion(question_type, question_sub_type, question_id) VALUES
 ('A' , 'A_1' , 42 ) , ('A' , 'A_2' , 10 ) , ('B' , 'B_1' , 36  ) , ('C' , null  , 2 ) ;

WITH types AS (
        select distinct question_type, question_sub_type
        FROM question_exclusion
        )
SELECT t.question_type, t.question_sub_type, q.question_id
FROM question q
JOIN types t ON NOT EXISTS (
        SELECT * FROM question_exclusion x
        WHERE 1=1
        AND x.question_id = q.question_id
        AND x.question_type = t.question_type
        AND x.question_sub_type = t.question_sub_type
        )
ORDER BY t.question_type, t.question_sub_type
        ;

修改的：

WITH types AS (
        select distinct question_type, question_sub_type
        FROM question_exclusion
        )
SELECT t.question_type, t.question_sub_type, q.question_id
FROM question q
CROSS JOIN types t
WHERE NOT EXISTS (
        SELECT * FROM question_exclusion x
        WHERE 1=1
        AND x.question_id = q.question_id
        AND x.question_type = t.question_type
        AND x.question_sub_type = t.question_sub_type
        )
ORDER BY t.question_type, t.question_sub_type
        ;

与以下没有什么不同

WITH types AS (
        select distinct question_type, question_sub_type
        FROM question_exclusion
        )
SELECT t.question_type, t.question_sub_type, q.question_id
FROM question q
CROSS JOIN types t
WHERE NOT EXISTS (
        SELECT * FROM question_exclusion x
        WHERE 1=1
        AND x.question_id = q.question_id
        AND (x.question_type, x.question_sub_type) IS NOT DISTINCT FROM
            (t.question_type, t.question_sub_type)
        )
ORDER BY t.question_type, t.question_sub_type
        ;