Hello! There are 2 tables, approximate form:

Table 1: "notification":

userId | type ------------- 1 | 1 2 | 2 3 | 991 4 | 991

Table 2 "user":

 vkId | something ------------- 1 | 1111 2 | 2222 3 | 3333 4 | 4444

How to select users from the 2nd table, but only those who are not in table №1 with type = 991?

It seems that something like this should work, but no, it still gives users from table 1 with type = 991.

 SELECT "user"."vkId" FROM "user" as "user" LEFT OUTER JOIN ( SELECT "notification"."userId" FROM "notification" AS "notification" WHERE "notification"."type" = 991 GROUP BY "notification"."userId" LIMIT 100 ) AS "notification" ON "notification"."userId" = "user"."vkId" WHERE "notification"."userId" IS NULL GROUP BY "user"."vkId" LIMIT 100

    3 answers 3

     SELECT "user"."vkId" FROM "user" as "user" LEFT JOIN "notification" ON "notification"."userId"="user"."vkId" AND "notification"."type" = 991 WHERE "notification"."userId" IS NULL GROUP BY "user"."vkId" LIMIT 100

    You can also use NOT EXISTS (in some cases it can be faster, although in others it is slower than LEFT JOIN):

     SELECT "user"."vkId" FROM "user" as "user" WHERE NOT EXISTS( SELECT 1 FROM "notification" WHERE "notification"."userId"="user"."vkId" AND "notification"."type" = 991 ) GROUP BY "user"."vkId" LIMIT 100 
       SELECT "user"."vkId" FROM "user" as "user" LEFT OUTER JOIN "notification" as "notification" ON "notification"."userId" = "user"."vkId" WHERE "user"."vkId" NOT IN ( SELECT "not"."userId" FROM "notification" AS "not" WHERE "not"."type" = 991 GROUP BY "user"."vkId" LIMIT 100

        If I understand the question correctly, it should work in PostgreSQL:

         select vkId from "user" except select userId from "notification" where "type" = 991