Snowflake SQL Tutorial 28 - USING vs ON in Snowflake, and Why NATURAL JOIN Returned Zero Rows

Snowflake SQL · Episode 28

In this post we are going to cover USING and NATURAL JOIN in Snowflake, and how both of them compare with the ON clause we have been writing so far.

USING and NATURAL JOIN are two shortcuts. Instead of writing the join condition ourselves, we let Snowflake work it out from the column names. USING takes the name we give it. NATURAL JOIN takes every name the two tables share, and we do not write anything at all.

We will start with ON, then write the same join with USING, then count the columns to see what the difference really is. After that we will try NATURAL JOIN, where it works, and then on a pair of tables where it comes back with no rows and no error.

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

Episode 28 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 ON, and ask for the key from both tables
  3. Now let's write the same join with USING
  4. Now let's count the columns with SELECT *
  5. Can we use USING on an outer join?
  6. Now let's try NATURAL JOIN
  7. So why is a correct result a problem?
  8. Let's see how that goes wrong a year from now
  9. So which one should we write?

Let's create some sample data

Both shortcuts in this post are driven by column names rather than by anything we write, so the place to start is the names themselves. Run the script below once if you do not already have these two tables.

Sample data
-- Both tables already exist if you have followed this level from the start.
-- This is the whole of both of them. Read the COLUMN NAMES rather than the data:
-- the names are what this episode turns on.
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,            -- <<-- the shared name
    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');

CREATE OR REPLACE TABLE DEPARTMENTS (
    DEPT_ID    INT,                 -- <<-- the shared name
    DEPT_NAME  VARCHAR,
    LOCATION   VARCHAR,
    BUDGET     NUMBER(12,2)
);

INSERT INTO DEPARTMENTS VALUES
    (10, 'Engineering', 'Charlotte', 2400000.00),
    (20, 'Finance',     'Charlotte', 1100000.00),
    (30, 'Marketing',   'Raleigh',    750000.00),
    (40, 'Operations',  'Raleigh',    640000.00);

Now let's read the two column lists against each other. EMPLOYEES has EMP_ID, FIRST_NAME, LAST_NAME, EMAIL, DEPT_ID, MANAGER_ID, SALARY, COMMISSION_PCT and HIRE_DATE. DEPARTMENTS has DEPT_ID, DEPT_NAME, LOCATION and BUDGET.

DEPT_ID is the only name they share. Same spelling, same meaning, same values. Hold on to that. It is what makes both shortcuts work here, and it is also the reason one of them is risky.

About the grids below

This episode did not re-capture a SELECT * of either table, so there is no grid of raw source rows on this page. We do not need one. Every grid up to the NATURAL JOIN section is a join of these two tables, and the column counts in the bars above them are what we are measuring. The last two grids join a pair of inline SELECTs instead, so that no real table had to be changed to produce them.

Let's start with ON, and ask for the key from both tables

Let's begin with the ON clause we already know. The one difference is that we are going to select the join key from both tables instead of just one.

Query
SELECT E.FIRST_NAME, E.DEPT_ID AS E_DEPT_ID, D.DEPT_ID AS D_DEPT_ID, D.DEPT_NAME
FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
ORDER BY E.EMP_ID;
Result7 rows · 4 columns
FIRST_NAMEE_DEPT_IDD_DEPT_IDDEPT_NAME
Aisha1010Engineering
Marcus1010Engineering
Lena2020Finance
Tomas2020Finance
Priya3030Marketing
Daniel1010Engineering
Sofia3030Marketing

Two columns holding the same value, row for row, all the way down. That is not a bug and it is not Snowflake being wasteful. ON joins two tables and keeps every column of both. The two key columns are equal in every row because the ON clause said they had to be.

Which raises the obvious question. If the whole point of an equi-join is that the two keys are equal, why carry both?

Now let's write the same join with USING

USING answers exactly that question. We give it the name of the column to join on, in brackets, and nothing else. No table prefixes, no equals sign, no expression.

Query
SELECT DEPT_ID, FIRST_NAME, DEPT_NAME
FROM EMPLOYEES
JOIN DEPARTMENTS USING (DEPT_ID)
ORDER BY EMP_ID;
Result7 rows · 3 columns
DEPT_IDFIRST_NAMEDEPT_NAME
10AishaEngineering
10MarcusEngineering
20LenaFinance
20TomasFinance
30PriyaMarketing
10DanielEngineering
30SofiaMarketing

