sql - select composite primary key with condition to one of the primary key

Question

Here is a table with id and permission as composite primary key, which means one can have some permissions.

id    permission
1     'A'
1     'B'
2     'A'
3     'B'

Now I want to select all ids that fit some conditions. For example:

1. id WITH permission 'A' AND 'B'.
2. id WITH permission 'A' AND WITHOUT permission 'B'.

I've come up with a kind of sql which uses exists clause.

For 1:

SELECT DISTINCT t0.id FROM t t0
WHERE
EXISTS (SELECT 1 FROM t t1 WHERE t1.id = t0.id AND t1.permission = 'A')
AND EXISTS (SELECT 1 FROM t t1 WHERE t1.id = t0.id AND t1.permission = 'B');

For 2:

SELECT DISTINCT t0.id FROM t t0
WHERE
EXISTS (SELECT 1 FROM t t1 WHERE t1.id = t0.id AND t1.permission = 'A')
AND NOT EXISTS (SELECT 1 FROM t t1 WHERE t1.id = t0.id AND t1.permission = 'B');

It works, but for large tables, it is slow because it traverses the table multiple times for each record. So I wonder if there is a faster and clearer way to achieve my goal.

Answer 1

You can use COUNT(DISTINCT) for case 1. Here is the sample:

SELECT id
FROM permissions
WHERE permission IN ('A', 'B')
GROUP BY id
HAVING COUNT(DISTINCT permission) = 2;

And you can use NOT IN for case 2. Here is the sample:

SELECT DISTINCT id
FROM permissions
WHERE permission = 'A'
AND id NOT IN (
    SELECT id
    FROM permissions
    WHERE permission = 'B'
);

It should be faster than your initial query that querying exists and/or non exists twice. Here is the fiddle link.