Pro Yearly is on sale from $80 to $50! »

Fearless Joins

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.

A9e271fb1622f8dbb6d652993f5a23a7?s=128

Swanand Pagnis

September 01, 2020
Tweet

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!