The same seven rows as the ON version above, in the same order. But DEPT_ID is one column now. And have a look at the SELECT list: it has no table prefix on DEPT_ID either. After USING there is only one DEPT_ID, so there is nothing left to disambiguate.

That is the whole behavioural difference. USING returns the same rows as the equivalent ON clause, with the join key merged into one column.

What USING cannot do

USING needs the column to have the same name in both tables. It cannot join DEPT_ID to DEPARTMENT_ID, it cannot handle an inequality, and it cannot wrap either side in a function. Anything beyond one plain equality on one shared name needs ON.

Now let's count the columns with SELECT *

Three columns at a time makes that difference easy to miss. Let's ask for everything instead, and the difference becomes a number we can read straight off the grid.

SELECT * with ON
SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID ORDER BY E.EMP_ID;
Result7 rows · 13 columns · scroll →
EMP_IDFIRST_NAMELAST_NAMEEMAILDEPT_IDMANAGER_IDSALARYCOMMISSION_PCTHIRE_DATEDEPT_IDDEPT_NAMELOCATIONBUDGET
1AishaKhanaisha.khan@example.com106118000.00NULL2019-03-1110EngineeringCharlotte2400000.00
2MarcusReidmarcus.reid@example.com10696500.00NULL2021-07-0110EngineeringCharlotte2400000.00
3LenaFischerlena.fischer@example.com206104000.000.052018-01-2220FinanceCharlotte1100000.00
4TomasNovaktomas.novak@example.com20387250.00NULL2022-09-0520FinanceCharlotte1100000.00
5PriyaRamanpriya.raman@example.com30696500.000.102020-11-1730MarketingRaleigh750000.00
6DanielO'Connordaniel.oconnor@example.com10NULL131000.00NULL2016-05-3010EngineeringCharlotte2400000.00
7SofiaMarinosofia.marino@example.com30571400.000.082023-02-1330MarketingRaleigh750000.00

Walk the header row. DEPT_ID appears twice, once from each table. Nine columns of employee plus four columns of department is thirteen, and nothing has been merged.

Now the same thing with USING.

SELECT * with USING
SELECT * FROM EMPLOYEES JOIN DEPARTMENTS USING (DEPT_ID) ORDER BY EMP_ID;
Result7 rows · 12 columns · scroll →
DEPT_IDEMP_IDFIRST_NAMELAST_NAMEEMAILMANAGER_IDSALARYCOMMISSION_PCTHIRE_DATEDEPT_NAMELOCATIONBUDGET
101AishaKhanaisha.khan@example.com6118000.00NULL2019-03-11EngineeringCharlotte2400000.00
102MarcusReidmarcus.reid@example.com696500.00NULL2021-07-01EngineeringCharlotte2400000.00
203LenaFischerlena.fischer@example.com6104000.000.052018-01-22FinanceCharlotte1100000.00
204TomasNovaktomas.novak@example.com387250.00NULL2022-09-05FinanceCharlotte1100000.00
305PriyaRamanpriya.raman@example.com696500.000.102020-11-17MarketingRaleigh750000.00
106DanielO'Connordaniel.oconnor@example.comNULL131000.00NULL2016-05-30EngineeringCharlotte2400000.00
307SofiaMarinosofia.marino@example.com571400.000.082023-02-13MarketingRaleigh750000.00

Twelve. One DEPT_ID instead of two. And it has moved to the front of the result, ahead of EMP_ID, which is not where it sits in either table.

Written asRowsColumnsThe join key
JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID713Twice, in table order
JOIN DEPARTMENTS USING (DEPT_ID)712Once, promoted to the first column

Same rows, one fewer column, and the column order changed. That last part matters more than it looks. Anything that reads a result by position rather than by name sees a different shape depending on which form we wrote. An INSERT ... SELECT *, a CREATE TABLE AS SELECT *, a script that unpacks rows into variables: all of those care.

Why the duplicate name is more than cosmetic

