Lesson 15 of 15 · 20 min
Non-equi joins: matching on ranges
By the end of this lesson you can write joins whose ON condition uses <, <=, BETWEEN or interval overlap, build a small lookup table with VALUES, avoid gaps and double counting at range boundaries, and test whether two periods overlap.
Every join so far matched on equality: o.customer_id = c.customer_id. But ON accepts any condition that is true or false for a pair of rows. A join whose condition is not a plain equality is called a non-equi join. The matching rule is unchanged (a pair is kept when the condition is true), so everything you know about row counts still applies, with one twist: a range can match zero, one or several rows, depending on how the ranges are drawn.
Lookup tables with VALUES
Range rules often aren't stored anywhere. You can write a small table inline with VALUES: each parenthesised list is a row, and the alias names the columns. It behaves like any other table in FROM.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons