Categories:

Query syntax

JOIN¶

A JOIN operation combines rows from two tables — or other table-like sources, such as views or table functions — to create a new combined row that can be used in the query. For a conceptual explanation of joins, see Working with joins.

This topic describes how to use the JOIN subclause in the FROM clause. The JOIN subclause specifies, explicitly or implicitly, how to relate rows in one table to the corresponding rows in the other table. You can also use the ASOF JOIN subclause, which is used to join time-series data on timestamp columns when their values closely follow each other, precede each other, or match exactly.

Although the recommended way to join tables is to use JOIN with the ON subclause of the FROM clause, an alternative way to join tables is to use the WHERE clause. For details, see the documentation for the WHERE clause.

Syntax¶

Use one of the following:

SELECT ...
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                     [ DIRECTED ]
                   ]
                   JOIN <object_ref2>
  [ ON <condition> ]
  [ { FIRST | LAST } ( <alias> ) ]
[ ... ]

SELECT *
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                     [ DIRECTED ]
                   ]
                   JOIN <object_ref2>
  [ USING( <column_list> ) ]
  [ { FIRST | LAST } ( <alias> ) ]
[ ... ]

SELECT ...
FROM <object_ref1> [
                     {
                       NATURAL [
                                 {
                                   INNER
                                   | { LEFT | RIGHT | FULL } [ OUTER ]
                                 }
                                 [ DIRECTED ]
                               ]
                       | CROSS  [ DIRECTED ]
                     }
                   ]
                   JOIN <object_ref2>
[ ... ]

Parameters¶

object_ref1 and object_ref2

Each object reference is a table or table-like data source.

JOIN

Use the JOIN keyword to specify that the tables should be joined. Combine JOIN with other join-related keywords — for example, INNER or OUTER — to specify the type of join.

The semantics of joins are as follows (for brevity, this topic uses o1 and o2 for object_ref1 and object_ref2, respectively).

Join Type	Semantics
o1 INNER JOIN o2	For each row of o1, a row is produced for each row of o2 that matches according to the ON condition subclause. (You can also use a comma to specify an inner join. For an example, see the examples section.) If you use INNER JOIN without the ON clause, or if you use a comma without a WHERE clause, the result is the same as using CROSS JOIN: a Cartesian product; every row of o1 paired with every row of o2.
o1 LEFT OUTER JOIN o2	The result of the inner join is augmented with a row for each row of o1 that has no matches in o2. The result columns referencing o2 contain null.
o1 RIGHT OUTER JOIN o2	The result of the inner join is augmented with a row for each row of o2 that has no matches in o1. The result columns referencing o1 contain null.
o1 FULL OUTER JOIN o2	Returns all joined rows, plus one row for each unmatched left side row (extended with nulls on the right), plus one row for each unmatched right side row (extended with nulls on the left).
o1 CROSS JOIN o2	For every possible combination of rows from o1 and o2 (that is, Cartesian product), the joined table contains a row consisting of all columns in o1 followed by all columns in o2. A CROSS JOIN can’t be combined with an ON condition clause. However, you can use a WHERE clause to filter the results.
o1 NATURAL JOIN o2	A NATURAL JOIN is identical to an explicit JOIN on the common columns of the two tables, except that the common columns are included only once in the output. (A natural join assumes that columns with the same name, but in different tables, contain corresponding data.) For examples, see the examples section. A NATURAL JOIN can be combined with an OUTER JOIN. A NATURAL JOIN can’t be combined with an ON condition clause because the JOIN condition is already implied. However, you can use a WHERE clause to filter the results.

Expand

Show lessSee more

The DIRECTED keyword specifies a directed join, which enforces the join order of the tables. The first, or left, table is scanned before the second, or right, table. For example, o1 INNER DIRECTED JOIN o2 scans the o1 table before the o2 table. Directed joins are useful in the following situations:

• You are migrating workloads into Snowflake that have join order directives.
• You want to improve performance by scanning join tables in a specific order.

To choose which table is scanned first without rewriting the order in which you write the tables, add a read-order clause after the ON or USING clause. For details, see the read-order clause.

Default: INNER JOIN

If the word JOIN is used without specifying INNER or OUTER, then the JOIN is an inner join.

If the DIRECTED keyword is added, the join type (for example, INNER, LEFT, RIGHT, or FULL) is required.

See also:

• LATERAL
• ASOF JOIN

ON condition

A Boolean expression that defines the rows from the two sides of the JOIN that are considered to match, for example:

