多对多关联表查询需求：右表含NULL返回左表全量，否则执行inner join

Absolutely, this is totally doable with some conditional SQL logic! Let's walk through how to make this work based on your table structure and requirements.

First, let's assign clear names to your tables to make the query easier to follow (since you didn't specify formal names):

• security_groups: Holds your security IDs and their associated groups (your first table)
• security_access: The many-to-many join table linking security IDs to function/access IDs (your second table)
• functions: Stores the function details (your third table)

Core Logic Breakdown #

Your requirement boils down to two scenarios for a given security group:

1. If the security_access table has any NULL value in the Access ID column for the group's security ID → return all functions for that group.
2. If there are no NULL Access IDs for the security ID → only return functions that are explicitly linked via an inner join between security_access and functions.

Solution Query (Two Approaches) #

Approach 1: Using EXISTS with Conditional Filtering

This approach keeps everything in a single query with conditional checks:

SELECT DISTINCT
    sg."Security Group",
    f."Function Code"
FROM
    security_groups sg
LEFT JOIN security_access sa 
    ON sg."Security ID" = sa."Security ID"
CROSS JOIN functions f
WHERE
    -- Replace 'Admin' with your target security group
    sg."Security Group" = 'Admin'
    AND (
        -- Scenario 1: NULL exists → include all functions
        EXISTS (
            SELECT 1
            FROM security_access sa_null
            WHERE sa_null."Security ID" = sg."Security ID"
              AND sa_null."Access ID" IS NULL
        )
        -- Scenario 2: No NULLs → only include matched functions
        OR (
            NOT EXISTS (
                SELECT 1
                FROM security_access sa_null
                WHERE sa_null."Security ID" = sg."Security ID"
                  AND sa_null."Access ID" IS NULL
            )
            AND f."Function ID" = sa."Access ID"
        )
    );

Approach 2: Using UNION for Clearer Separation

If you prefer more readable, split logic, this UNION approach works great:

-- Scenario 1: Return all functions when a NULL Access ID exists
SELECT
    sg."Security Group",
    f."Function Code"
FROM
    security_groups sg
CROSS JOIN functions f
WHERE
    sg."Security Group" = 'Admin'
    AND EXISTS (
        SELECT 1
        FROM security_access sa
        WHERE sa."Security ID" = sg."Security ID"
          AND sa."Access ID" IS NULL
    )

UNION

-- Scenario 2: Return only linked functions when no NULLs exist
SELECT
    sg."Security Group",
    f."Function Code"
FROM
    security_groups sg
INNER JOIN security_access sa 
    ON sg."Security ID" = sa."Security ID"
INNER JOIN functions f 
    ON sa."Access ID" = f."Function ID"
WHERE
    sg."Security Group" = 'Admin'
    AND NOT EXISTS (
        SELECT 1
        FROM security_access sa
        WHERE sa."Security ID" = sg."Security ID"
          AND sa."Access ID" IS NULL
    );

How This Works With Your Sample Data #

• For the Admin group (Security ID 1): Since there's a NULL Access ID in security_access, both queries will return both Search and Delete functions.
• For the Basic group (Security ID 2): No NULL Access IDs exist, so only the Search function (linked via Access ID 1) will be returned.

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