SQL query with "not exists" not working

Question

I'm trying to use a simple query but I'm not getting anywhere. The objective is to "learn" how "not exists" works. I have a simple table with "idUsuario" as default ID for users and a name.

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

Here i'm trying to get ALL the users from the table where the ID IS NOT 16. But it just get all of them.. What am I doing wrong?

Thanks in advance!

Solution 1

You should join result from your query with result from the subquery as below

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

SQL Fiddle DEMO

Solution 2

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

For example, if you wanted to query the usuario table where the idUsuario value was not present in another table you would do:

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

The usuarioExclude table could have any value(s) you wanted excluded from your results, such as 16 in your example.

Comments

[markus]

Tried that, got no results. :(

[markus]

Worked perfectly! Thanks!