JOIN

JOIN is used to combine rows from two tables or other tabular sources (views, table functions, etc.) into one or more new combined rows.

An explanation of the JOIN concept can be found under 'Joins' in the 'SQL Guide'.

The JOIN subclause within a FROM clause defines (explicitly or implicitly) how rows in one table should relate to corresponding rows in another table.

Alternatively, it is also possible to use a WHERE clause to join objects. See the documentation on the WHERE clause.

Syntax

sql

SELECT ...
FROM obj_ref1 [
               {
                INNER | {LEFT | RIGHT | FULL} [OUTER]
                CROSS
               }
              ]
              JOIN obj_ref2
  [ON condition] |  [USING(col_list)]
[ ... ]

Parameters

obj_ref1 and obj_ref2: Object references. This can be a table or another tabular data source (e.g., a view).

JOIN keyword: Indicates that the objects should be joined. The default is an INNER join. See more under JOIN Types.

ON condition: A boolean expression that defines which rows on both sides of the JOIN are considered matching. Example: ON obj_ref2.id_col = obj_ref1.id_col. See more on matching conditions in the documentation on the WHERE clause.

The ON clause is not allowed with CROSS JOIN.

For other JOIN types, the ON clause is optional. If omitted, the result is a Cartesian product (each row from obj_ref1 combined with each row from obj_ref2).

Warning

A Cartesian product can potentially produce a very large number of result rows that do not meaningfully match.

USING(col_list): A list of columns shared by obj_ref1 and obj_ref2, used as join columns. Example: ... obj_ref1 JOIN obj_ref2 USING (key_column) is equivalent to ... obj_ref1 JOIN obj_ref2 ON obj_ref2.key_column = obj_ref1.key_column.

When * is used in the SELECT statement, the columns listed in col_list appear only once in the result. In general, columns are returned in the following order:

col_list (columns in the USING clause) in the order listed
Remaining columns of the left object (not in USING)
Remaining columns of the right object (not in USING)

`JOIN` Types

INNER, LEFT, RIGHT, FULL, OUTER, and CROSS define the type of join. Example: 'object1 join_type object2' → obj_ref1 INNER JOIN obj_ref2 ON ....

INNER JOIN

A INNER JOIN B

For each row in A that satisfies the ON clause, a row from B is produced.

If an INNER join is used without an ON clause, the result is equivalent to a CROSS join, which is a Cartesian product (each row from A paired with each row from B). An inner join can also be written using a comma operator or a WHERE clause. An INNER JOIN without a comma and without a WHERE clause is equivalent to a CROSS JOIN.

LEFT OUTER JOIN

A LEFT OUTER JOIN B

The result of the INNER join is extended to include every row from A that has no matching row in B. Columns from B are returned as NULL.

RIGHT OUTER JOIN

A RIGHT OUTER JOIN B

The result of the INNER join is extended to include every row from B that has no matching row in A. Columns from A are returned as NULL.

FULL OUTER JOIN

A FULL OUTER JOIN B

Returns all rows from A and B that match the ON condition, plus one row for each row in A and B that does not match. Rows from A without matches in B return NULL for the B columns, and vice versa.

CROSS JOIN

A CROSS JOIN B

Produces one row for every possible combination of rows from A and B, forming a Cartesian product. The output consists of all columns from A followed by all columns from B.

The ON clause is not allowed with a CROSS JOIN.

However, a WHERE clause can be used to filter the result.

The WHERE clause may also be used to filter results in a way that mimics an INNER JOIN or OUTER JOIN.

Important

Using JOIN in combination with WHERE and FROM ... ON ... clauses may produce different results in certain cases. These methods are therefore not interchangeable.

Example 1 | `INNER JOIN`

sql

WITH
t1 AS (SELECT 1 AS col1, 2 AS col2
       UNION ALL
       SELECT 2 AS col1, 3 AS col2
       UNION ALL 
       SELECT 3 AS col1, 4 AS col2
       UNION ALL 
       SELECT 4 AS col1, 5 AS col2
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 3 AS col1, 50 AS col2
      UNION ALL 
      SELECT 5 AS col1, 60 AS col2
      )
SELECT t1.col1 AS t1_col1, t2.col1 AS t2_col1, t1.col2 AS t1_col2, t2.col2 AS t2_col2
    FROM t1 INNER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1, 2;

+---------+---------+---------+---------+
| T1_COL1 | T2_COL1 | T1_COL2 | T2_COL2 |
+---------+---------+---------+---------+
| 1       | 1       | 2       | 20      |
| 2       | 2       | 3       | 30      |
| 3       | 3       | 4       | 50      |
+---------+---------+---------+---------+

Example 2 | `LEFT OUTER JOIN`

sql

WITH
t1 AS (SELECT 1 AS col1, 2 AS col2
       UNION ALL
       SELECT 2 AS col1, 3 AS col2
       UNION ALL 
       SELECT 3 AS col1, 4 AS col2
       UNION ALL 
       SELECT 4 AS col1, 5 AS col2
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 3 AS col1, 50 AS col2
      UNION ALL 
      SELECT 5 AS col1, 60 AS col2
      )
SELECT t1.col1 AS t1_col1, t2.col1 AS t2_col1, t1.col2 AS t1_col2, t2.col2 AS t2_col2
    FROM t1 LEFT OUTER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1, 2;

+---------+---------+---------+---------+
| T1_COL1 | T2_COL1 | T1_COL2 | T2_COL2 |
+---------+---------+---------+---------+
| 1       | 1       | 2       | 20      |
| 2       | 2       | 3       | 30      |
| 3       | 3       | 4       | 50      |
| 4       | NULL    | 5       | NULL    |
+---------+---------+---------+---------+

Example 3 | `RIGHT OUTER JOIN`

sql

WITH
t1 AS (SELECT 1 AS col1, 2 AS col2
       UNION ALL
       SELECT 2 AS col1, 3 AS col2
       UNION ALL 
       SELECT 3 AS col1, 4 AS col2
       UNION ALL 
       SELECT 4 AS col1, 5 AS col2
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 3 AS col1, 50 AS col2
      UNION ALL 
      SELECT 5 AS col1, 60 AS col2
      )
SELECT t1.col1 AS t1_col1, t2.col1 AS t2_col1, t1.col2 AS t1_col2, t2.col2 AS t2_col2
    FROM t1 RIGHT OUTER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1, 2;

+---------+---------+---------+---------+
| T1_COL1 | T2_COL1 | T1_COL2 | T2_COL2 |
+---------+---------+---------+---------+
| 1       | 1       | 2       | 20      |
| 2       | 2       | 3       | 30      |
| 3       | 3       | 4       | 50      |
| NULL    | 5       | NULL    | 60      |
+---------+---------+---------+---------+

Example 4 | `FULL OUTER JOIN`

sql

WITH
t1 AS (SELECT 1 AS col1, 2 AS col2
       UNION ALL
       SELECT 2 AS col1, 3 AS col2
       UNION ALL 
       SELECT 3 AS col1, 4 AS col2
       UNION ALL 
       SELECT 4 AS col1, 5 AS col2
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 3 AS col1, 50 AS col2
      UNION ALL 
      SELECT 5 AS col1, 60 AS col2
      )
SELECT t1.col1 AS t1_col1, t2.col1 AS t2_col1, t1.col2 AS t1_col2, t2.col2 AS t2_col2
    FROM t1 FULL OUTER JOIN t2
        ON t2.col1 = t1.col1
    ORDER BY 1, 2;

+---------+---------+---------+---------+
| T1_COL1 | T2_COL1 | T1_COL2 | T2_COL2 |
+---------+---------+---------+---------+
| 1       | 1       | 2       | 20      |
| 2       | 2       | 3       | 30      |
| 3       | 3       | 4       | 50      |
| 4       | NULL    | 5       | NULL    |
| NULL    | 5       | NULL    | 60      |
+---------+---------+---------+---------+

Example 5 | `CROSS JOIN`

Also called a "Cartesian product." The ON clause is not permitted here.

sql

WITH
t1 AS (SELECT 1 AS col1, 2 AS col2
       UNION ALL
       SELECT 2 AS col1, 3 AS col2
       UNION ALL 
       SELECT 3 AS col1, 4 AS col2
       UNION ALL 
       SELECT 4 AS col1, 5 AS col2
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 3 AS col1, 50 AS col2
      UNION ALL 
      SELECT 5 AS col1, 60 AS col2
      )
SELECT t1.col1 AS t1_col1, t2.col1 AS t2_col1, t1.col2 AS t1_col2, t2.col2 AS t2_col2
    FROM t1 CROSS JOIN t2
    ORDER BY 1, 2;

+---------+---------+---------+---------+
| T1_COL1 | T2_COL1 | T1_COL2 | T2_COL2 |
+---------+---------+---------+---------+
| 1       | 1       | 2       | 20      |
| 1       | 2       | 2       | 30      |
| 1       | 3       | 2       | 50      |
| 1       | 5       | 2       | 60      |
| 2       | 1       | 3       | 20      |
| 2       | 2       | 3       | 30      |
| 2       | 3       | 3       | 50      |
| 2       | 5       | 3       | 60      |
| 3       | 1       | 4       | 20      |
| 3       | 2       | 4       | 30      |
| 3       | 3       | 4       | 50      |
| 3       | 5       | 4       | 60      |
| 4       | 1       | 5       | 20      |
| 4       | 2       | 5       | 30      |
| 4       | 3       | 5       | 50      |
| 4       | 5       | 5       | 60      |
+---------+---------+---------+---------+

Example 6 | `CROSS JOIN` with `WHERE` clause

sql

WITH
t1 AS (SELECT 1 AS col1, 2 AS col2
       UNION ALL
       SELECT 2 AS col1, 3 AS col2
       UNION ALL 
       SELECT 3 AS col1, 4 AS col2
       UNION ALL 
       SELECT 4 AS col1, 5 AS col2
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 3 AS col1, 50 AS col2
      UNION ALL 
      SELECT 5 AS col1, 60 AS col2
      )
SELECT t1.col1 AS t1_col1, t2.col1 AS t2_col1, t1.col2 AS t1_col2, t2.col2 AS t2_col2
    FROM t1 CROSS JOIN t2
    WHERE t2.col2 BETWEEN 30 AND 50
    ORDER BY 1, 2;

+---------+---------+---------+---------+
| T1_COL1 | T2_COL1 | T1_COL2 | T2_COL2 |
+---------+---------+---------+---------+
| 1       | 2       | 2       | 30      |
| 1       | 3       | 2       | 50      |
| 2       | 2       | 3       | 30      |
| 2       | 3       | 3       | 50      |
| 3       | 2       | 4       | 30      |
| 3       | 3       | 4       | 50      |
| 4       | 2       | 5       | 30      |
| 4       | 3       | 5       | 50      |
+---------+---------+---------+---------+

Example 7 | Multiple `JOIN` statements in the `FROM` clause.

In the following example, three objects are joined together.

sql

WITH
t1 AS (SELECT 1 AS col1, 2 AS col12
       UNION ALL
       SELECT 2 AS col1, 3 AS col12
       UNION ALL 
       SELECT 3 AS col1, 4 AS col12
       UNION ALL 
       SELECT 4 AS col1, 5 AS col12
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 3 AS col1, 50 AS col2
      UNION ALL 
      SELECT 5 AS col1, 60 AS col2
      ),
t3 AS (SELECT 1 AS col1, 320 AS col32
      UNION ALL 
      SELECT 3 AS col1, 350 AS col32
      UNION ALL 
      SELECT 5 AS col1, 360 AS col32
      UNION ALL 
      SELECT 6 AS col1, 370 AS col32
      )
SELECT t1.col1  AS t1_col1, 
       t2.col1  AS t2_col1,
       t3.col1  AS t3_col1,
       t1.col12 AS t1_col2,
       t2.col2  AS t2_col2,
       t3.col32 AS t3_col2
    FROM t1 
         LEFT OUTER JOIN t2 ON (t1.col1 = t2.col1)
         RIGHT OUTER JOIN t3 ON (t3.col1 = t2.col1)
    ORDER BY 1, 3;

+---------+---------+---------+---------+---------+---------+
| T1_COL1 | T2_COL1 | T3_COL1 | T1_COL2 | T2_COL2 | T3_COL2 |
+---------+---------+---------+---------+---------+---------+
| 1       | 1       | 1       | 2       | 20      | 320     |
| 3       | 3       | 3       | 4       | 50      | 350     |
| NULL    | NULL    | 5       | NULL    | NULL    | 360     |
| NULL    | NULL    | 6       | NULL    | NULL    | 370     |
+---------+---------+---------+---------+---------+---------+

Example 8 | `USING` keyword.

ISO 9075 compliant use of USING (projection list is specified with the * operator).

In this case, the column 'col1' appears only once in the result.

sql

WITH
t1 AS (SELECT 1 AS col1, 2 AS col12
       UNION ALL
       SELECT 2 AS col1, 3 AS col12
       UNION ALL 
       SELECT 3 AS col1, 4 AS col12
       UNION ALL 
       SELECT 4 AS col1, 5 AS col12
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 3 AS col1, 50 AS col2
      UNION ALL 
      SELECT 5 AS col1, 60 AS col2
      )
SELECT *
    FROM t1 
         LEFT JOIN t2 USING(col1);

+------+-------+------+
| COL1 | COL12 | COL2 |
+------+-------+------+
| 1    | 2     | 20   |
| 2    | 3     | 30   |
| 3    | 4     | 50   |
| 4    | 5     | NULL |
+------+-------+------+

Not ISO 9075 compliant use of USING (projection list is specified differently than with the * operator).

sql

WITH
t1 AS (SELECT 1 AS col1, 2 AS col12
       UNION ALL
       SELECT 2 AS col1, 3 AS col12
       UNION ALL 
       SELECT 3 AS col1, 4 AS col12
       UNION ALL 
       SELECT 4 AS col1, 5 AS col12
      ), 
t2 AS (SELECT 1 AS col1, 20 AS col2
      UNION ALL 
      SELECT 2 AS col1, 30 AS col2
      UNION ALL 
      SELECT 3 AS col1, 50 AS col2
      UNION ALL 
      SELECT 5 AS col1, 60 AS col2
      )
SELECT t1.col1 AS join_col_t1, t2.col1 AS join_col_t2
    FROM t1 
         LEFT JOIN t2 USING(col1);

Error: column part of `USING` clause cannot have qualifier
 at my_folder.tests (1:1)

Integrations

Connections

Sections

Editors

Release Management

SQL Command Reference

Query Syntax

Query Operators

General DDL

General DML

Function Reference

Scripting Reference

Information Schema

JOIN ​

Syntax ​

Parameters ​

JOIN Types ​

INNER JOIN ​

LEFT OUTER JOIN ​

RIGHT OUTER JOIN ​

FULL OUTER JOIN ​

CROSS JOIN ​

Example 1 | INNER JOIN ​

Example 2 | LEFT OUTER JOIN ​

Example 3 | RIGHT OUTER JOIN ​

Example 4 | FULL OUTER JOIN ​

Example 5 | CROSS JOIN ​

Example 6 | CROSS JOIN with WHERE clause ​

Example 7 | Multiple JOIN statements in the FROM clause. ​

Example 8 | USING keyword. ​