Fearless Joins

Companion slides for the "Fearless Joins" talk: https://hasgeek.com/rootconf/just-enough-sql-to-be-effective/

Warning: May not be very effective on their own.

If you work with SQL, you invariably come across questions like:

- What is a lateral join?
- When do I pick what kind of join?
- Rectangular join? What?
- What happens if you mix inner and outer joins together?

With simple practical examples, and visual representation of the joins, we will cover:

- Why is it called a join and what exactly does it mean?
- The concepts behind “inner/outer” and “left/right” and how DB engines determine which rows will be in ResultSet.
- The technique of “one at a time” in building large, complex joins.

Transcript

1. Fearless Joins Just enough SQL to be effective
2. Swanand Pagnis @_swanand info.pagnis.in queries.fun
3. Outgrow ActiveRecord, Django.DB, and friends
4. Joins
5. - Understand joins - Write complex joins - When to use which
6. Inner / Outer Left / Right / Full Recursive Lateral
7. Have sets, will combine
8. Have sets, will join
9. Joins are about sets. Not tables.
10. Your data could be in tables
11. Your data could be in views
12. Your data could be in a file
13. Your data could be in another query
14. Joins are about sets.
15. Unlearn the Venn diagram. #
16. Illustration time! $
17. Refer to the PDF
18. Join Semantics:
19. SET L <joins> SET R <based on predicate>
20. L set: [(a, 1), (b, 2)] R set: [(1, %), (3, &)]
21. L set: [(a, 1), (b, 2), null] R set: [(1, %), (3, &), null]
22. Working set:
23. (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)
24. Joins without predicate
25. (a, 1) - (1, %) (a, 1) - (3, &) (b, 2) - (1, %) (b, 2) - (3, &)
26. All nulls ignored.
27. Try this: select * FROM (VALUES ('one', 1), ('two', 2)) as t1, (VALUES (1, 'O'), (2, 'T')) as t2;
28. Result: +-----------+-----------+-----------+-----------+ | column1 | column2 | column1 | column2 | |-----------+-----------+-----------+-----------| | one | 1 | 1 | O | | one | 1 | 2 | T | | two | 2 | 1 | O | | two | 2 | 2 | T | +-----------+-----------+-----------+-----------+
29. Joins with predicate
30. 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"
31. join on L.column2 = R.column1 (a, 1) - (1, %) ✅ "true" match (b, 2) - null ✅ "no match" null - (3, &) ✅ "no match"
32. join on L.column2 = R.column1 (a, 1) - (1, %) - matched (b, 2) - null - L has no match null - (3, &) - R has no match
33. Back to left/inner/outer
34. 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
35. join on L.column2 = R.column1 (a, 1) - (1, %) - always included (b, 2) - null - left and full null - (3, &) - right and full
36. Joins in action
37. We will use this schema:
38. Rumours Tidbits Sources
39. 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
40. 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
41. Labels
42. Label can sub-labels Label can have one parent label A classic tree hierarchy.
43. - Computer Science - OS - Memory Management - Processes and Signals - Software Engineering - OOP Design - Encapsulation - Functional Programming
44. https:"//gist.github.com/swanandp/ d0252911bb7bb4dbe82a0f9ce8df9d1a
45. Questions?
46. Thank you!