Snowflake SQL Tutorial 31 - Non-Equi Joins in Snowflake

Snowflake SQL · Episode 31

In this post we are going to cover non-equi joins in Snowflake.

Every ON clause in this level so far has had the same shape, something = something. That is a habit, not a rule. ON takes a boolean condition, and equality is only the most familiar condition that fits. Anything that comes out true or false will do, so >, <, BETWEEN and a date comparison are all legal. A join whose condition is not an equality is called a non-equi join.

We will spend most of the post on the one you will actually reach for, the band join, which matches a value to a range instead of to a key. We will get it right first, then break it two different ways and watch the row count change without a single error. After that we will try the same predicate on dates, and finish by joining on greater-than to build a ranking.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 31 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's start with the band join
3. Now let's group by the band
4. Now let's make the bands overlap
5. Now let's leave a gap between the bands
6. Let's put the three row counts side by side
7. Can we do the same thing with dates?
8. Now let's join on greater-than

Let's create some sample data

Everything below runs against the shared sample data for this course. Run the script once if you do not already have these tables.

Sample data

-- The shared sample data for this course. Run it once; every episode
-- in the series queries these same tables.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE EMPLOYEES (
    EMP_ID          INT,
    FIRST_NAME      VARCHAR,
    LAST_NAME       VARCHAR,
    EMAIL           VARCHAR,
    DEPT_ID         INT,
    MANAGER_ID      INT,
    SALARY          NUMBER(10,2),
    COMMISSION_PCT  NUMBER(4,2),
    HIRE_DATE       DATE
);

INSERT INTO EMPLOYEES VALUES
    (1, 'Aisha',  'Khan',      'aisha.khan@example.com',      10,    6, 118000.00, NULL,  '2019-03-11'),
    (2, 'Marcus', 'Reid',      'marcus.reid@example.com',     10,    6,  96500.00, NULL,  '2021-07-01'),
    (3, 'Lena',   'Fischer',   'lena.fischer@example.com',    20,    6, 104000.00, 0.05,  '2018-01-22'),
    (4, 'Tomas',  'Novak',     'tomas.novak@example.com',     20,    3,  87250.00, NULL,  '2022-09-05'),
    (5, 'Priya',  'Raman',     'priya.raman@example.com',     30,    6,  96500.00, 0.10,  '2020-11-17'),
    (6, 'Daniel', 'O''Connor', 'daniel.oconnor@example.com',  10, NULL, 131000.00, NULL,  '2016-05-30'),
    (7, 'Sofia',  'Marino',    'sofia.marino@example.com',    30,    5,  71400.00, 0.08,  '2023-02-13'),
    (8, 'Grace',  'Lin',       'grace.lin@example.com',     NULL,    6,  92000.00, NULL,  '2021-04-19');

Now let's add a small table of salary bands

Each band has a lower bound and an upper bound. In a real warehouse this would be a reference table. Here it is written inline so the post stays self-contained.

Query

SELECT * FROM (
  SELECT 'Junior' AS BAND, 0 AS LOW, 89999 AS HIGH
  UNION ALL SELECT 'Mid',      90000,  109999
  UNION ALL SELECT 'Senior',  110000, 9999999
) ORDER BY LOW;

Result3 rows · 3 columns

BAND	LOW	HIGH
Junior	0	89999
Mid	90000	109999
Senior	110000	9999999

Three rows and three columns. Now look at what is not there. There is no band ID on the employee side. A salary is not a band key, and no amount of equality will turn it into one.

The relationship we want is “falls inside”, not “equals”. So that is how we have to write the join.

Let's start with the band join

We join EMPLOYEES to the bands and put BETWEEN in the ON clause.

Query

SELECT E.FIRST_NAME, E.SALARY, B.BAND
FROM EMPLOYEES E
JOIN (
  SELECT 'Junior' AS BAND, 0 AS LOW, 89999 AS HIGH
  UNION ALL SELECT 'Mid',      90000,  109999
  UNION ALL SELECT 'Senior',  110000, 9999999
) B ON E.SALARY BETWEEN B.LOW AND B.HIGH
ORDER BY E.SALARY DESC;

Result8 rows · 3 columns

FIRST_NAME	SALARY	BAND
Daniel	131000.00	Senior
Aisha	118000.00	Senior
Lena	104000.00	Mid
Marcus	96500.00	Mid
Priya	96500.00	Mid
Grace	92000.00	Mid
Tomas	87250.00	Junior
Sofia	71400.00	Junior

