What the heck is a lateral join anyway?

Learn The Advantages of SQL’s Least Popular Join

Photo by Tianyi Ma on Unsplash

If you’d like to support my writing, consider buying a copy of my E-Book, JetPack SQL. It’s a comprehensive, 70+ page PDF for absolute beginners. Click to learn more: https://stan.store/datawithjon/p/elevate-your-sql-skills-elevate-your-career

A lateral join combines two tables using a different syntax than typical joins. In a lateral join, the right-hand table is expressed as a subquery, and the join condition is specified within the WHERE clause of this subquery.

If that sounds confusing, don’t worry. Succinctly describing how lateral joins work is difficult. But fret not; things make more sense after working through some examples.

So let’s start from the top with a simple lateral join that mimics a left join.

Simple Left Join to Simple Lateral Join

Let’s say we have two tables in our database, one for users and one for profiles. There’s a one-to-one relationship between these two tables.

User and Profile Tables