Outer Join

Description

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition. (Oracle SQL Language Reference Outer Joins Subsection)

Oracle ANSI syntax

[ query_partition_clause ] [ NATURAL ]
outer_join_type JOIN table_reference
 [ query_partition_clause ]
 [ ON condition
 | USING ( column [, column ]...)
 ]

outer_join_type
{ FULL | LEFT | RIGHT } [ OUTER ]

Oracle also supports the (+) operator that can be used to do outer joins. This operator is added to a column expression in the WHERE clause.

column_expression (+)

Snowflake ANSI syntax

Snowflake also supports the ANSI syntax for OUTER JOINS, just like Oracle. However, the behavior when using the (+) operator might be different depending on the usage. For more information on Snowflake Joins check here.

The Snowflake grammar is one of the following:

SELECT ...
FROM <object_ref1> [
                     {
                       INNER
                       | { LEFT | RIGHT | FULL } [ OUTER ]
                     }
                   ]
                   JOIN <object_ref2>
  [ ON <condition> ]
[ ... ]

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

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

Sample Source Patterns

Order by clause added because the result order may vary between Oracle and Snowflake.

Since the result set is too large, Row Limiting Clause was added. You can remove it to retrieve the entire result set.

Check this section to set up the sample database.

For the following examples, these inserts and alter statements were executed to distinguish better the result for each kind of JOIN:

INSERT INTO hr.regions VALUES (5, 'Oceania');
ALTER TABLE hr.countries DROP CONSTRAINT countr_reg_fk;
INSERT INTO hr.countries VALUES ('--', 'Unknown Country', 0);

1. ANSI syntax

Snowflake fully supports the ANSI syntax for SQL JOINS. The behavior is also the same for both database engines.

Left Outer Join On

Oracle

Query

SELECT * FROM
hr.countries c
LEFT OUTER JOIN hr.regions r ON c.region_id = r.region_id
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;

Result

Snowflake

Query

SELECT * FROM hr.countries c
LEFT OUTER JOIN hr.regions r ON c.region_id = r.region_id
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;

Result

Right Outer Join On

Oracle

Query

SELECT * FROM
hr.countries c
RIGHT OUTER JOIN hr.regions r ON c.region_id = r.region_id
ORDER BY country_id DESC
FETCH FIRST 10 ROWS ONLY;

Result

Snowflake

Query

SELECT * FROM hr.countries c
RIGHT OUTER JOIN hr.regions r ON c.region_id = r.region_id
ORDER BY country_id DESC
FETCH FIRST 10 ROWS ONLY;

Result

Full Outer Join On

Oracle

Query

SELECT * FROM
hr.countries c
FULL OUTER JOIN hr.regions r ON c.region_id = r.region_id
ORDER BY r.region_name DESC, c.country_id
FETCH FIRST 10 ROWS ONLY;

Result

Snowflake

Query

SELECT * FROM hr.countries c
FULL OUTER JOIN hr.regions r ON c.region_id = r.region_id
ORDER BY r.region_name DESC, c.country_id
FETCH FIRST 10 ROWS ONLY;

Result

2. Natural Outer Join

Both Oracle and Snowflake support the Natural Outer Join and they behave the same.

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.)(Snowflake SQL Language Reference JOIN)

Natural Left Outer Join

Oracle

Query

SELECT * FROM
hr.countries c
NATURAL LEFT OUTER JOIN hr.regions r
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;

Result

Snowflake

Query

SELECT * FROM hr.countries c
NATURAL LEFT OUTER JOIN hr.regions r
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;

Result

Natural Right Outer Join

Oracle

Query

SELECT * FROM
hr.countries c
NATURAL RIGHT OUTER JOIN hr.regions r
ORDER BY country_id DESC
FETCH FIRST 10 ROWS ONLY;

Result

Snowflake

Query

SELECT * FROM hr.countries c
NATURAL RIGHT OUTER JOIN hr.regions r
ORDER BY country_id DESC
FETCH FIRST 10 ROWS ONLY;

Result

3. Basic Outer Join with USING

Table columns can be joined using the USING keyword. The results will be the same as a basic OUTER JOIN with the ON keyword.

Left Outer Join Using

Oracle

Query

SELECT * FROM
hr.countries c
LEFT OUTER JOIN hr.regions r USING (region_id)
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;

Result

Snowflake

Query

SELECT * FROM hr.countries c
LEFT OUTER JOIN hr.regions r USING (region_id)
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;

Result

4. (+) Operator

Oracle and Snowflake have a (+) operator that can be used for outer joins too. In some cases, Snowflake may not work properly when using this operator.

For more information regarding this operator in Snowflake, check this.

Left Outer Join with (+) operator

Oracle

Query

SELECT * FROM hr.countries c, hr.regions r
WHERE c.region_id = r.region_id(+)
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;

Result

Snowflake

Query

SELECT *
/*** MSC-WARNING - MSCEWI3081 - SNOWFLAKE NON-ANSI OUTER JOIN SYNTAX HAS SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE. IT COULD CAUSE COMPILATION ERRORS ***/
 FROM hr.countries c, hr.regions r
WHERE c.region_id = r.region_id(+)
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;

Result

Right Outer Join with (+) operator

Oracle

Query

SELECT * FROM hr.countries c, hr.regions r
WHERE c.region_id (+) = r.region_id
ORDER BY country_id DESC
FETCH FIRST 10 ROWS ONLY;

Result

Snowflake

Query

SELECT *
/*** MSC-WARNING - MSCEWI3081 - SNOWFLAKE NON-ANSI OUTER JOIN SYNTAX HAS SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE. IT COULD CAUSE COMPILATION ERRORS ***/
 FROM hr.countries c, hr.regions r
WHERE c.region_id (+) = r.region_id
ORDER BY country_id DESC
FETCH FIRST 10 ROWS ONLY;

Result

Single table joined with multiple tables with (+)

In Oracle, you can join a single table with multiple tables using the (+) operator, however, Snowflake does not support this. Queries with this kind of Outer Joins will be changed to ANSI syntax.

Oracle

Query

SELECT
c.country_id,
c.country_name,
r.region_id,
r.region_name,
l.location_id,
l.street_address,
l.postal_code,
l.city
FROM
hr.countries c, hr.regions r,  hr.locations l
WHERE
c.region_id(+) = r.region_id AND
l.country_id = c.country_id(+)
ORDER BY r.region_id, l.city
FETCH FIRST 10 ROWS ONLY;

Result

Snowflake

Query

SELECT
c.country_id,
c.country_name,
r.region_id,
r.region_name,
l.location_id,
l.street_address,
l.postal_code,
l.city
/*** MSC-WARNING - MSCEWI3003 - NON-ANSI OUTER JOIN SYNTAX CONVERTED TO ANSI SYNTAX ***/
FROM hr.regions r CROSS JOIN hr.locations l LEFT OUTER JOIN hr.countries c ON
c.region_id = r.region_id AND
l.country_id = c.country_id
ORDER BY r.region_id, l.city
FETCH FIRST 10 ROWS ONLY;

Result

Using (+) operator with a column from a not-joined table and a non-column value

In Oracle, you can use the (+) operator with a Column and join it with a value that is not a column from another table. Snowflake can also do this but it will fail if the table of the column was not joined with another table. To solve this issue, the (+) operator is removed from the query when this scenario happens and the result will be the same as in Oracle.

Oracle

Query

SELECT * FROM hr.regions r
WHERE
r.region_name (+) LIKE 'A%'
ORDER BY region_id;

Result

Snowflake

Query

SELECT *
 FROM hr.regions r
WHERE
r.region_name LIKE 'A%'
ORDER BY region_id;

Result

Known issues

For all the unsupported cases, please check the related EWIs to obtain recommendations and possible workarounds.

1. Converted Outer Joins to ANSI syntax might reorder de columns

When a query with a non-ANSI Outer Join is converted to an ANSI Outer Join, it may change the order of the columns in the converted query. To fix this issue, try to select the columns in the specific order required.

Oracle

Query

