Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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.

Swanand Pagnis

September 01, 2020
Tweet

More Decks by Swanand Pagnis

Other Decks in Programming

Transcript

  1. (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)
  2. (a, 1) - (1, %) (a, 1) - (3, &)

    (b, 2) - (1, %) (b, 2) - (3, &)
  3. Try this: select * FROM (VALUES ('one', 1), ('two', 2))

    as t1, (VALUES (1, 'O'), (2, 'T')) as t2;
  4. Result: +-----------+-----------+-----------+-----------+ | column1 | column2 | column1 | column2

    | |-----------+-----------+-----------+-----------| | one | 1 | 1 | O | | one | 1 | 2 | T | | two | 2 | 1 | O | | two | 2 | 2 | T | +-----------+-----------+-----------+-----------+
  5. 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"
  6. join on L.column2 = R.column1 (a, 1) - (1, %)

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

    - matched (b, 2) - null - L has no match null - (3, &) - R has no match
  8. 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
  9. join on L.column2 = R.column1 (a, 1) - (1, %)

    - always included (b, 2) - null - left and full null - (3, &) - right and full
  10. 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
  11. 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
  12. - Computer Science - OS - Memory Management - Processes

    and Signals - Software Engineering - OOP Design - Encapsulation - Functional Programming