Outer Join

Some parts in the output code are omitted for clarity reasons.

Description

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and returns some or all 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 the same for both database engines.

Left Outer Join On

Oracle

Query

IN -> Oracle_01.sql

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

COUNTRY_ID|COUNTRY_NAME   |REGION_ID|REGION_ID|REGION_NAME|
----------+---------------+---------+---------+-----------+
--        |Unknown Country|        0|         |           |
AR        |Argentina      |        2|        2|Americas   |
AU        |Australia      |        3|        3|Asia       |
BE        |Belgium        |        1|        1|Europe     |
BR        |Brazil         |        2|        2|Americas   |
CA        |Canada         |        2|        2|Americas   |
CH        |Switzerland    |        1|        1|Europe     |
CN        |China          |        3|        3|Asia       |
DE        |Germany        |        1|        1|Europe     |
DK        |Denmark        |        1|        1|Europe     |

Snowflake

Query

OUT -> Oracle_01.sql

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

COUNTRY_ID|COUNTRY_NAME   |REGION_ID            |REGION_ID            |REGION_NAME|
----------+---------------+---------------------+---------------------+-----------+
--        |Unknown Country|0.0000000000000000000|                     |           |
AR        |Argentina      |2.0000000000000000000|2.0000000000000000000|Americas   |
AU        |Australia      |3.0000000000000000000|3.0000000000000000000|Asia       |
BE        |Belgium        |1.0000000000000000000|1.0000000000000000000|Europe     |
BR        |Brazil         |2.0000000000000000000|2.0000000000000000000|Americas   |
CA        |Canada         |2.0000000000000000000|2.0000000000000000000|Americas   |
CH        |Switzerland    |1.0000000000000000000|1.0000000000000000000|Europe     |
CN        |China          |3.0000000000000000000|3.0000000000000000000|Asia       |
DE        |Germany        |1.0000000000000000000|1.0000000000000000000|Europe     |
DK        |Denmark        |1.0000000000000000000|1.0000000000000000000|Europe     |

Right Outer Join On

Oracle