SELECT
*
FROM
hr.countries c, hr.regions r,  hr.locations l
WHERE
c.region_id(+) = r.region_id AND
l.country_id = c.country_id(+)
ORDER BY r.region_id, l.city
FETCH FIRST 10 ROWS ONLY;

Result

Snowflake

Query

SELECT
*
/*** MSC-WARNING - MSCEWI3003 - NON-ANSI OUTER JOIN SYNTAX CONVERTED TO ANSI SYNTAX ***/
FROM hr.regions r CROSS JOIN hr.locations l LEFT OUTER JOIN hr.countries c ON
c.region_id = r.region_id AND
l.country_id = c.country_id
ORDER BY r.region_id, l.city
FETCH FIRST 10 ROWS ONLY;

Result

2. Outer Join with (+) OR predicate

Oracle supports doing OUTER JOINS with non-column values combined with an OR predicate, however, Snowflake does not. The OR predicate does not work for OUTER JOINS in both Oracle and Snowflake if they are done with another column. For both cases, the whole OR predicate will be removed.

Oracle

Query

SELECT * FROM
hr.countries c, hr.regions r,  hr.locations l
WHERE
c.region_id(+) = r.region_id AND
l.country_id(+) = c.country_id AND
(c.country_name(+) LIKE 'A%' OR c.country_name(+) LIKE 'B%');

Result

Snowflake

Query

SELECT *
/*** MSC-WARNING - MSCEWI3081 - SNOWFLAKE NON-ANSI OUTER JOIN SYNTAX HAS SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE. IT COULD CAUSE COMPILATION ERRORS ***/
 FROM hr.countries c, hr.regions r,  hr.locations l
WHERE
c.region_id(+) = r.region_id AND
l.country_id(+) = c.country_id AND
(
-- ** MSC-ERROR - MSCEWI1059 - NON-ANSI OUTER JOIN IN 'OR' PREDICATE IS NOT SUPPORTED IN SNOWFLAKE **
-- c.country_name(+) LIKE 'A%' OR c.country_name(+) LIKE 'B%'
                                                           );

3. Outer joined between predicate with an interval with multiple tables

Between predicates can be used for non-ANSI OUTER JOINS. In Oracle, columns inside the interval can be outer joined, even if they come from different tables, however, Snowflake does not support this. For these cases, the between predicate will be commented out.

Oracle

Query

SELECT
*
FROM 
hr.countries c, hr.regions r,  hr.locations l WHERE 
l.location_id  BETWEEN r.region_id(+) AND c.region_id(+)
ORDER BY r.region_id, l.city
FETCH FIRST 10 ROWS ONLY;

Result

Snowflake

Query

SELECT
*
/*** MSC-WARNING - MSCEWI3081 - SNOWFLAKE NON-ANSI OUTER JOIN SYNTAX HAS SOME FUNCTIONAL DIFFERENCES COMPARED TO ORACLE. IT COULD CAUSE COMPILATION ERRORS ***/
FROM hr.countries c, hr.regions r, hr.locations l WHERE
-- ** MSC-ERROR - MSCEWI3090 - INVALID NON-ANSI OUTER JOIN BETWEEN PREDICATE CASE FOR SNOWFLAKE. **
--l.location_id  BETWEEN r.region_id(+) AND c.region_id(+)
ORDER BY r.region_id, l.city
FETCH FIRST 10 ROWS ONLY;

Related EWIs

1. MSCEWI1059: Non-Ansi Outer Join is not supported in OR predicate.

2. MSCEWI1060 (deprecated): Non-Ansi Outer Join to multiple tables is not supported in Snowflake.

3. MSCEWI1061 (deprecated): Column does not come from a Non-Ansi Outer Join Expression.

4. MSCEWI3003: Non-ANSI outer join syntax converted to ANSI syntax.

5. MSCEWI3081: Snowflake Non-Ansi Outer Join has some functional differences compared to Oracle.

6. MSCEWI3087: Ordering of the Outer Joins failed.

7. MSCEWI3088: Condition with unknown table name for outer join with (+).

8. MSCEWI3090: Non-Ansi Outer Join has an invalid Between predicate.