Fearless Joins - Speaker Deck

Slide 1

Slide 1 text

Fearless Joins Just enough SQL to be effective

Slide 2

Slide 2 text

Swanand Pagnis @_swanand info.pagnis.in queries.fun

Slide 3

Slide 3 text

Outgrow ActiveRecord, Django.DB, and friends

Slide 4

Slide 4 text

Joins

Slide 5

Slide 5 text

- Understand joins - Write complex joins - When to use which

Slide 6

Slide 6 text

Inner / Outer Left / Right / Full Recursive Lateral

Slide 7

Slide 7 text

Have sets, will combine

Slide 8

Slide 8 text

Have sets, will join

Slide 9

Slide 9 text

Joins are about sets. Not tables.

Slide 10

Slide 10 text

Your data could be in tables

Slide 11

Slide 11 text

Your data could be in views

Slide 12

Slide 12 text

Your data could be in a file

Slide 13

Slide 13 text

Your data could be in another query

Slide 14

Slide 14 text

Joins are about sets.

Slide 15

Slide 15 text

Unlearn the Venn diagram. #

Slide 16

Slide 16 text

Illustration time! $

Slide 17

Slide 17 text

Refer to the PDF

Slide 18

Slide 18 text

Join Semantics:

Slide 19

Slide 19 text

SET L SET R

Slide 20

Slide 20 text

L set: [(a, 1), (b, 2)] R set: [(1, %), (3, &)]

Slide 21

Slide 21 text

L set: [(a, 1), (b, 2), null] R set: [(1, %), (3, &), null]

Slide 22

Slide 22 text

Working set:

Slide 23

Slide 23 text

(a, 1) - (1, %) (a, 1) - (3, &) (a, 1) - null (b, 2) - (1, %) (b, 2) - (3, &) (b, 2) - null null - (1, %) null - (3, &) null - null (Ignored for practicality)

Slide 24

Slide 24 text

Joins without predicate

Slide 25

Slide 25 text

(a, 1) - (1, %) (a, 1) - (3, &) (b, 2) - (1, %) (b, 2) - (3, &)

Slide 26

Slide 26 text

All nulls ignored.

Slide 27

Slide 27 text

Try this: select * FROM (VALUES ('one', 1), ('two', 2)) as t1, (VALUES (1, 'O'), (2, 'T')) as t2;

Slide 28

Slide 28 text

Result: +-----------+-----------+-----------+-----------+ | column1 | column2 | column1 | column2 | |-----------+-----------+-----------+-----------| | one | 1 | 1 | O | | one | 1 | 2 | T | | two | 2 | 1 | O | | two | 2 | 2 | T | +-----------+-----------+-----------+-----------+

Slide 29

Slide 29 text

Joins with predicate

Slide 30

Slide 30 text

join on L.column2 = R.column1 (a, 1) - (1, %) ✅ "true" match (a, 1) - (3, &) "false" match (a, 1) - null because already matched (b, 2) - (1, %) "false" match (b, 2) - (3, &) "false" match (b, 2) - null ✅ "no match" null - (1, %) because already matched null - (3, &) ✅ "no match"

Slide 31

Slide 31 text

join on L.column2 = R.column1 (a, 1) - (1, %) ✅ "true" match (b, 2) - null ✅ "no match" null - (3, &) ✅ "no match"

Slide 32

Slide 32 text

join on L.column2 = R.column1 (a, 1) - (1, %) - matched (b, 2) - null - L has no match null - (3, &) - R has no match

Slide 33

Slide 33 text

Back to left/inner/outer

Slide 34

Slide 34 text

join on L.column2 = R.column1 inner — skip all "no match" left — skip all "R with no match" right — skip all "L with no match" full - don't skip anything

Slide 35

Slide 35 text

join on L.column2 = R.column1 (a, 1) - (1, %) - always included (b, 2) - null - left and full null - (3, &) - right and full

Slide 36

Slide 36 text

Joins in action

Slide 37

Slide 37 text

We will use this schema:

Slide 38

Slide 38 text

Rumours Tidbits Sources

Slide 39

Slide 39 text

Rumour is provided by many Sources Source provides many Rumours Tidbit is about a Rumour Tidbit is provided by many Sources  Source provides many Tidbits

Slide 40

Slide 40 text

Rumour: "BigCo is going all remote" Sources: PopularMag, BuzzStream Tidbits: - Employees are given home setup budgets - A job was posted on WeWorkRemotely - Hired Jane; senior leader, outspoken remote work supporter - CEO followed "ReMoat Podcast" on Twitter

Slide 41

Slide 41 text

Labels

Slide 42

Slide 42 text

Label can sub-labels Label can have one parent label A classic tree hierarchy.

Slide 43

Slide 43 text

- Computer Science - OS - Memory Management - Processes and Signals - Software Engineering - OOP Design - Encapsulation - Functional Programming