Various IN and NOT IN results in SQL

Question

I am trying to make two queries, in my opinion, it should give me the same result, but not

SELECT count(1)
FROM users
WHERE users.id NOT IN (
    SELECT user_id as id
    FROM users_roles as ur
    WHERE 
        ur.role_id = 10
        OR ur.role_id = 12
    )

i has the result:

 count 
-------
     0
(1 row)

and second, first change the value NOT INto INand wrap it on the outerNOT IN

SELECT count(1)
FROM users
WHERE id NOT IN (
    SELECT users.id
    FROM users
    WHERE users.id IN (
        SELECT user_id as id
        FROM users_roles as ur
        WHERE 
            ur.role_id = 10
            OR ur.role_id = 12
        )
)

with the result

  count  
---------
 3150136
(1 row)

what is wrong with the first request?

more details for the second request:

SELECT count(1)
FROM users
WHERE users.id IN (
    SELECT user_id as id
    FROM users_roles as ur
    WHERE 
        ur.role_id = 10
        OR ur.role_id = 12
    )

 count 
-------
 40320
(1 row)

and

select count(1) from users;
  count  
---------
 3190466

change user request:

database=# \d users_roles
                          Table "public.users_roles"
  Column  |  Type   |                        Modifiers                         
----------+---------+----------------------------------------------------------
 user_id  | integer | 
 role_id  | integer | 
 track_id | integer | 
 id       | integer | not null default nextval('users_roles_id_seq'::regclass)
Indexes:
    "users_roles_pkey" PRIMARY KEY, btree (id)
    "uniq_users_roles" UNIQUE CONSTRAINT, btree (user_id, role_id)
    "uq_users_roles_role_track" UNIQUE CONSTRAINT, btree (role_id, track_id)
Foreign-key constraints:
    "fk_roles_track_id" FOREIGN KEY (track_id) REFERENCES tracks(id)
    "fk_users_roles_roles" FOREIGN KEY (role_id) REFERENCES roles(id)
    "fk_users_roles_users" FOREIGN KEY (user_id) REFERENCES users(id)

and select

SELECT Count(user_id) as totalusers FROM users_roles as ur WHERE ur.role_id = 10 OR ur.role_id = 12;
     totalusers 
------------
      40320

PS

database=# select version();
                                                   version                                                    
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.4.14 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit

Answer 1

This may be because the table user_rolesmay be NULL user_idwhen role_id= 10or role_id= 12.

The first query looks something like this:

SELECT COUNT(1)
FROM users
WHERE users.id NOT IN (id1, id2, id3, NULL, id4, NULL, id5,...)

users.id , id , .

NULL, , NOT IN users.id [users.id < > id1 AND users.id < > id2 AND... users.id < > NULL users.id < > id5], , , SQL , users.id < > NULL , , FALSE , users.id . users.id users, 0 .

FYI: id= NULL = > UNKNOWN, id < > NULL = > UNKNOWN, UNKNOWN TRUE, FALSE ( not TRUE, , TRUE), NULL "" - .

, NULL , :

SELECT count(1)
FROM users
WHERE users.id NOT IN (
SELECT user_id as id
FROM users_roles as ur
WHERE 
    (ur.role_id = 10 OR ur.role_id = 12) AND ur.user_id IS NOT NULL
)

NOT IN.

IN, -NULL users.id, , , 40 320 .