Eight rows. Every employee, with the band their salary falls in. There is no equals sign anywhere in that ON clause, and Snowflake did not mind.

Nothing exotic happened here. The join still considers pairs of rows, and it still keeps the pairs for which the condition is true. Only the condition changed. Daniel and Aisha land in Senior, and Tomas and Sofia in Junior.

Both ends are included

BETWEEN is inclusive at both ends. x BETWEEN a AND b means x >= a AND x <= b. That matters for bands, because a bound you reuse as both the top of one band and the bottom of the next will match both of them. The bands above avoid it by ending one at 89999 and starting the next at 90000.

Now let's group by the band

Matching each employee to a band is rarely what we are actually asked for. What we are asked for is the distribution. The band is a column now, so we can group by it like any other.

Query

SELECT B.BAND, COUNT(*) AS N, MIN(E.SALARY) AS LOWEST, MAX(E.SALARY) AS HIGHEST
FROM EMPLOYEES E
JOIN (
  SELECT 'Junior' AS BAND, 0 AS LOW, 89999 AS HIGH
  UNION ALL SELECT 'Mid',      90000,  109999
  UNION ALL SELECT 'Senior',  110000, 9999999
) B ON E.SALARY BETWEEN B.LOW AND B.HIGH
GROUP BY B.BAND
ORDER BY LOWEST;

Result3 rows · 4 columns

BAND	N	LOWEST	HIGHEST
Junior	2	71400.00	87250.00
Mid	4	92000.00	104000.00
Senior	2	118000.00	131000.00

Three rows and four columns. Now let's check the counts against the band join we just ran: 2 + 4 + 2 = 8. Everybody is counted exactly once.

That addition is not a formality, and it is the reason for the rest of this post. A band join can return more rows than it was given, or fewer, and the reconciliation is the only place either one shows up.

Have a look at LOWEST and HIGHEST too. In every row they sit inside their own band's bounds. That is a second check, and it is free. A HIGHEST above its band's HIGH would mean the join matched something it should not have.

Now let's make the bands overlap

The bands we have used so far were disjoint. No two of them covered the same salary, and that was deliberate. Let's see what happens when they overlap.

Wide runs from 90000 to 120000. Narrow runs from 96000 to 100000, which is entirely inside it.

Query

SELECT E.FIRST_NAME, E.SALARY, B.BAND
FROM EMPLOYEES E
JOIN (
  SELECT 'Wide'   AS BAND,  90000 AS LOW, 120000 AS HIGH
  UNION ALL SELECT 'Narrow', 96000,        100000
) B ON E.SALARY BETWEEN B.LOW AND B.HIGH
ORDER BY E.SALARY DESC, B.BAND;

Result7 rows · 3 columns

FIRST_NAME	SALARY	BAND
Aisha	118000.00	Wide
Lena	104000.00	Wide
Marcus	96500.00	Narrow
Priya	96500.00	Narrow
Marcus	96500.00	Wide
Priya	96500.00	Wide
Grace	92000.00	Wide

Seven rows. Marcus is in the result twice and so is Priya, once against each band. They both earn 96500.00, which satisfies BETWEEN 90000 AND 120000 and BETWEEN 96000 AND 100000, so the join found two true pairings for each of them and returned both.

That is not an error. It is the answer to the question we asked. A join returns every pair of rows for which the condition holds, and here two pairs hold per person.

Now count the names, because something else is going on in the same grid. Only five people appear at all. Daniel earns 131000.00, which is above the top of both bands. Tomas earns 87250.00 and Sofia earns 71400.00, which are below the bottom of both. So three employees matched nothing and the inner join dropped them. Five people plus two duplicate rows is the seven.

Watch out

An overlap is most dangerous one step downstream. If the next thing we write is SUM(SALARY) or COUNT(*), Marcus's and Priya's salaries are now in the total twice each, and the total is still a perfectly plausible number. Nothing in the output says a row was counted more than once.

Now let's leave a gap between the bands

The opposite mistake is a hole in the coverage. These two bands run from 90000 to 109999, then from 110000 upwards. They are disjoint, so nothing will be duplicated. But nothing covers a salary below 90000 either.

Query