Copy code

ON object_ref2.id_number = object_ref1.id_number

Conditions are discussed in more detail in the WHERE clause documentation.

The ON clause is prohibited for CROSS JOIN.

The ON clause is unnecessary, and prohibited, for NATURAL JOIN because the join columns are implied.

For other joins, the ON clause is optional. However, omitting the ON clause results in a Cartesian product; every row of object_ref1 paired with every row of object_ref2. A Cartesian product can produce a very large volume of output, almost all of which consists of pairs of rows that aren’t actually related, which consumes a lot of resources and is often a user error.

USING( column_list )

A list of columns in common between the two tables being joined. These columns are used as the join columns. The columns must have the same name and meaning in each of the tables being joined.

For example, suppose that the SQL statement contains:

Copy code

... o1 JOIN o2
    USING (key_column)

In the simple case, this would be equivalent to:

Copy code

... o1 JOIN o2
    ON o2.key_column = o1.key_column

In the standard JOIN syntax, the projection list (the list of columns and other expressions after the SELECT keyword) is *. This causes the query to return the key_column exactly once. The columns are returned in the following order:

• The columns in the USING clause in the order specified.
• The left table columns not specified in the USING clause.
• The right table columns not specified in the USING clause.

For examples of standard and nonstandard usage, see the examples section.

{ FIRST | LAST } ( alias )

Specifies which side of a directed join is scanned first. This clause is called the read-order clause. FIRST( alias ) names the table that is scanned first, and LAST( alias ) names the table that is scanned last.

A join has two sides, so the two forms are interchangeable. For o1 INNER DIRECTED JOIN o2, specifying FIRST(o2) is equivalent to specifying LAST(o1). Use whichever form is clearer. You can specify only one read-order clause for a single join.

The alias must name one of the two tables that the join combines. If the table doesn’t have an explicit alias, use the table name.

This clause is useful when you want to set the scan order of one join inside a chain of joins. Without it, you must restructure the query: move a table to the front of the FROM clause, parenthesize the remaining join, and move the join condition to change which side is scanned first. For an example, see Set the scan order for a directed join.

For restrictions, see the usage notes.

Default: The left table is scanned first.

Usage notes¶

• The following restrictions apply to table functions other than SQL UDTFs:

  • You can’t specify the ON, USING, or NATURAL JOIN clause in a lateral table function, other than a SQL UDTF.

    For example, the following syntax is not allowed:

    Copy code

    SELECT ... FROM my_table
      JOIN TABLE(FLATTEN(input=>[col_a]))
      ON ... ;
    
    Copy code

    SELECT ... FROM my_table
      INNER JOIN TABLE(FLATTEN(input=>[col_a]))
      ON ... ;
    
    Copy code

    SELECT ... FROM my_table
      JOIN TABLE(my_js_udtf(col_a))
      ON ... ;
    
    Copy code

    SELECT ... FROM my_table
      INNER JOIN TABLE(my_js_udtf(col_a))
      ON ... ;
    

  • You can’t specify the ON, USING, or NATURAL JOIN clause in an outer lateral join to a table function, other than a SQL UDTF.

    For example, the following syntax is not allowed:

    Copy code

    SELECT ... FROM my_table
      LEFT JOIN TABLE(FLATTEN(input=>[a]))
      ON ... ;
    
    Copy code

    SELECT ... FROM my_table
      FULL JOIN TABLE(FLATTEN(input=>[a]))
      ON ... ;
    
    Copy code

    SELECT ... FROM my_table
      LEFT JOIN TABLE(my_js_udtf(a))
      ON ... ;
    
    Copy code

    SELECT ... FROM my_table
      FULL JOIN TABLE(my_js_udtf(a))
      ON ... ;
    

    Using this syntax results in the following error:

    000002 (0A000): Unsupported feature
      'lateral table function called with OUTER JOIN syntax
       or a join predicate (ON clause)'
    

  • These restrictions don’t apply if you are using a comma, rather than a JOIN keyword:

    Copy code

    SELECT ... FROM my_table,
      TABLE(FLATTEN(input=>[col_a]))
      ON ... ;
    

