When to use NOT EXISTS in SQL?

Question

As per what I know, EXISTS returns true when a sub query contains atleast a row whereas NOT EXIST returns true if the subquery returns nothing. So for a given subquery either of the two should return true, right? For eg: 1) This returns as to what kind of store is present in one or more cities?

SELECT DISTINCT store_type FROM stores
WHERE EXISTS 
             (SELECT * 
              FROM cities_stores
              WHERE cities_stores.store_type = stores.store_type);

2) And this returns as to what kind of store is present in no cities?

SELECT DISTINCT store_type FROM stores
WHERE NOT EXISTS 
             (SELECT * 
              FROM cities_stores
              WHERE cities_stores.store_type = stores.store_type);

So how can the same sub query give output for both the queries? as one uses EXIST and the other uses NOT EXIST?

I took the examples from http://dev.mysql.com/doc/refman/5.1/en/exists-and-not-exists-subqueries.html

Also how is 2 NOT EXISTS helping here? Isn't this a kind of OR? 3) This returns what kind of store is present in all cities?

SELECT DISTINCT store_type FROM stores s1
WHERE NOT EXISTS (
            SELECT * FROM cities 
            WHERE NOT EXISTS (
                         SELECT * FROM cities_stores
                         WHERE cities_stores.city = cities.city
                         AND cities_stores.store_type = stores.store_type));

Because some `stores` rows have matching rows in `cities_stores` while others have not? — wildplasser, Oct 02 '13 at 14:00
If you do a `SELECT *` instead of 'SELECT DISTINCT store_type` you might get a better feel for what 'EXISTS' and 'NOT EXISTS' are doing. — , Oct 02 '13 at 14:05
`how can the same sub query give output for both the queries` look at the results, they are not the same, so YES you get output for both, but theyre not the same — DrCopyPaste, Oct 02 '13 at 14:10
eg 1 and 2 only have the difference of EXISTS and NOT EXISTS. so how is the sub query returning true for both?? But i guess i understood from the concept of join. — pnk6, Oct 02 '13 at 14:15
In your code fragments the subquery is not connected to the main query (alias s1 in some). So it eiter yields True or False _for all the rows_, and effectively functions as an if clause for the complete outer select statement. — wildplasser, Oct 02 '13 at 14:18
On an unrelated note, if you are using MySQL using the `LEFT JOIN\IS NULL` method rather than `NOT EXISTS` or using an `INNER JOIN` instead of `EXISTS` will perform better. http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/ or http://stackoverflow.com/a/2246793/1048425 — GarethD, Oct 02 '13 at 14:55

score 4 · Accepted Answer · answered Oct 02 '13 at 14:55

Typical usage for EXISTS() clauses is to check where some related row for a given row exists in a related table:

CREATE TABLE clients
        ( client_id INTEGER NOT NULL PRIMARY KEY
        , client_name varchar
        );
CREATE TABLE products
        ( product_id INTEGER NOT NULL PRIMARY KEY
        , product_name varchar
        );
CREATE TABLE orders
        ( client_id INTEGER NOT NULL REFERENCES clients(client_id)
        , product_id INTEGER NOT NULL REFERENCES products(product_id)
        , quantity INTEGER NOT NULL DEFAULT 1
        , order_date DATE
        , PRIMARY KEY (client_id,product_id)
        );
INSERT INTO clients(client_id, client_name) VALUES (1, 'Alice' ), (2, 'Bob' ), (3, 'Charly' ), (4, 'Diana' );
INSERT INTO products(product_id, product_name) VALUES (1, 'Apple' ), (2, 'Banana' ), (3, 'Citrus' );
INSERT INTO orders(client_id,product_id,order_date) VALUES (1,2, '2013-9-8'),(2,1, '2013-9-11'),(3,2, '2013-10-1');

-- Find clients who ordered something
SELECT * FROM clients cl
WHERE EXISTS (
        SELECT * FROM orders oo
        WHERE oo.client_id = cl.client_id
        )
        ;

-- Find clients who never ordered anything
SELECT * FROM clients cl
WHERE NOT EXISTS (
        SELECT * FROM orders oo
        WHERE oo.client_id = cl.client_id
        )
        ;

-- Find products that were never ordered
SELECT * FROM products pr
WHERE NOT EXISTS (
        SELECT * FROM orders oo
        WHERE oo.product_id = pr.product_id
        )
        ;

My Stack Overfloweth · Answer 2 · 2013-10-02T14:41:55.317

2

To add to this, it would help performance when using NOT EXISTS by making sure you're checking the columns in a way that does not negate the indexes. I think this applies to larger datasets but is still good to know. Jayachandran explains it very well here: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/582544fb-beda-46c0-befd-4b28b5c2cdee/select-not-exists-very-slow

edited Oct 02 '13 at 14:41

answered Oct 02 '13 at 14:17

My Stack Overfloweth

4,729
4
25
42

score 1 · Answer 3 · answered Oct 02 '13 at 15:07

An example where Where not exists is incredibly useful is when making inserts. If you wish to prevent adding duplicate rows based on some condition, it is helpful to use this.

For example, when creating reference data tables, I also have IDENTITY_INSERT off to keep IDs consistent across databases. When inserting new reference data, I do it like so:

INSERT INTO ref_table
(ID, ReferenceData)
SELECT 425, 'foo' where not exists (select 1 from ref_table where ID = 425) UNION ALL
SELECT 426, 'bar' where not exists (select 1 from ref_table where ID = 426) UNION ALL
...
SELECT 532, 'biz' where not exists (select 1 from ref_table where ID = 532)

of course I always make sure that my inserts will be consistent, but i add this clause for safe measure.

score 0 · Answer 4 · answered Oct 02 '13 at 14:05

First you want the store types that have a match in the inner query (join), in the second you want the store types that DO NOT have a match from the inner query!!!

When using a EXISTS/NOT EXISTS clause you always join the inner and outer queries, then you just choose if you want the results that have a match or that don't have a match.

When to use NOT EXISTS in SQL?

4 Answers4