SELECT E.FIRST_NAME, E.SALARY, B.BAND
FROM EMPLOYEES E
JOIN (
  SELECT 'Mid' AS BAND, 90000 AS LOW, 109999 AS HIGH
  UNION ALL SELECT 'Senior', 110000, 9999999
) B ON E.SALARY BETWEEN B.LOW AND B.HIGH
ORDER BY E.SALARY DESC;

Result6 rows · 3 columns

FIRST_NAME	SALARY	BAND
Daniel	131000.00	Senior
Aisha	118000.00	Senior
Lena	104000.00	Mid
Marcus	96500.00	Mid
Priya	96500.00	Mid
Grace	92000.00	Mid

Six rows. Tomas and Sofia are gone, because their salaries fall in the gap. There is no band for which the BETWEEN is true, so the inner join had no pair to keep. Two employees are simply absent from a headcount.

Again, no error. And this one is worse than the duplicate, because a missing row leaves nothing behind. A duplicate at least puts a name on screen twice where a careful reader might catch it. A dropped row is invisible by construction.

Make the gap show up

If we want the gap to be visible rather than silent, we make the join a LEFT JOIN. The unmatched employees come back with NULL in the BAND column, which turns “silently missing” into “obviously unclassified”. Then a WHERE BAND IS NULL lists exactly which rows our bands fail to cover.

Let's put the three row counts side by side

The employee data never changed. Only the band definitions did.

Band definitions	Rows back	What happened
Disjoint, no gaps	8	Correct. Every employee matched exactly one band.
Overlapping	7	Marcus and Priya matched two bands each and came back twice. Daniel, Tomas and Sofia fell outside both and were dropped.
A gap below the lowest band	6	Tomas and Sofia matched no band and were dropped.

Three different answers, and Snowflake raised no error for any of them. Each query was executed exactly as written. The bands were wrong, and a wrong band definition is not a syntax problem.

So here is the one habit to take from this post. After a band join, count the rows. If eight went in and eight did not come out, our ranges overlap or they have a hole in them. The row count is the only symptom we are going to get.

• More rows than we started with → the ranges overlap somewhere.
• Fewer rows than we started with → the ranges have a gap, or they do not reach far enough at one end.
• The same number of rows → the coverage is sound for this data. A range that no current row happens to fall into can still be wrong tomorrow.

Two defences

Two cheap defences are worth building in. Give the top band an absurdly high upper bound, the way the first set of bands ends at 9999999, and give the bottom band a lower bound of 0, so the ends cannot leak. Then check the band table itself. Each band's LOW should be exactly its predecessor's HIGH plus one.

Can we do the same thing with dates?

Ranges are not only about numbers. The most common non-equi condition in real work is a date comparison, and ORDERS gives us two dates per row to compare. When the order was placed, and when it shipped.

Let's ask for the orders that shipped within three days.

Query

SELECT ORDER_ID, ORDER_DATE, SHIP_DATE, DATEDIFF('day', ORDER_DATE, SHIP_DATE) AS DAYS
FROM ORDERS
WHERE SHIP_DATE IS NOT NULL AND DATEDIFF('day', ORDER_DATE, SHIP_DATE) <= 3
ORDER BY DAYS, ORDER_ID;

Result12 rows · 4 columns

ORDER_ID	ORDER_DATE	SHIP_DATE	DAYS
1003	2023-02-14	2023-02-16	2
1006	2023-03-22	2023-03-24	2
1011	2023-06-08	2023-06-10	2
1001	2023-01-12	2023-01-15	3
1015	2023-08-14	2023-08-17	3
1017	2023-09-16	2023-09-19	3
1020	2023-11-07	2023-11-10	3
1023	2023-12-27	2023-12-30	3
1024	2024-01-15	2024-01-18	3
1029	2024-04-06	2024-04-09	3
1035	2024-07-18	2024-07-21	3
1040	2024-10-16	2024-10-19	3

Twelve rows and four columns. Each one is an order whose SHIP_DATE is close behind its ORDER_DATE.

This one is a filter, not a join

Read that query again before you reuse it. There is no join in it at all. It is a single table and a WHERE clause. The two dates being compared are two columns of the same row, so no second table is needed. It is here because the predicate is the same idea in a different place.

That distinction is worth being precise about, because it decides where the condition goes. Comparing two columns of one row is a filter. Comparing a column of one row against a column of a different row is a join. The other row can be in another table, or in another copy of the same one. The operators are identical. The question is which rows we are putting next to each other.

