Fearless Joins: Just enough SQL to be effective

SQL joins strike terror in the hearts of some developers. Some others can write their out of them, but never truly grok them and get stuck when things go wrong. We will undo the damage done by that useless venn diagram. Yup, the same one you’re thinking of.

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

• What the hell 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?

We aim to “decomplect” joins in this session i.e. build up an understanding of the invariants that are behind SQL joins, and work out any join clause using them. If you pay sufficiennt attention, by the end of the 30 minutes, you will be able to very confidently explain the difference between adding a condition in JOIN clause vs adding that same condition in a WHERE clause and when to use which construct. Amongst other things.

Outline

We roughly cover these main ideas:

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

There will simple practical examples, and visual represenation of the joins.

Requirements

This is not a SQL primer. Prior experience in writing a query is needed. Experience with ActiveRecord, Django.DB is fine.
A willingness to unlearn and relearn is needed.

Speaker bio

Senior backend engineer, leader, and mentor, with a focus on web application development and relational databases. I have been using and teaching MySQL, PostgreSQL for over 10 years.

Links

• 1. The workshop that I teach on this subject: https://postgres-workshop.com/
• 2. A blog post explaining some ideas behind this talk: https://info.pagnis.in/blog/2017/08/19/deconstructing-sql-queries/ (This talk goes well beyond the post)
• 3. My most popular talk that talks about understanding RDBMS as a foundational skill for career development: https://speakerdeck.com/swanandp/the-well-rounded-engineer