The duplicate name in the thirteen-column result is its own small problem. A result with two columns called DEPT_ID is fine to look at, but anything that has to name those columns afterwards now has an ambiguity to resolve. A view, a CREATE TABLE AS, a client library keyed by column name. USING removes it, and so does naming your columns explicitly instead of writing *.

Can we use USING on an outer join?

Yes. USING is a way of writing the join condition, not a kind of join. It works anywhere an ON clause with a single equality would, including the outer joins from the last few posts. Let's try it with a LEFT JOIN.

Query
SELECT DEPT_ID, FIRST_NAME, DEPT_NAME
FROM EMPLOYEES
LEFT JOIN DEPARTMENTS USING (DEPT_ID)
ORDER BY EMP_ID;
Result8 rows · 3 columns
DEPT_IDFIRST_NAMEDEPT_NAME
10AishaEngineering
10MarcusEngineering
20LenaFinance
20TomasFinance
30PriyaMarketing
10DanielEngineering
30SofiaMarketing
NULLGraceNULL

Eight rows, so Grace is back. A left join keeps her whether or not she matches. Her merged DEPT_ID is NULL, and so is DEPT_NAME.

What you give up on an outer join

With one merged key column there is no second key left to test. The outer-join posts found unmatched rows by checking the right-hand table's key for NULL, and the result above has no separate right-hand key column any more. So to hunt unmatched rows, test a non-key column from the right-hand table instead. DEPT_NAME IS NULL would do it here. Or go back to ON, where both keys are still addressable.

Now let's try NATURAL JOIN

The second shortcut goes further. NATURAL JOIN takes no ON clause and no USING list. There is nothing for us to write.

It joins on every column name the two tables share, with an equality on each. Here that set has one member, DEPT_ID, so it should behave exactly like USING (DEPT_ID).

Query
SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS ORDER BY EMP_ID;
Result7 rows · 12 columns · scroll →
DEPT_IDEMP_IDFIRST_NAMELAST_NAMEEMAILMANAGER_IDSALARYCOMMISSION_PCTHIRE_DATEDEPT_NAMELOCATIONBUDGET
101AishaKhanaisha.khan@example.com6118000.00NULL2019-03-11EngineeringCharlotte2400000.00
102MarcusReidmarcus.reid@example.com696500.00NULL2021-07-01EngineeringCharlotte2400000.00
203LenaFischerlena.fischer@example.com6104000.000.052018-01-22FinanceCharlotte1100000.00
204TomasNovaktomas.novak@example.com387250.00NULL2022-09-05FinanceCharlotte1100000.00
305PriyaRamanpriya.raman@example.com696500.000.102020-11-17MarketingRaleigh750000.00
106DanielO'Connordaniel.oconnor@example.comNULL131000.00NULL2016-05-30EngineeringCharlotte2400000.00
307SofiaMarinosofia.marino@example.com571400.000.082023-02-13MarketingRaleigh750000.00

And it does. The same seven rows and the same twelve columns as the SELECT * with USING above, with the key merged and promoted to the front in the same way.

So it works. That is worth saying plainly before the rest of this page, because the problem with NATURAL JOIN is not that it misbehaves today.

So why is a correct result a problem?

Look at that query again and ask the question we would ask in review. What is this joining on? The query does not say. The answer is not in the SQL at all. It is in the current column names of two tables, as they happen to be at the moment the query runs.

So let's run the same construct where the two sides share two names instead of one. These are inline SELECTs rather than real tables, so we can run this without altering anything. Side A has DEPT_ID, LOCATION and WHO. Side B has DEPT_ID, LOCATION and DEPT_NAME. The DEPT_ID values are the same on both sides. The LOCATION values are not.

Query
SELECT *
FROM (SELECT 10 AS DEPT_ID, 'Charlotte' AS LOCATION, 'Aisha' AS WHO) A
NATURAL JOIN (SELECT 10 AS DEPT_ID, 'Raleigh' AS LOCATION, 'Engineering' AS DEPT_NAME) B;
Result0 rows · 4 columns
DEPT_IDLOCATIONWHODEPT_NAME

Nothing came back. And it is not an error. The query succeeded and returned no rows.

The headers of that empty result are the evidence for why. Two sides of three columns each came back as four columns, which means two names were merged rather than one: DEPT_ID and LOCATION. The join asked for both of them to match. The locations differ, so nothing survived.