Why the NULL test is spelled out

SHIP_DATE IS NOT NULL is in that WHERE clause on purpose. Not every order has shipped, and DATEDIFF against a NULL returns NULL, which is never <= 3. So those rows would have been excluded anyway. Writing the test out says we meant to exclude them, rather than leaving the next reader to work out whether we had thought about it.

Now let's join on greater-than

One more shape, and this is the one that shows what a non-equi join can do that an equality cannot. Let's join every employee to everybody who earns more than they do, and then count the matches. The whole condition is H.SALARY > E.SALARY.

Query

SELECT E.FIRST_NAME, E.SALARY, COUNT(H.EMP_ID) AS HOW_MANY_EARN_MORE
FROM EMPLOYEES E
LEFT JOIN EMPLOYEES H ON H.SALARY > E.SALARY
GROUP BY E.FIRST_NAME, E.SALARY
ORDER BY HOW_MANY_EARN_MORE;

Result8 rows · 3 columns

FIRST_NAME	SALARY	HOW_MANY_EARN_MORE
Daniel	131000.00	0
Aisha	118000.00	1
Lena	104000.00	2
Marcus	96500.00	3
Priya	96500.00	3
Grace	92000.00	5
Tomas	87250.00	6
Sofia	71400.00	7

Eight rows and three columns, and the last column is a ranking built entirely out of a comparison. Daniel has nobody above him. Sofia has everybody.

Now look at Marcus and Priya. They earn the same, 96500.00, and the column says the same thing for both. That is because > is strictly greater than. Neither of them counts the other, and neither counts themselves. Switching to >= would make each of them count the other and themselves, which is a different question with a different answer.

Two details in that query do real work. The join is a LEFT JOIN, so Daniel survives having nothing above him. And the count is COUNT(H.EMP_ID), not COUNT(*). COUNT of a column ignores NULLs, so Daniel's manufactured row counts as nothing. COUNT(*) would have counted the row itself and given him a misleading answer.

How many pairs did that join actually match?

Let's add the last column up. 0, 1, 2, 3, 3, 5, 6 and 7 come to 27. A table of eight rows produced 27 matched pairs. A wider inequality on bigger tables grows the same way, because the number of pairs is driven by how many rows satisfy the comparison, not by how many rows exist.

That is the thing to keep in mind about non-equi joins generally. An equality join on a key gives us roughly one match per row. A comparison gives us however many rows satisfy it, which can be most of the other table. So check the row count of a non-equi join before building anything on top of it.

Key points

• ON takes a boolean condition. Equality is a habit, not a requirement, and BETWEEN, > and date comparisons are all legal join conditions.
• A band join matches a value to a range, which is the answer when there is no key to match on: a salary is not a band ID.
• BETWEEN is inclusive at both ends, so a bound shared between two bands matches both of them.
• Overlapping bands duplicate rows. A row that satisfies two ranges comes back twice, and any SUM or COUNT downstream is then wrong by a plausible-looking amount.
• A gap between bands drops rows. A row that satisfies no range is removed by the inner join and leaves nothing behind.
• Neither failure raises an error. The same eight employees came back as 8, 7 and 6 rows depending only on how the bands were defined.
• So count the rows after a band join. More than you started with means an overlap. Fewer means a gap.
• Make an unmatched row visible with LEFT JOIN and WHERE BAND IS NULL instead of discovering it in a total.
• Comparing two columns of one row is a filter. Comparing columns across different rows is a join. The operators look identical.
• A non-equi join can match far more pairs than either table has rows: 27 pairs from eight employees in the ranking query.

Next in this series

In the next post we will cover ASOF JOIN. It is a non-equi join Snowflake gives us outright. For every row, find the nearest earlier row, which is how we answer “what was the price on that date?” without a correlated subquery.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the setup script shown above, including both of the wrong row counts. The EMPLOYEES and ORDERS data is invented demo data created by that script, and the band definitions are written inline in the queries that use them. This episode captures no SELECT * of either source table, so no row count is claimed for either one — every count on this page is of a grid printed beside it. No performance claims are made anywhere in this series: nothing here was timed, so nothing is said about what a range join costs, only about how many rows it returns. Join behaviour is described from the Snowflake JOIN documentation.