· 8 years ago · Mar 17, 2018, 08:58 AM
1/*
2
3 TUTORIAL: Ansi-SQL Refresher
4
5 PURPOSE: Crank open you mysql cli, copy & paste until you are one with your INNER JOIN.
6
7 DEMO SCENARIO:
8 - Andrew, Anna & Angela work at Apple.
9 - Sam, Sally & Selma work at Spotify.
10 - Bob & Britney are unemployed.
11 - Rotten Mgmt Group has no employees.
12
13 VENN DIAGRAM:
14
15 +--company--------+
16 | |
17 | +----------person-----------+
18 | | . |
19 | | . |
20 | | . |
21 | | . |
22 | +--employed-----unemployed--+
23 | |
24 +-----------------+
25
26*/
27
28
29# TABLES
30
31DROP TABLE IF EXISTS demo_company;
32DROP TABLE IF EXISTS demo_person;
33DROP TABLE IF EXISTS demo_company_employee;
34
35CREATE TABLE demo_company (id int, name varchar(125));
36CREATE TABLE demo_person (id int, name varchar(125));
37CREATE TABLE demo_company_employee (id int, companyId int, employeeId int);
38
39
40# DATA
41
42INSERT INTO demo_company (id, name) VALUES (1,'Apple');
43INSERT INTO demo_company (id, name) VALUES (2,'Spotify');
44INSERT INTO demo_company (id, name) VALUES (3,'Rotten Mgmt Group');
45
46INSERT INTO demo_person (id, name) VALUES (1,'Andrew');
47INSERT INTO demo_person (id, name) VALUES (2,'Sam');
48INSERT INTO demo_person (id, name) VALUES (3,'Bob');
49INSERT INTO demo_person (id, name) VALUES (4,'Anna');
50INSERT INTO demo_person (id, name) VALUES (5,'Sally');
51INSERT INTO demo_person (id, name) VALUES (6,'Britney');
52INSERT INTO demo_person (id, name) VALUES (7,'Angela');
53INSERT INTO demo_person (id, name) VALUES (8,'Selma');
54
55INSERT INTO demo_company_employee (id, companyId, employeeId) VALUES (1,1,1);
56INSERT INTO demo_company_employee (id, companyId, employeeId) VALUES (2,1,4);
57INSERT INTO demo_company_employee (id, companyId, employeeId) VALUES (3,1,7);
58INSERT INTO demo_company_employee (id, companyId, employeeId) VALUES (4,2,2);
59INSERT INTO demo_company_employee (id, companyId, employeeId) VALUES (5,2,5);
60INSERT INTO demo_company_employee (id, companyId, employeeId) VALUES (6,2,8);
61
62
63# VERIFY
64
65mysql> SELECT * FROM demo_company;
66+------+-------------------+
67| id | name |
68+------+-------------------+
69| 1 | Apple |
70| 2 | Spotify |
71| 3 | Rotten Mgmt Group |
72+------+-------------------+
733 rows in set (0.00 sec)
74
75mysql> SELECT * FROM demo_person;
76+------+---------+
77| id | name |
78+------+---------+
79| 1 | Andrew |
80| 2 | Sam |
81| 3 | Bob |
82| 4 | Anna |
83| 5 | Sally |
84| 6 | Britney |
85| 7 | Angela |
86| 8 | Selma |
87+------+---------+
888 rows in set (0.00 sec)
89
90mysql> SELECT * FROM demo_company_employee;
91+------+-----------+------------+
92| id | companyId | employeeId |
93+------+-----------+------------+
94| 1 | 1 | 1 |
95| 2 | 1 | 4 |
96| 3 | 1 | 7 |
97| 4 | 2 | 2 |
98| 5 | 2 | 5 |
99| 6 | 2 | 8 |
100+------+-----------+------------+
1016 rows in set (0.01 sec)
102
103
104# INNER JOIN ("companies and their employed people")
105# returns rows when there is a match in both tables
106
107+--company--------+
108| |
109| +----------person-----------+
110| |XXXXXXXXXXXX. |
111| |XXXXXXXXXXXX. |
112| |XXXXXXXXXXXX. |
113| |XXXXXXXXXXXX. |
114| +--employed-----unemployed--+
115| |
116+-----------------+
117
118SELECT c.name as company, p.name as person
119 FROM demo_company c
120 INNER JOIN demo_company_employee ce
121 ON c.id = ce.companyId
122 INNER JOIN demo_person p
123 ON p.id = ce.employeeId;
124
125mysql> SELECT c.name as company, p.name as person
126 -> FROM demo_company c
127 -> INNER JOIN demo_company_employee ce
128 -> ON c.id = ce.companyId
129 -> INNER JOIN demo_person p
130 -> ON p.id = ce.employeeId;
131+---------+--------+
132| company | person |
133+---------+--------+
134| Apple | Andrew |
135| Spotify | Sam |
136| Apple | Anna |
137| Spotify | Sally |
138| Apple | Angela |
139| Spotify | Selma |
140+---------+--------+
1416 rows in set (0.00 sec)
142
143
144# LEFT OUTER JOIN ("all companies with or without employed people")
145# returns all rows from the left table, even if there are no matches in the right table
146
147+--company--------+
148|XXXXXXXXXXXXXXXXX|
149|XXXX+----------person-----------+
150|XXXX|XXXXXXXXXXXX. |
151|XXXX|XXXXXXXXXXXX. |
152|XXXX|XXXXXXXXXXXX. |
153|XXXX|XXXXXXXXXXXX. |
154|XXXX+--employed-----unemployed--+
155|XXXXXXXXXXXXXXXXX|
156+-----------------+
157
158SELECT c.name as company, p.name as person
159 FROM demo_company c
160 LEFT JOIN demo_company_employee ce
161 ON c.id = ce.companyId
162 LEFT JOIN demo_person p
163 ON p.id = ce.employeeId;
164
165mysql> SELECT c.name as company, p.name as person
166 -> FROM demo_company c
167 -> LEFT JOIN demo_company_employee ce
168 -> ON c.id = ce.companyId
169 -> LEFT JOIN demo_person p
170 -> ON p.id = ce.employeeId;
171+-------------------+--------+
172| company | person |
173+-------------------+--------+
174| Apple | Andrew |
175| Apple | Anna |
176| Apple | Angela |
177| Spotify | Sam |
178| Spotify | Sally |
179| Spotify | Selma |
180| Rotten Mgmt Group | NULL |
181+-------------------+--------+
1827 rows in set (0.00 sec)
183
184
185# RIGHT OUTER JOIN ("all persons and the companies they are employed by")
186# returns all rows from the right table, even if there are no matches in the left table
187
188+--company--------+
189| |
190| +----------person-----------+
191| |XXXXXXXXXXXX.XXXXXXXXXXXXXX|
192| |XXXXXXXXXXXX.XXXXXXXXXXXXXX|
193| |XXXXXXXXXXXX.XXXXXXXXXXXXXX|
194| |XXXXXXXXXXXX.XXXXXXXXXXXXXX|
195| +--employed-----unemployed--+
196| |
197+-----------------+
198
199SELECT c.name as company, p.name as person
200 FROM demo_company c
201 RIGHT JOIN demo_company_employee ce
202 ON c.id = ce.companyId
203 RIGHT JOIN demo_person p
204 ON p.id = ce.employeeId;
205
206mysql> SELECT c.name as company, p.name as person
207 -> FROM demo_company c
208 -> RIGHT JOIN demo_company_employee ce
209 -> ON c.id = ce.companyId
210 -> RIGHT JOIN demo_person p
211 -> ON p.id = ce.employeeId;
212+---------+---------+
213| company | person |
214+---------+---------+
215| Apple | Andrew |
216| Spotify | Sam |
217| NULL | Bob |
218| Apple | Anna |
219| Spotify | Sally |
220| NULL | Britney |
221| Apple | Angela |
222| Spotify | Selma |
223+---------+---------+
2248 rows in set (0.00 sec)
225
226
227# SEMI JOIN ("all the companies that employ people")
228
229+--company--------+
230| |
231| +----------person-----------+
232| |XXXXXXXXXXXX. |
233| |XXXXXXXXXXXX. |
234| |XXXXXXXXXXXX. |
235| |XXXXXXXXXXXX. |
236| +--employed-----unemployed--+
237| |
238+-----------------+
239
240SELECT c.name as company
241 FROM demo_company c
242 WHERE EXISTS (
243 SELECT 1
244 FROM demo_company_employee ce
245 WHERE c.id = ce.companyId
246 );
247
248mysql> SELECT c.name as company
249 -> FROM demo_company c
250 -> WHERE EXISTS (
251 -> SELECT 1
252 -> FROM demo_company_employee ce
253 -> WHERE c.id = ce.companyId
254 -> );
255+---------+
256| company |
257+---------+
258| Apple |
259| Spotify |
260+---------+
2612 rows in set (0.00 sec)
262
263
264# ANTI SEMI JOIN ("all the companies without employees")
265
266+--company--------+
267|XXXXXXXXXXXXXXXXX|
268|XXXX+----------person-----------+
269|XXXX| . |
270|XXXX| . |
271|XXXX| . |
272|XXXX| . |
273|XXXX+--employed-----unemployed--+
274|XXXXXXXXXXXXXXXXX|
275+-----------------+
276
277SELECT c.name as company
278 FROM demo_company c
279 WHERE NOT EXISTS (
280 SELECT 1
281 FROM demo_company_employee ce
282 WHERE c.id = ce.companyId
283 );
284
285mysql> SELECT c.name as company
286 -> FROM demo_company c
287 -> WHERE NOT EXISTS (
288 -> SELECT 1
289 -> FROM demo_company_employee ce
290 -> WHERE c.id = ce.companyId
291 -> );
292+-------------------+
293| company |
294+-------------------+
295| Rotten Mgmt Group |
296+-------------------+
2971 row in set (0.00 sec)
298
299
300# LEFT OUTER JOIN with exclusion ("all the companies without employees")
301
302+--company--------+
303|XXXXXXXXXXXXXXXXX|
304|XXXX+----------person-----------+
305|XXXX| . |
306|XXXX| . |
307|XXXX| . |
308|XXXX| . |
309|XXXX+--employed-----unemployed--+
310|XXXXXXXXXXXXXXXXX|
311+-----------------+
312
313SELECT c.name as company, p.name as person
314 FROM demo_company c
315 LEFT JOIN demo_company_employee ce
316 ON c.id = ce.companyId
317 LEFT JOIN demo_person p
318 ON p.id = ce.employeeId
319 WHERE ce.companyId is null;
320
321mysql> SELECT c.name as company, p.name as person
322 -> FROM demo_company c
323 -> LEFT JOIN demo_company_employee ce
324 -> ON c.id = ce.companyId
325 -> LEFT JOIN demo_person p
326 -> ON p.id = ce.employeeId
327 -> WHERE ce.companyId is null;
328+-------------------+--------+
329| company | person |
330+-------------------+--------+
331| Rotten Mgmt Group | NULL |
332+-------------------+--------+
3331 row in set (0.00 sec)
334
335
336# RIGHT OUTER JOIN with exclusion ("all unemployed people")
337
338+--company--------+
339| |
340| +----------person-----------+
341| | .XXXXXXXXXXXXXX|
342| | .XXXXXXXXXXXXXX|
343| | .XXXXXXXXXXXXXX|
344| | .XXXXXXXXXXXXXX|
345| +--employed-----unemployed--+
346| |
347+-----------------+
348
349SELECT c.name as company, p.name as person
350 FROM demo_company c
351 RIGHT JOIN demo_company_employee ce
352 ON c.id = ce.companyId
353 RIGHT JOIN demo_person p
354 ON p.id = ce.employeeId
355 WHERE c.id is null;
356
357mysql> SELECT c.name as company, p.name as person
358 -> FROM demo_company c
359 -> RIGHT JOIN demo_company_employee ce
360 -> ON c.id = ce.companyId
361 -> RIGHT JOIN demo_person p
362 -> ON p.id = ce.employeeId
363 -> WHERE c.id is null;
364+---------+---------+
365| company | person |
366+---------+---------+
367| NULL | Bob |
368| NULL | Britney |
369+---------+---------+
3702 rows in set (0.00 sec)
371
372
373# FULL OUTER JOIN (all companies and people properly matched)
374# returns all records when there is a match in either left or right table
375
376+--company--------+
377|XXXXXXXXXXXXXXXXX|
378|XXXX+----------person-----------+
379|XXXX|XXXXXXXXXXXX.XXXXXXXXXXXXXX|
380|XXXX|XXXXXXXXXXXX.XXXXXXXXXXXXXX|
381|XXXX|XXXXXXXXXXXX.XXXXXXXXXXXXXX|
382|XXXX|XXXXXXXXXXXX.XXXXXXXXXXXXXX|
383|XXXX+--employed-----unemployed--+
384|XXXXXXXXXXXXXXXXX|
385+-----------------+
386
387SELECT c.name as company, p.name as person
388 FROM demo_company c
389 LEFT JOIN demo_company_employee ce
390 ON c.id = ce.companyId
391 LEFT JOIN demo_person p
392 ON p.id = ce.employeeId
393 UNION
394SELECT c.name as company, p.name as person
395 FROM demo_company c
396 RIGHT JOIN demo_company_employee ce
397 ON c.id = ce.companyId
398 RIGHT JOIN demo_person p
399 ON p.id = ce.employeeId;
400
401mysql> SELECT c.name as company, p.name as person
402 -> FROM demo_company c
403 -> LEFT JOIN demo_company_employee ce
404 -> ON c.id = ce.companyId
405 -> LEFT JOIN demo_person p
406 -> ON p.id = ce.employeeId
407 -> UNION
408 -> SELECT c.name as company, p.name as person
409 -> FROM demo_company c
410 -> RIGHT JOIN demo_company_employee ce
411 -> ON c.id = ce.companyId
412 -> RIGHT JOIN demo_person p
413 -> ON p.id = ce.employeeId;
414+-------------------+---------+
415| company | person |
416+-------------------+---------+
417| Apple | Andrew |
418| Apple | Anna |
419| Apple | Angela |
420| Spotify | Sam |
421| Spotify | Sally |
422| Spotify | Selma |
423| Rotten Mgmt Group | NULL |
424| NULL | Bob |
425| NULL | Britney |
426+-------------------+---------+
4279 rows in set (0.00 sec)
428
429
430# FULL OUTER JOIN with exclusion (all companies and people without matches)
431
432+--company--------+
433|XXXXXXXXXXXXXXXXX|
434|XXXX+----------person-----------+
435|XXXX| .XXXXXXXXXXXXXX|
436|XXXX| .XXXXXXXXXXXXXX|
437|XXXX| .XXXXXXXXXXXXXX|
438|XXXX| .XXXXXXXXXXXXXX|
439|XXXX+--employed-----unemployed--+
440|XXXXXXXXXXXXXXXXX|
441+-----------------+
442
443SELECT c.name as company, p.name as person
444 FROM demo_company c
445 LEFT JOIN demo_company_employee ce
446 ON c.id = ce.companyId
447 LEFT JOIN demo_person p
448 ON p.id = ce.employeeId
449 WHERE ce.companyId is null
450 UNION
451SELECT c.name as company, p.name as person
452 FROM demo_company c
453 RIGHT JOIN demo_company_employee ce
454 ON c.id = ce.companyId
455 RIGHT JOIN demo_person p
456 ON p.id = ce.employeeId
457 WHERE c.id is null;
458
459mysql> SELECT c.name as company, p.name as person
460 -> FROM demo_company c
461 -> LEFT JOIN demo_company_employee ce
462 -> ON c.id = ce.companyId
463 -> LEFT JOIN demo_person p
464 -> ON p.id = ce.employeeId
465 -> WHERE ce.companyId is null
466 -> UNION
467 -> SELECT c.name as company, p.name as person
468 -> FROM demo_company c
469 -> RIGHT JOIN demo_company_employee ce
470 -> ON c.id = ce.companyId
471 -> RIGHT JOIN demo_person p
472 -> ON p.id = ce.employeeId
473 -> WHERE c.id is null;
474+-------------------+---------+
475| company | person |
476+-------------------+---------+
477| Rotten Mgmt Group | NULL |
478| NULL | Bob |
479| NULL | Britney |
480+-------------------+---------+
4813 rows in set (0.00 sec)
482
483
484# enjoy!