Beginning
This technique is based on SQL Puzzle and Answers # 21 AIRPLANES AND PILOTS
It is better with 『SQL Puzzle and Answers』 to read this article.
Because it says the idea of algorithm and
You can check what each quiz is intended for ・・・
In this article ・・・
I would like to explain in detail about 『Relational Division』
In my case, I didn't understand 『Relational Division』 quickly
It took time to understand the idea of 『Relational Division』
Maybe I still need to learn more ・・・
It is an unique idea of SQL and
You must understand this idea to learn SQL
Let's start !!
There are two tables, the first one is planes data in the Hangar
the other one is planes data that pilots can fly
We want to find pilots who can fly all planes in the Hangar.
We are going to find pilots using the idea of 『Relational Division』
I have checked that it works with PostgreSQL
tables and data
CREATE TABLE PilotSkills (
pilot CHAR(15) NOT NULL,
plane CHAR(15) NOT NULL,
PRIMARY KEY (pilot, plane)
);
CREATE TABLE Hangar (
plane CHAR(15) PRIMARY KEY
);
INSERT INTO PilotSkills
VALUES ('Celko', 'Piper Cub'),
('Higgins', 'B-52 Bomber'),
('Higgins', 'F-14 Fighter'),
('Higgins', 'Piper Cub'),
('Jones', 'B-52 Bomber'),
('Jones', 'F-14 Bomber'),
('Smith', 'B-1 Bomber'),
('Smith', 'B-52 Bomber'),
('Smith', 'F-14 Fighter'),
('Wilson', 'B-1 Bomber'),
('Wilson', 'B-52 Bomber'),
('Wilson', 'F-14 Fighter'),
('Wilson', 'F-17 Fighter');
INSERT INTO Hangar
VALUES ('B-1 Bomber'),
('B-52 Bomber'),
('F-14 Fighter');
▼ Output
There are three planes in Hangar
There are planes name that five pilots can fly in the PilotSkills table
We find the pilots who can fly all planes in the Hangar by SQL
Planes lists that pilot can fly
SELECT PS1.*,
(SELECT array_agg(plane) FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot)
FROM PilotSkills AS PS1
First・・・, making a list of planes that each pilot can fly
Self join PilotSkills table with the same pilot name
⇒ WHERE PS1.pilot = PS2.pilot
taking out planes from PiloSkills PS2 table that each pilot can fly
▼ Output
Planes lists that pilot can fly in the Hangar
SELECT PS1.*,
(SELECT array_agg(plane) FROM Hangar
WHERE EXISTS (
SELECT * FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot AND PS2.plane = Hangar.plane
)
)
FROM PilotSkills AS PS1
▼ Output
image of table data flow
check 『Planes lists that pilot can fly』 for the square part of the blue dotted line
now we see the SQL that can take out the pilots who can fly planes in the Hangar
Smith and Wilson can fly the all planes in the Hangar
the next is ・・・
We are going to find the pilots who can not fly in the Hangar
Planes lists that pilot can not fly in the Hangar
SELECT PS1.*,
(SELECT array_agg(plane) FROM Hangar
WHERE NOT EXISTS ( -- change to NOT EXISTS
SELECT * FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot AND PS2.plane = Hangar.plane
)
)
FROM PilotSkills AS PS1
▼ Output
change 『EXISTS』 to 『NOT EXISTS』
Celko, Higgins, Jones can not fly some of planes in the Hangar・・・
Smith and Wilson have no planes they can not fly in the Hangar
⇒ this means they can fly all planes in the Hangar
⇒ taking out these pilots
use subquery in WHERE clause
SELECT PS1.*,
(SELECT array_agg(plane) FROM Hangar
WHERE NOT EXISTS (
SELECT * FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot AND PS2.plane = Hangar.plane
)
)
FROM PilotSkills AS PS1
WHERE NOT EXISTS (
SELECT * FROM Hangar
WHERE NOT EXISTS (
SELECT * FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot AND PS2.plane = Hangar.plane
)
)
▼ Output
add WHERE condition, then use NOT EXISTS(・・・)
⇒ NOT EXISTS(・・・)
⇒ (・・・) is 『planes that pilot can not fly in the Hangar』
writing an affirmative sentence using double negation
planes that pilot can not fly ・・・does not exists in the Hangar
⇒ NOT EXISTS ⇒ NOT EXISTS
why Smith and Wilson are chosen?
from inner NOT EXISTS(subquery)
⇒ Celko,Higgins,Jones get return values which are planes they can not fly
⇒ Smith and Wilson get nothing because they can fly all planes in the Hangar
outside NOT EXISTS・・・
⇒ trying to get pilots who get nothing from inner NOT EXISTS(subquery)
⇒ pilots who have no planes they can not fly in the Hangar
⇒ pilots who can fly all planes in the Hangar
⇒ Smith and Wilson fit this condition
Answer SQL
--SELECT PS1.*
SELECT DISTINCT pilot
FROM PilotSkills AS PS1
WHERE NOT EXISTS (
SELECT * FROM Hangar
WHERE NOT EXISTS (
SELECT * FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot AND PS2.plane = Hangar.plane
)
)
▼ Output
| pilot |
|---|
| Smith |
| Wilson |
Try to divide
What kind of division is 『Relational Division』?
It is the idea of SQL so it is not easy to imagine
below is an image what 『Relational Division』 does, I think ・・・
this does not look like doing division ・・・
but just remove pilots who can not fly in the Hangar from PilotSkills AS PS1
so focus on the characteristic of division
⇒ 6 ÷ 3 = 2
⇒ dividend ÷ divisor = quotient
⇒ 3 x 2 = 6
⇒ divisor x quotient = dividend
if we can see 『 divisor x quotient = dividend 』
we can tell 『Relational Division』 is dividing
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
Quoted from the book
the important characteristic of a relational division is that
the CROSS JOIN(Cartesian product) of the divisor and the quotient
produces a valid subset of rows from the dividend
This is where the name comes from
～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
Multiplication SQL
WITH answer AS (
SELECT DISTINCT pilot
FROM PilotSkills AS PS1
WHERE NOT EXISTS (
SELECT * FROM Hangar
WHERE NOT EXISTS (
SELECT * FROM PilotSkills AS PS2
WHERE PS1.pilot = PS2.pilot AND PS2.plane = Hangar.plane
)
)
)
SELECT * FROM answer, Hangar
▼ Output(right side of image)
define answer SQL as CTE(answer), then CROSS JOIN with Hangar table
left side of image is the output from 『use subquery in WHERE clause』
⇒ this output come from PilotSkills
right side image is multiplication
⇒ quotient(answer) x divisor(Hangar) = devidend(PilotSkills)
left side and right side are not exactly the same but almost the same
『Relational Division』 does something like division
