Join a table with itself I have a big table (which i created from 3 small tables) which looks like this

Acquisition | device | user_id | user_name
------------|--------|---------|----------
null        |   1    |    1    |   John
3           |   2    |    1    |   John
4           |   4    |    3    |   Mary
null        |   5    |    4    |   Snow


I want to know wich user have not made any  acquisition yet, for this example (4, Snow) would be the answer, as John has made the Acquisition 3.

I made this query

select user_id, user_name 
from adquisicion_dispositivo
where acquisition is null and
user_id not in
(select user_id from adquisicion_dispositivo where
acquisition is not null);


But it takes too long (half hour and going ~1 million rows).
This table does not have any id's.

this is the schema of the table

  Columna      |          Tipo          | Modificadores
-------------------+------------------------+---------------
id_adquisicion    | integer                |
id_articulo       | integer                |
id_dispositivo    | integer                |
fecha_adquisicion | date                   |
id_usuario        | integer                |
version_android   | character varying(100) |
nombre_usuario    | character varying(100) |
fecha_nacimiento  | date                   |
sexo              | character varying(20)  |
On large tables you may be able to speed it up.

SELECT user_id, user_name 
FROM adquisicion_dispositivo AS a1
WHERE acquisition IS NULL
AND NOT EXISTS (
  SELECT 1
  FROM adquisicion_dispositivo AS a2
  WHERE acquisition IS NOT NULL
  AND a1.user_id = a2.user_id
);


You need to show your table with \d adquisicion_dispositivo. You may need an index, @joanolo's suggestion is good switch the order though I would switch the order,

CREATE INDEX ON adquisicion_dispositivo (user_id, acquisition);