• The following restrictions apply to the read-order clause ({ FIRST | LAST } ( alias )):

  • The join must specify the DIRECTED keyword and an ON or USING clause. Using FIRST or LAST on a join that isn’t directed returns the following error:

    001867 (42601): SQL compilation error: FIRST or LAST is only valid on a DIRECTED JOIN.
    

  • The clause isn’t supported for CROSS DIRECTED JOIN, NATURAL ... DIRECTED JOIN, or a directed join that omits the ON and USING clauses. In those positions, FIRST or LAST is parsed as an alias for the second table rather than as a read-order clause. If that table has no alias of its own, the statement succeeds and the requested scan order is silently ignored; if it already has one, the statement fails with a syntax error. Add an ON or USING clause to set the scan order.

  • The alias must name one of the two tables that the join combines. When one side of the join is itself a join (for example, the accumulated result of earlier joins in a chain), that side can’t be named, so name the table on the other side instead. Naming anything else returns the following error:

    001868 (42601): SQL compilation error: FIRST or LAST requires two direct operands, and
    alias 'my_alias' must name one of them.
    

  • You can specify at most one read-order clause for a single join, but you can specify a read-order clause on more than one join in the same query.

  • FIRST and LAST aren’t reserved keywords. You can continue to use them as table names, column names, and aliases.

Examples¶

Many of the JOIN examples use two tables: t1 and t2. Create these tables and insert data:

CREATE TABLE t1 (col1 INTEGER);

INSERT INTO t1 (col1) VALUES
  (2),
  (3),
  (4);

CREATE TABLE t2 (col1 INTEGER);

INSERT INTO t2 (col1) VALUES
  (1),
  (2),
  (2),
  (3);

The following examples run queries with joins:

• Run a query with an inner join
• Run a query with a left outer join
• Run a query with a right outer join
• Run a query with a full outer join
• Run a query with a cross join
• Run a query with a natural join
• Run a query that combines joins in the FROM clause
• Run queries with joins that use the USING clause
• Set the scan order for a directed join

Run a query with an inner join¶

The following example runs a query with an inner join:

SELECT t1.col1, t2.col1
  FROM t1 INNER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Run the same query with an inner-directed join to enforce the join order so that the left table is scanned first:

SELECT t1.col1, t2.col1
  FROM t1 INNER DIRECTED JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Run a query with a left outer join¶

The following example runs a query with a left outer join:

SELECT t1.col1, t2.col1
  FROM t1 LEFT OUTER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

In the output, there is a NULL value for the row in table t1 that doesn’t have a matching row in table t2:

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
+------+------+

Run a query with a right outer join¶

The following example runs a query with a right outer join:

SELECT t1.col1, t2.col1
  FROM t1 RIGHT OUTER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

In the output, there is a NULL value for the row in table t1 that doesn’t have a matching row in table t2.

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
| NULL |    1 |
+------+------+

Run a query with a full outer join¶

The following example runs a query with a full outer join:

SELECT t1.col1, t2.col1
  FROM t1 FULL OUTER JOIN t2
    ON t2.col1 = t1.col1
  ORDER BY 1,2;

Each table has a row that doesn’t have a matching row in the other table, so the output contains two rows with NULL values:

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
|    4 | NULL |
| NULL |    1 |
+------+------+

Run a query with a cross join¶

The following example runs a query with a cross join:

SELECT t1.col1, t2.col1
  FROM t1 CROSS JOIN t2
  ORDER BY 1, 2;

The output shows that the query produces a Cartesian product:

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    1 |
|    2 |    2 |
|    2 |    2 |
|    2 |    3 |
|    3 |    1 |
|    3 |    2 |
|    3 |    2 |
|    3 |    3 |
|    4 |    1 |
|    4 |    2 |
|    4 |    2 |
|    4 |    3 |
+------+------+

A cross join can be filtered by a WHERE clause, as shown in the following example:

SELECT t1.col1, t2.col1
  FROM t1 CROSS JOIN t2
  WHERE t2.col1 = t1.col1
  ORDER BY 1, 2;

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Run a query with a natural join¶

The following example shows a query with a natural join. First, create two tables and insert data:

CREATE OR REPLACE TABLE d1 (
  id NUMBER,
  name VARCHAR);

INSERT INTO d1 (id, name) VALUES
  (1,'a'),
  (2,'b'),
  (4,'c');

CREATE OR REPLACE TABLE d2 (
  id NUMBER,
  value VARCHAR);

INSERT INTO d2 (id, value) VALUES
  (1,'xx'),
  (2,'yy'),
  (5,'zz');

Run a query with a natural join:

SELECT *
  FROM d1 NATURAL INNER JOIN d2
  ORDER BY id;

The output shows that a natural join produces the same output as the corresponding inner join, except that the output doesn’t include a second copy of the join column:

+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
+----+------+-------+

The following example shows that you can combine natural joins with outer joins:

SELECT *
  FROM d1 NATURAL FULL OUTER JOIN d2
  ORDER BY id;

+----+------+-------+
| ID | NAME | VALUE |
|----+------+-------|
|  1 | a    | xx    |
|  2 | b    | yy    |
|  4 | c    | NULL  |
|  5 | NULL | zz    |
+----+------+-------+

Run a query that combines joins in the FROM clause¶

You can combine in the FROM clause. Create a third table:

CREATE TABLE t3 (col1 INTEGER);

INSERT INTO t3 (col1) VALUES
  (2),
  (6);

Run a query that chains together two joins in the FROM clause:

SELECT t1.*, t2.*, t3.*
  FROM t1
    LEFT OUTER JOIN t2 ON (t1.col1 = t2.col1)
    RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1)
  ORDER BY t1.col1;

+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
| NULL | NULL |    6 |
+------+------+------+

In such a query, the results are determined based on the joins taking place from left to right, although the optimizer might reorder the joins if a different join order produces the same result. If the right outer join is meant to take place before the left outer join, then write the query in the following way:

SELECT t1.*, t2.*, t3.*
FROM t1
  LEFT OUTER JOIN
    (t2 RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1))
  ON (t1.col1 = t2.col1)
ORDER BY t1.col1;

+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
|    3 | NULL | NULL |
|    4 | NULL | NULL |
+------+------+------+

Run queries with joins that use the USING clause¶

The next two examples show standard (ISO 9075) and nonstandard usage of the USING clause. Both are supported by Snowflake.

This first example shows standard usage. Specifically, the projection list contains exactly *:

WITH
  l AS (
       SELECT 'a' AS userid
       ),
  r AS (
       SELECT 'b' AS userid
       )
SELECT *
  FROM l LEFT JOIN r USING(userid);

Even though the example query joins two tables, and each table has one column, and the query asks for all columns, the output contains one column, not two:

+--------+
| USERID |
|--------|
| a      |
+--------+

The following example shows nonstandard usage. The projection list contains something other than *:

WITH
  l AS (
       SELECT 'a' AS userid
     ),
  r AS (
       SELECT 'b' AS userid
       )
SELECT l.userid as UI_L,
       r.userid as UI_R
  FROM l LEFT JOIN r USING(userid);

The output contains two columns, and the second column contains either a value from the second table or NULL:

+------+------+
| UI_L | UI_R |
|------+------|
| a    | NULL |
+------+------+

Set the scan order for a directed join¶

By default, a directed join scans the left table first. The following example uses FIRST to scan the right table (t2) first instead. The results are the same as for the corresponding inner join; only the scan order changes:

SELECT t1.col1, t2.col1
  FROM t1 INNER DIRECTED JOIN t2
    ON t2.col1 = t1.col1 FIRST(t2)
  ORDER BY 1,2;

+------+------+
| COL1 | COL1 |
|------+------|
|    2 |    2 |
|    2 |    2 |
|    3 |    3 |
+------+------+

Because a join has two sides, LAST(t1) is equivalent to FIRST(t2) in this query.

The read-order clause is most useful when you want to set the scan order of one join in a chain of joins. Chained joins are combined from left to right, so in the following query the second join combines the result of t1 INNER JOIN t2 with t3. Adding FIRST(t3) scans t3 first in that join, without changing anything else about the query:

SELECT t1.col1, t2.col1, t3.col1
  FROM t1
    INNER JOIN t2 ON t2.col1 = t1.col1
    INNER DIRECTED JOIN t3 ON t3.col1 = t2.col1 FIRST(t3)
  ORDER BY 1,2,3;

+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
+------+------+------+

You can’t use LAST to express the same intent in this query, because the other side of that join is the result of t1 INNER JOIN t2, which doesn’t have an alias that you can name.

To scan t3 first without the read-order clause, you must restructure the query: move t3 to the front of the FROM clause, parenthesize the other join, and move the join condition to the outer join. The following query is equivalent to the preceding one:

SELECT t1.col1, t2.col1, t3.col1
  FROM t3
    INNER DIRECTED JOIN (t1 INNER JOIN t2 ON t2.col1 = t1.col1)
      ON t3.col1 = t2.col1
  ORDER BY 1,2,3;

+------+------+------+
| COL1 | COL1 | COL1 |
|------+------+------|
|    2 |    2 |    2 |
|    2 |    2 |    2 |
+------+------+------+