Visualize SQL | Let's explain 『Relational  Division』 in detail

# 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
```SQL:SQL
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


![re_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b6366a0f-1157-5c7b-36b1-42ccd1294fd0.png)

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
```SQL:SQL
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



![re_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/7bc8335b-298d-ef0a-7530-e52a952b0341.png)



▼ Output


![re_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b327f4f4-a3f8-215c-edca-4a86aa5cd869.png)



# Planes lists that pilot can fly in the Hangar
```SQL:SQL
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


![re_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e112d703-11be-89fb-a68a-6307caf1d050.png)



------

image of table data flow
check 『Planes lists that pilot can fly』 for the square part of the blue dotted line


![re_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/438dad51-c8cd-a77d-6a22-d55a196ad519.png)


-----

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


![re_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/61bbd1c1-c5be-0323-c4bf-d644893d998a.png)



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
```SQL:SQL
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


![re_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/12120716-714b-b903-5c8c-06d1cfb5d920.png)


# use subquery in WHERE clause
```SQL:SQL
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


![re_9.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/4a2b26f0-5254-3775-8f62-86a0714f6847.png)




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


![re_11.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/b5866b29-0975-889c-b53b-03a08ac99f6f.png)



# Answer SQL
```SQL: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 ・・・


![re_12.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/6ce6a6f5-8b9d-1925-97a1-963e4acfc7ef.png)

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
```SQL: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**


![re_13.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/37f28d1c-80d5-e271-abc8-ff54efff936d.png)


# References
[Joe Celko's SQL Puzzles and Answers](https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)