The EXISTS and NOT EXISTS defines subquery which return true of false, for each record in the main query, if the subquery return true, the record is kept, otherwise its discarded.

For EXIST, if the subquery's result contains one or more records , the EXIST return true. The NOT EXIST is the same but opposite.

Here is a simple example

 
    SELECT c.CustomerId  
    FROM Customers c  
    WHERE EXISTS(  
        SELECT OrderID FROM Orders o  
        WHERE o.CustomerID = cu.CustomerID)  
 

This select out customers who has at least one order. How it goes, for each customer in the main query, check the subquery, if subquery return at least one result, which means this customer has at least one record in order table, the customer is kept.

Double nested NOT EXISTS

The EXISTS subquery can be nested, and it will become hard to understand when there are multiple subqueries are nested together.

Lets say there are employee who master one or more languages, and there is a language table contains languages the company may need.

 
 
DROP TABLE `languageskill`;
CREATE TABLE `languageskill` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `employee` VARCHAR(20) NOT NULL,
    `language` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`)
 
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
DROP TABLE `languages`;
CREATE TABLE `languages` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `language` VARCHAR(30) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
INSERT INTO languages VALUES ('','English');
INSERT INTO languages VALUES ('','French');
INSERT INTO languages VALUES ('','Spanish');
 
 
INSERT INTO languageskill VALUES ('','Dave','French');
 
INSERT INTO languageskill VALUES ('','John','French');
INSERT INTO languageskill VALUES ('','John','Spanish');
INSERT INTO languageskill VALUES ('','John','English');
 
INSERT INTO languageskill VALUES ('','Jones','Spanish');
INSERT INTO languageskill VALUES ('','Jones','Japanese');
 
INSERT INTO languageskill VALUES ('','Smith','French');
INSERT INTO languageskill VALUES ('','Smith','Spanish');
INSERT INTO languageskill VALUES ('','Smith','English');
 
INSERT INTO languageskill VALUES ('','Wilson','German');
INSERT INTO languageskill VALUES ('','Wilson','English');
INSERT INTO languageskill VALUES ('','Wilson','Chinese');
INSERT INTO languageskill VALUES ('','Wilson','French');
 
 
 

How to find employees who master all the language in languages table?

One way is to use a nested NOT EXISTS subquery, like this:

 
 
SELECT DISTINCT employee FROM languageskill AS ls1 
  WHERE NOT EXISTS (
    SELECT * FROM languages 
      WHERE NOT EXISTS (
        SELECT * FROM languageskill AS ls2 
          WHERE (ls1.employee = ls2.employee) AND (ls2.LANGUAGE = languages.LANGUAGE) ));
 

Expressed in English, the query means, for each employee, if there is not exist such a language in languages table that the employee can not speak, the employee is kept.

If the second NOT EXISTS return true, means the employee can not speak a language in languages table, then if the first NOT EXISTS return true, indicate such a language is not exist. So if there are no languages that the employee can not speak, the employee is kept.

This is the same as when the employee master all the languages in languages table, the employee is kept.