Q:

When to use NOT EXISTS in SQL?

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));

A:

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
        )
        ;

