Fearless Joins: Just enough SQL to be effective
Swanand Pagnis
@swanand
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:
- Why is it called a join and what exactly does it mean? Joins from first principles.
- 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
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
-
- The workshop that I teach on this subject: https://postgres-workshop.com/
-
- 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)
-
- My most popular talk that talks about understanding RDBMS as a foundational skill for career development: https://speakerdeck.com/swanandp/the-well-rounded-engineer
{{ errorMsg }}