Help me understand this particular use of nested SELECT statements

Question

From this site:

Tables:

CREATE TABLE PilotSkills
(pilot_name CHAR(15) NOT NULL,
plane_name CHAR(15) NOT NULL,
PRIMARY KEY (pilot_name, plane_name));

CREATE TABLE Hangar
(plane_name CHAR(15) NOT NULL PRIMARY KEY);

Query:

SELECT DISTINCT pilot_name
  FROM PilotSkills AS PS1
  WHERE NOT EXISTS
       (SELECT *
          FROM Hangar
         WHERE NOT EXISTS
               (SELECT *
                  FROM PilotSkills AS PS2
                 WHERE (PS1.pilot_name = PS2.pilot_name)
                   AND (PS2.plane_name = Hangar.plane_name)));

I understand the problem it's used for (set division), including the analogy that describes it as "There ain't no planes in this hangar that I can't fly!". What I don't understand is exactly what's at work here, and how it comes together to do what it says its doing.

Having trouble stating with specifics my difficulty at the moment...

---

Edit: Let me just first ask what something like this does, exactly:

SELECT DISTINCT pilot_name
  FROM PilotSkills
  WHERE NOT EXISTS
       (SELECT *
          FROM Hangar)

I think I'm missing some fundamental understanding here...

Edit: Irrelevant, and it wouldn't be a meaningful without the third nested SELECT, right?

Answer 1

What we want is a distinct list of pilots that can fly every plane in the hanger. For that to be true, for a given pilot, there cannot exist a plane they cannot fly. So, we want to get a list of all planes for each pilot and see if there is one they cannot fly. If there is one (the pilot cannot fly) we remove them from the list. Whomever is left, can fly all planes in the hanger.

Said more formally, find a distinct list of pilot names such that for a given pilot, there does not exist a plane in the set of planes (Hanger) such that the plane does not exist in the set of the given pilot's skills.

"find a distinct list of pilot names..."

Select Distinct pilot_name
From PilotSkills As PS1
...

"...such that for a given pilot, there does not exist a plane in the set of planes (Hanger)..."

Select Distinct pilot_name
From PilotSkills As PS1
Where Not Exists    (
                    Select 1
                    From Hanger

"...such that the plane does not exist in the set of the given pilot's skills."

Select Distinct pilot_name
From PilotSkills As PS1
Where Not Exists    (
                    Select 1
                    From Hanger As H
                    Where Not Exists    (
                                        Select 1
                                        From PilotSkills As PS2
                                        Where PS2.pilot_name = PS1.pilot_name
                                            And PS2.plane_name = H.plane_name
                                        )
                    )

Answer 2

As a minor comment initially, Select * is overkill in this situation. You should select a single column, or a couple of columns, but pulling all columns should be avoided, especially in sub queries where they're only used during the query and not returned in the final result set. That said, to try to break down the work flow:

1. Select Pilot_Name From PilotSkills - We're interested in pilot names eventually.
2. Where Not Exists (Select * From Hangar) - We're only going to retrieve pilots if there is not a relevant entry for them in the Hangar table.
3. Where Not Exists (Select * From PilotSkills) - We're only going to retrieve Hangars that don't have a pilot from the outer query.

Describing it as a double negative (from the other answer) is a great way to understand it. It can probably be achieved more directly.