CODEWITHSUNDEEP
sqlwhere-clauseexists
markus
markus

Reputation: 1011

SQL query with "not exists" not working

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!

Upvotes: 6

Views: 20225

Answers (2)

Dan Bellandi
Dan Bellandi

Reputation: 546

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.

Upvotes: 3

Robert
Robert

Reputation: 25753

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

Upvotes: 12

Related Questions