Now let's join the same pair of rows on the column we actually meant.

Query
SELECT *
FROM (SELECT 10 AS DEPT_ID, 'Charlotte' AS LOCATION, 'Aisha' AS WHO) A
JOIN (SELECT 10 AS DEPT_ID, 'Raleigh' AS LOCATION, 'Engineering' AS DEPT_NAME) B
  ON A.DEPT_ID = B.DEPT_ID;
Result1 row · 6 columns · scroll →
DEPT_IDLOCATIONWHODEPT_IDLOCATIONDEPT_NAME
10CharlotteAisha10RaleighEngineering

One row, and six columns rather than four. An ON clause merges nothing, so both DEPT_IDs and both LOCATIONs are present, and we can see the mismatched locations sitting side by side. The join condition is one line of visible text, and that line is the whole difference between the two results.

Let's see how that goes wrong a year from now

Nobody writes the empty query above on purpose. The way it actually happens is slower than that.

  1. A NATURAL JOIN goes into production. The two tables share one column name, it is the right one, and the results are correct.
  2. Months later, somebody adds a column to one of those tables. A LOCATION, a CREATED_AT, an UPDATED_BY, a REGION. A perfectly reasonable change, reviewed, approved, merged.
  3. The name collides with a column the other table already has.
  4. The join silently starts matching on it as well. The result gets narrower or empties out.
  5. Nothing is logged and no error is raised. The query's own text never changed, so the query is not where anybody looks.
The reason this is hard to debug

This is the part worth taking seriously. The blast radius of adding a column is normally zero. With a NATURAL JOIN somewhere in the codebase it is not, and nothing in either the migration or the query connects the two. Audit columns and timestamps are the usual culprits, because they are exactly the names that get added to every table eventually.

USING is immune to this. It names the columns it joins on, so a new shared name changes nothing. So the invisible part of NATURAL JOIN is not a style preference. It is a dependency on something that is allowed to change without anybody consulting us.

So which one should we write?

ONUSINGNATURAL JOIN
Condition is written downYes, in fullYes, by nameNo
Join key in the resultOnce per tableMerged into oneMerged into one
Needs matching column namesNoYesYes
Handles inequalities, functions, several conditionsYesNoNo
Survives somebody adding a columnYesYesNot necessarily
Reasonable defaultYesYes, when the names matchNo

The rule that comes out of that is short. Use USING when the column genuinely has the same name in both tables and we want to say so in one word. Write the ON clause the rest of the time. Reach for NATURAL JOIN in a worksheet if you like, but not in a view, a pipeline, or anything that has to keep working after somebody else edits a table.

Key points

  • ON keeps every column of both tables, so an equi-join returns the join key twice — two columns that are equal in every row by construction.
  • USING (COL) returns the same rows with the key merged into a single, unprefixed column that moves to the front of the result.
  • With SELECT * over these two tables that is 13 columns versus 12 — the one number that captures the whole difference.
  • USING needs the same column name on both sides, and handles nothing but a plain equality. Everything else needs ON.
  • USING is not inner-join-only: it works with LEFT, RIGHT and FULL joins too.
  • After USING there is no second key column to test for NULL, so find unmatched rows with a non-key column from the other table — or use ON.
  • NATURAL JOIN joins on every shared column name and writes none of them down. Its condition lives in the tables, not in the query.
  • On these two tables it is identical to USING (DEPT_ID). On two sides sharing a second name it returned zero rows, with no error.
  • That is the real risk: adding a column to a table can silently change what a NATURAL JOIN elsewhere matches on, while the query text stays exactly the same.
Next in this series

In the next post we will join three or more tables. What happens when a second join is chained onto the result of the first, why the order we write them in starts to matter, and how one outer join in the middle of a chain changes everything after it.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the shared sample-data script for this course, including the empty result — a query that returns no rows is a captured result here, not a description. The EMPLOYEES and DEPARTMENTS data is invented demo data created by that script, and the two-shared-column demonstration uses inline SELECTs so that no real table was modified to produce it. Nothing on this page was timed: no claim is made that NATURAL JOIN is slow, invalid or deprecated — only that its condition is invisible, which is demonstrated rather than asserted. Join behaviour is described from the Snowflake JOIN documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)