如何在单条SQL查询中针对动态question_type与question_sub_type组合执行多连接，获取排除指定问题的结果

Instead of manually writing repetitive UNION clauses for every possible (question_type, question_sub_type) pair, we can use a combination of cross joins and anti-joins to solve this in one clean, scalable query. Here's the breakdown:

Core Approach #

1. Get all unique type/subtype pairs: Pull every distinct (question_type, question_sub_type) combination from the question_exclusion table.
2. Generate full possible combinations: Cross join these pairs with every question ID in the question table—this creates every possible pairing of type/subtype and question ID.
3. Filter out excluded records: Use a left join to match against the exclusion table, then keep only the combinations that don't exist in the exclusion list (these are your unexcluded questions for each pair).

The Query #

SELECT 
    combos.question_type,
    combos.question_sub_type,
    q.question_id
FROM 
    (SELECT DISTINCT question_type, question_sub_type FROM question_exclusion) AS combos
CROSS JOIN 
    question AS q
LEFT JOIN 
    question_exclusion AS qe 
    ON combos.question_type = qe.question_type
    AND combos.question_sub_type <=> qe.question_sub_type  -- Handles NULL subtype matches (MySQL syntax)
    AND q.question_id = qe.question_id
WHERE 
    qe.question_id IS NULL
ORDER BY 
    combos.question_type, combos.question_sub_type, q.question_id;

Key Notes for NULL Handling #

• If you're using PostgreSQL instead of MySQL, replace the <=> operator with IS NOT DISTINCT FROM to properly compare NULL values in question_sub_type. This ensures pairs like (C, NULL) from your sample data are matched correctly.

How It Works With Your Sample Data #

1. The combos subquery extracts the 4 unique pairs: (A,A_1), (A,A_2), (B,B_1), (C,NULL).
2. Cross joining with your 5 question IDs creates 20 total combinations.
3. The left join identifies the 5 excluded pairs (e.g., (A,A_1,42), (C,NULL,2)).
4. Filtering out those excluded pairs leaves exactly the 15 rows from your expected result.

Why This Beats UNION Chains #

• No manual updates: The query automatically adapts when new type/subtype pairs are added to the exclusion table.
• Scalable: Works for any number of pairs without changing the core logic.
• Maintainable: A single query is far easier to read and debug than a long string of repeated UNION blocks.

内容的提问来源于stack exchange，提问作者Serg