SQL query with "does not exist" does not work

Question

I'm trying to use a simple query, but I'm not going anywhere. The goal is to “learn” how “does not exist” works. I have a simple table with "idUsuario" as the default user id and name.

SELECT * FROM usuario WHERE NOT EXISTS ( SELECT * FROM usuario WHERE u.idUsuario =16 ) 

Here I am trying to get ALL users from a table where the ID is NOT 16. But it just gets all of them. What am I doing wrong?

Thanks in advance!

Answer 1

You must join the result from your query with the result from the subquery, as shown below

  SELECT * FROM usuario u WHERE NOT EXISTS ( SELECT * FROM usuario u1 WHERE u1.idUsuario = u.idUsuario and u1.idUsuario =16 ) 

SQL Fiddle DEMO

Answer 2

Parado's answer is correct. I would add that a single table query does not provide the best demonstration of NOT EXISTS . Usually your NOT EXISTS clause refers to another table.

For example, if you want to query the usuario table where idUsuario was not in another table, you should:

  SELECT * FROM usuario u WHERE NOT EXISTS ( SELECT * FROM usuarioExclude x WHERE x.idUsuario = u.idUsuario ) 

The usuarioExclude table can contain any values ​​that you want to exclude from your results, for example 16 in your example.