Zsolt Stegena Zsolt Stegena - 7 months ago 12
SQL Question

PostgreSQL selection

I have a table looks like:

user key
x 1
x 2
y 1
z 1


The question is simple. How to find out which one is the user who has not key 2?
The result should be y and z users.

Answer

@jarlh's answer is likely the fastest if you have two tables;
- One with the users
- One with your facts

select "users"."user_id"
from "users"
where not exists (select 1 from tablename t2
                   where t2."user_id" = "users"."user_id"
                     and t2."key" = 2)

That's the structure I would recommend too, having two tables.

For your case, where you only have one table, the following may be a faster alternative; it does not need to join or run a correlated sub-query, but rather scans the whole table just once.

SELECT
    "user"
FROM
    tablename
GROUP BY
    "user"
HAVING
    MAX(CASE WHEN "key" = 2 THEN 1 ELSE 0 END)
Comments