SnowConvert - Translation Spec
  • For Teradata
  • Translation Reference
    • Teradata
      • Scripts to Snowflake SQL Translation Reference
        • COMMON STATEMENTS
          • ERROR HANDLING
          • EXIT or QUIT
          • GOTO
          • IF... THEN...
        • BTEQ
        • MLOAD
          • Import
      • Scripts To Python Translation Reference
        • BTEQ
          • REPEAT
          • USING REQUEST MODIFIER
        • FLOAD
          • BEGIN LOADING
        • MLOAD
          • BEGIN MLOAD
        • TPT
        • SnowConvert Scripts Helpers
          • Technical Documentation
      • SQL Translation Reference
        • Built-in Functions
          • CURRENT_TIMESTAMP
          • Cast to DATE using { }
          • Cast to INTERVAL datatype
          • COALESCE
          • DAYNUMBER_OF_MONTH
          • FROM_BYTES
          • GETQUERYBANDVALUE
          • JSON_CHECK
          • JSON_EXTRACT
          • JSON_TABLE
          • NEW JSON
          • NVP
          • OVERLAPS
          • PIVOT
          • P_INTERSECT
          • RANK
          • Regex functions
          • STRTOK_SPLIT_TO_TABLE
          • SUBSTRING
          • TD_UNPIVOT
          • TO_CHAR
          • XMLAGG
        • Database DBC
        • Data Types
        • DDL
          • Tables
            • WITH DEFAULT
          • Index
          • Views
        • DML
          • Delete Statement
          • Insert Statement
            • LOGGING ERRORS
          • Select Statement
            • Expand On Clause
            • Normalize
            • Reset When
          • Set Operators
          • Update Statement
          • With Modifier
        • SnowConvert Procedures Helpers
          • Cursor Helper
          • Exec Helper
          • Functional Equivalence Helpers
          • Into Helper
      • Teradata to JavaScript Translation Reference
        • GET DIAGNOSTICS EXCEPTION
        • Macros
        • Procedures
      • Teradata to Snowflake Scripting Translation Reference
        • ABORT and ROLLBACK
        • BEGIN END
        • CASE
        • CREATE MACRO
        • CREATE PROCEDURE
        • CURSOR
        • DECLARE
        • DECLARE CONDITION HANDLER
        • DML and DDL Objects
        • EXCEPTION HANDLERS
        • EXECUTE IMMEDIATE
        • EXECUTE/EXEC
        • FUNCTION OPTIONS OR DATA ACCESS
        • GET DIAGNOSTICS EXCEPTION
        • IF
        • LOCKING FOR ACCESS
        • LOOP
        • OUTPUT PARAMETERS
        • PREPARE
        • REPEAT
        • SET
        • SYSTEM_DEFINED
        • WHILE
    • Oracle
      • Sample data
      • Basic Elements of Oracle SQL
        • Data Types
          • Oracle Built-in Data Types
            • Character Data Types
              • CHAR Data type
              • NCHAR Data Type
              • VARCHAR2 Data Type
              • VARCHAR Data Type
              • NVARCHAR2 Data Type
            • Numeric Data Types
              • NUMBER Data Type
              • FLOAT Data Type
              • Floating-Point Numbers
                • BINARY_FLOAT
                • BINARY_DOUBLE
            • LONG Data Type
            • Datetime and Interval Data Types
              • DATE Data Type
              • TIMESTAMP Data Type
              • TIMESTAMP WITH TIME ZONE Data Type
              • TIMESTAMP WITH LOCAL TIME ZONE Data Type
              • INTERVAL YEAR TO MONTH Data Type
              • INTERVAL DAY TO SECOND Data Type
              • Datetime Arithmetic
                • Interval UDFs vs Snowflake native interval operation
            • LOB Data Types
              • BFILE Data Type
              • BLOB Data Type
              • CLOB Data Type
              • NCLOB Data type
            • JSON Data Type
            • Extended Data Types
            • RAW and LONG RAW Data types
            • PL SQL Data Types
              • PLS_INTEGER Data Type
              • BINARY_INTEGER Data Type
          • Rowid Data Type
            • ROWID DataType
            • UROWID Data Type
          • ANSI Data Types
          • User-Defined Types
            • REF Data Types
          • Any Types
            • ANYTYPE
            • ANYDATA
            • ANYDATASET
          • XML Types
            • XMLType
            • URI Data Types
              • HTTPURIType
              • XDBURIType
              • DBURIType
            • URIFactory Package
          • Spatial Types
            • SDO_GEOMETRY
            • SDO_TOPO_GEOMETRY
            • SDO_GEORASTER
        • Literals
          • Interval Literal
          • Interval Type and Date Type
          • Text literals
      • Pseudocolumns
        • ROWID
        • ROWNUM
      • Built-in functions
        • SnowConvert Custom UDFs
          • BFILENAME UDF
          • DATE_TO_JULIANDAYS_UDF
          • DATEADD UDF
          • DATEDIFF UDF
          • INTERVAL UDFs
            • DATEADD UDF INTERVAL
            • DATEDIFF UDF INTERVAL
          • CAST_DATE UDF
          • JSON_VALUE UDF
          • JULIAN TO GREGORIAN DATE UDF
          • MONTHS BETWEEN UDF [DEPRECATED]
          • REGEXP LIKE UDF
          • TIMESTAMP DIFF UDF
          • TRUNC (date) UDF
          • TRUNC (number) UDF
        • TO_NUMBER
        • NLSSORT
      • Built-In packages
        • DBMS_LOB
          • SUBSTR Function
        • DBMS_RANDOM
          • VALUE functions
        • DBMS_OUTPUT
          • PUT_LINE procedure
        • UTL_FILE
          • FOPEN procedure
          • PUT_LINE procedure
          • FCLOSE procedure
      • SQL Queries and Subqueries
        • Select
          • Select Flashback Query
        • Joins
          • Equijoin
          • Band Join
          • Self Join
          • Cartesian Products
          • Inner Join
          • Outer Join
          • Antijoin
          • Semijoin
      • SQL Statements
        • Alter Session
        • Alter Table
        • Create Materialized Views
        • Create Database Link
        • Create Index
        • Create Sequence
        • Create Synonym
        • Create Table
        • Create Type
          • Object Type Definition
          • Subtype Definition
          • Array Type Definition
          • Nested Table Type Definition
          • Member Function Definitions
        • Create View
        • Drop Table
      • PL/SQL to Snowflake Scripting
        • ASSIGNMENT STATEMENT
        • CALL
        • CASE
        • COLLECTIONS AND RECORDS
          • Associative Array Type Definition
          • Varray Type Definition
          • Nested Table Array Type Definition
          • Collection Methods
          • Collection Bulk Operations
            • WITH, SELECT, and BULK COLLECT INTO statements
          • Record Type Definition
        • COMPOUND STATEMENTS
        • CONTINUE
        • CREATE PROCEDURE
        • CURSOR
          • PARAMETRIZED CURSOR
          • CURSOR DECLARATION
          • Workaround for cursors using parameters or procedure variables
          • Cursor Variables
        • DECLARE
        • DEFAULT PARAMETERS
        • DML STATEMENTS
          • INSERT Statement Extension
          • MERGE Statement
          • SELECT INTO Statement
          • Work around to simulate the use of Records
        • EXIT
        • EXPRESSIONS
        • EXECUTE IMMEDIATE
        • FORALL
        • FOR LOOP
        • HELPERS
          • Bulk Cursor Helpers
        • IF
        • IS EMPTY
        • LOCK TABLE
        • LOG ERROR
        • LOOP
        • OUTPUT PARAMETERS
        • PACKAGES
          • DECLARATION
          • BODY
          • VARIABLES
          • Constants
        • PROCEDURE CALL
        • RAISE
        • RAISE_APPICATION_ERROR
        • UDF CALL
        • WHILE
      • PL/SQL to Javascript
        • Procedures
        • User defined functions
        • Packages
        • Helpers
          • EXEC Helper
          • Cursor Helper
          • Raise Helper
          • ROWTYPE Helper
          • Between operator helper
          • Like operator Helper
          • IS NULL Helper
          • Concat Value Helper
          • Package variables helper
          • Implicit Cursor attribute helper
        • Declarations
        • Control Statements
        • Conditional Compilation
        • Collections & Records
        • DDL - DML Statements
        • SQL Language Elements
        • Expressions and operators
        • Synonyms
        • Triggers
        • TYPE attribute
      • SQL*Plus
        • Archive Log
        • Attribute
        • Break
        • Btitle
        • Change
        • Column
        • Define
        • Host
        • Prompt
        • Remark
        • Set
        • Spool
        • Start
        • Whenever oserror
        • Whenever sqlerror
        • Show
        • Append
        • Accept
      • Wrapped objects
    • SQLServer
      • General Language Elements
        • COMPUTED COLUMN
        • EXECUTE
          • System Store Procedures
            • SP_RENAME
        • Collate
        • USE
        • OUTER APPLY
      • DDLs
        • Tables
        • Index
        • Views
        • Procedures
        • Functions
      • DMLs
        • Set Operators
        • Between
        • Update
        • Select
        • Insert
        • Delete
        • Merge
        • Exists
        • IN
        • Truncate
        • Drop
        • Bulk Insert
        • Common Table Expression (CTE)
        • Drops
      • Data Types
      • Statements
        • ALTER
          • TABLE
            • ADD
              • COLUMN DEFINITION
                • COLUMN CONSTRAINT
                  • FOREIGN KEY
                  • PRIMARY KEY / UNIQUE
                  • CHECK
              • TABLE CONSTRAINT
                • FOREIGN KEY
                • PRIMARY KEY
                • CHECK CONSTRAINT
                • CHECK
                • CONNECTION
                • DEFAULT
                • ON PARTITION
        • CREATE
          • FUNCTION
            • SCALAR
            • INLINE TABLE-VALUED
            • MULTI-STATEMENT TABLE-VALUED
      • Built-in functions
        • SnowConvert custom UDFs
          • OPENXML UDF
          • STR UDF
          • SWITCHOFFSET_UDF
        • Aggregate functions
          • COUNT
          • COUNT_BIG
          • SUM
        • Analytic Functions
          • LAG
        • Conversion functions
          • CONVERT
          • TRY_CONVERT
        • Data Type functions
          • DATALENGTH
        • Date & Time functions
          • AT TIME ZONE
          • SWITCHOFFSET
          • DATEADD
          • DATEDIFF
          • DATEPART
          • DATEFROMPARTS
          • DATENAME
          • DAY
          • EOMONTH
          • GETDATE
          • MONTH
          • SYSDATETIME
          • SYSUTCDATETIME
          • YEAR
        • Logical functions
          • IIF
        • Mathematical functions
          • ABS
          • ACOS
            • ACOS in JS
          • ASIN
            • ASIN in JS
          • ATAN
            • ATAN in JS
          • ATN2
            • ATAN2 in JS
          • AVG
          • CEILING
          • COS
            • COS in JS
          • COT
            • COT in JS
          • DEGREES
            • DEGREES in JS
          • EXP
            • EXP in JS
          • FLOOR
          • LOG
            • LOG in JS
          • LOG10
            • LOG10 in JS
          • PI
            • PI in JS
          • POWER
            • POW in JS
          • SQUARE
          • STDEV
          • STDEVP
          • VAR
          • POWER
          • RADIANS
            • RADIANS in JS
          • ROUND
          • SQRT
        • Metadata functions
          • DB_NAME
          • OBJECT_ID
        • Ranking functions
          • DENSE_RANK
          • RANK
          • ROW_NUMBER
        • String functions
          • ASCII
            • ASCII in JS
          • CHAR
          • CHARINDEX
          • COALESCE
          • CONCAT
          • CONCAT_WS
            • Join in JS
          • DIFFERENCE
            • DIFFERENCE in JS
          • FORMAT
            • FORMAT in JS
          • LEFT
          • LEN
          • LOWER
          • LTRIM
            • LTRIM in JS
          • NCHAR
          • PATINDEX
            • search in JS
          • QUOTENAME
            • QUOTENAME in JS
          • REPLACE
          • REPLICATE
          • REVERSE
            • reverse in JS
          • RIGHT
          • RTRIM
          • SOUNDEX
            • SOUNDEX in JS
          • SPACE
          • STR
            • STR in JS
          • STRING_ESCAPE
            • stringify in JS
          • SUBSTRING
          • TRIM
            • trim in JS
          • UPPER
        • System functions
          • FORMATMESSAGE
            • FORMATMESSAGE_UDF
          • ISNULL
          • NEWID
          • NULLIF
        • XML Functions
          • Value
          • Query
      • Built-in procedures
        • Custom User Defined Procedures
          • SP_ADDEXTENDEDPROPERTY_UDP
      • Snowflake Scripting
        • CREATE PROCEDURE
        • CASE
        • CURSOR
        • DECLARE
        • EXECUTE
        • IF
        • SET
        • DMLs
        • CALL
        • WHILE
        • DROPs
        • BEGIN and COMMIT Transaction
        • OUTPUT PARAMETERS
        • LABEL and GOTO
        • SELECT
        • TEXTIMAGE_ON
        • TRY CATCH
      • System Tables
        • SYS.FOREIGN_KEYS
      • Queries
        • TOP
Powered by GitBook
On this page
  • Description
  • Oracle ANSI syntax
  • Snowflake ANSI syntax
  • Sample Source Patterns
  • 1. ANSI syntax
  • Left Outer Join On
  • Right Outer Join On
  • Full Outer Join On
  • 2. Natural Outer Join
  • Natural Left Outer Join
  • Natural Right Outer Join
  • 3. Basic Outer Join with USING
  • Left Outer Join Using
  • 4. (+) Operator
  • Left Outer Join with (+) operator
  • Right Outer Join with (+) operator
  • Single table joined with multiple tables with (+)
  • Using (+) operator with a column from a not-joined table and a non-column value
  • Known issues
  • Related EWIs
  1. Translation Reference
  2. Oracle
  3. SQL Queries and Subqueries
  4. Joins

Outer Join

PreviousInner JoinNextAntijoin

Last updated 1 year ago

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 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 .

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.

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

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;
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

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;
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

IN -> Oracle_02.sql
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;
COUNTRY_ID|COUNTRY_NAME            |REGION_ID|REGION_ID|REGION_NAME           |
----------+------------------------+---------+---------+----------------------+
          |                        |         |        5|Oceania               |
ZW        |Zimbabwe                |        4|        4|Middle East and Africa|
ZM        |Zambia                  |        4|        4|Middle East and Africa|
US        |United States of America|        2|        2|Americas              |
UK        |United Kingdom          |        1|        1|Europe                |
SG        |Singapore               |        3|        3|Asia                  |
NL        |Netherlands             |        1|        1|Europe                |
NG        |Nigeria                 |        4|        4|Middle East and Africa|
MX        |Mexico                  |        2|        2|Americas              |
ML        |Malaysia                |        3|        3|Asia                  |

Snowflake

OUT -> Oracle_02.sql
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;
COUNTRY_ID|COUNTRY_NAME            |REGION_ID            |REGION_ID            |REGION_NAME           |
----------+------------------------+---------------------+---------------------+----------------------+
          |                        |                     |5.0000000000000000000|Oceania               |
ZW        |Zimbabwe                |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
ZM        |Zambia                  |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
US        |United States of America|2.0000000000000000000|2.0000000000000000000|Americas              |
UK        |United Kingdom          |1.0000000000000000000|1.0000000000000000000|Europe                |
SG        |Singapore               |3.0000000000000000000|3.0000000000000000000|Asia                  |
NL        |Netherlands             |1.0000000000000000000|1.0000000000000000000|Europe                |
NG        |Nigeria                 |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
MX        |Mexico                  |2.0000000000000000000|2.0000000000000000000|Americas              |
ML        |Malaysia                |3.0000000000000000000|3.0000000000000000000|Asia                  |

Full Outer Join On

Oracle

IN -> Oracle_03.sql
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;
COUNTRY_ID|COUNTRY_NAME   |REGION_ID|REGION_ID|REGION_NAME           |
----------+---------------+---------+---------+----------------------+
--        |Unknown Country|        0|         |                      |
          |               |         |        5|Oceania               |
EG        |Egypt          |        4|        4|Middle East and Africa|
IL        |Israel         |        4|        4|Middle East and Africa|
KW        |Kuwait         |        4|        4|Middle East and Africa|
NG        |Nigeria        |        4|        4|Middle East and Africa|
ZM        |Zambia         |        4|        4|Middle East and Africa|
ZW        |Zimbabwe       |        4|        4|Middle East and Africa|
BE        |Belgium        |        1|        1|Europe                |
CH        |Switzerland    |        1|        1|Europe                |

Snowflake

OUT -> Oracle_03.sql
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;
COUNTRY_ID|COUNTRY_NAME   |REGION_ID            |REGION_ID            |REGION_NAME           |
----------+---------------+---------------------+---------------------+----------------------+
--        |Unknown Country|0.0000000000000000000|                     |                      |
          |               |                     |5.0000000000000000000|Oceania               |
EG        |Egypt          |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
IL        |Israel         |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
KW        |Kuwait         |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
NG        |Nigeria        |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
ZM        |Zambia         |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
ZW        |Zimbabwe       |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
BE        |Belgium        |1.0000000000000000000|1.0000000000000000000|Europe                |
CH        |Switzerland    |1.0000000000000000000|1.0000000000000000000|Europe                |

2. Natural Outer Join

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

Natural Left Outer Join

Oracle

IN -> Oracle_04.sql
SELECT * FROM
hr.countries c
NATURAL LEFT OUTER JOIN hr.regions r
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;
REGION_ID|COUNTRY_ID|COUNTRY_NAME   |REGION_NAME|
---------+----------+---------------+-----------+
        0|--        |Unknown Country|           |
        2|AR        |Argentina      |Americas   |
        3|AU        |Australia      |Asia       |
        1|BE        |Belgium        |Europe     |
        2|BR        |Brazil         |Americas   |
        2|CA        |Canada         |Americas   |
        1|CH        |Switzerland    |Europe     |
        3|CN        |China          |Asia       |
        1|DE        |Germany        |Europe     |
        1|DK        |Denmark        |Europe     |

Snowflake

OUT -> Oracle_04.sql
SELECT * FROM
hr.countries c
NATURAL LEFT OUTER JOIN
hr.regions r
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;
REGION_ID            |COUNTRY_ID|COUNTRY_NAME   |REGION_NAME|
---------------------+----------+---------------+-----------+
0.0000000000000000000|--        |Unknown Country|           |
2.0000000000000000000|AR        |Argentina      |Americas   |
3.0000000000000000000|AU        |Australia      |Asia       |
1.0000000000000000000|BE        |Belgium        |Europe     |
2.0000000000000000000|BR        |Brazil         |Americas   |
2.0000000000000000000|CA        |Canada         |Americas   |
1.0000000000000000000|CH        |Switzerland    |Europe     |
3.0000000000000000000|CN        |China          |Asia       |
1.0000000000000000000|DE        |Germany        |Europe     |
1.0000000000000000000|DK        |Denmark        |Europe     |

Natural Right Outer Join

Oracle

IN -> Oracle_05.sql
SELECT * FROM
hr.countries c
NATURAL RIGHT OUTER JOIN hr.regions r
ORDER BY country_id DESC
FETCH FIRST 10 ROWS ONLY;
REGION_ID|COUNTRY_ID|COUNTRY_NAME            |REGION_NAME           |
---------+----------+------------------------+----------------------+
        5|          |                        |Oceania               |
        4|ZW        |Zimbabwe                |Middle East and Africa|
        4|ZM        |Zambia                  |Middle East and Africa|
        2|US        |United States of America|Americas              |
        1|UK        |United Kingdom          |Europe                |
        3|SG        |Singapore               |Asia                  |
        1|NL        |Netherlands             |Europe                |
        4|NG        |Nigeria                 |Middle East and Africa|
        2|MX        |Mexico                  |Americas              |
        3|ML        |Malaysia                |Asia                  |

Snowflake

OUT -> Oracle_05.sql
SELECT * FROM
hr.countries c
NATURAL RIGHT OUTER JOIN
hr.regions r
ORDER BY country_id DESC
FETCH FIRST 10 ROWS ONLY;
REGION_ID            |COUNTRY_ID|COUNTRY_NAME            |REGION_NAME           |
---------------------+----------+------------------------+----------------------+
5.0000000000000000000|          |                        |Oceania               |
4.0000000000000000000|ZW        |Zimbabwe                |Middle East and Africa|
4.0000000000000000000|ZM        |Zambia                  |Middle East and Africa|
2.0000000000000000000|US        |United States of America|Americas              |
1.0000000000000000000|UK        |United Kingdom          |Europe                |
3.0000000000000000000|SG        |Singapore               |Asia                  |
1.0000000000000000000|NL        |Netherlands             |Europe                |
4.0000000000000000000|NG        |Nigeria                 |Middle East and Africa|
2.0000000000000000000|MX        |Mexico                  |Americas              |
3.0000000000000000000|ML        |Malaysia                |Asia                  |

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

IN -> Oracle_06.sql
SELECT * FROM
hr.countries c
LEFT OUTER JOIN hr.regions r USING (region_id)
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;
REGION_ID|COUNTRY_ID|COUNTRY_NAME   |REGION_NAME|
---------+----------+---------------+-----------+
        0|--        |Unknown Country|           |
        2|AR        |Argentina      |Americas   |
        3|AU        |Australia      |Asia       |
        1|BE        |Belgium        |Europe     |
        2|BR        |Brazil         |Americas   |
        2|CA        |Canada         |Americas   |
        1|CH        |Switzerland    |Europe     |
        3|CN        |China          |Asia       |
        1|DE        |Germany        |Europe     |
        1|DK        |Denmark        |Europe     |

Snowflake

OUT -> Oracle_06.sql
SELECT * FROM
hr.countries c
LEFT OUTER JOIN
hr.regions r USING (region_id)
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;
REGION_ID            |COUNTRY_ID|COUNTRY_NAME   |REGION_NAME|
---------------------+----------+---------------+-----------+
0.0000000000000000000|--        |Unknown Country|           |
2.0000000000000000000|AR        |Argentina      |Americas   |
3.0000000000000000000|AU        |Australia      |Asia       |
1.0000000000000000000|BE        |Belgium        |Europe     |
2.0000000000000000000|BR        |Brazil         |Americas   |
2.0000000000000000000|CA        |Canada         |Americas   |
1.0000000000000000000|CH        |Switzerland    |Europe     |
3.0000000000000000000|CN        |China          |Asia       |
1.0000000000000000000|DE        |Germany        |Europe     |
1.0000000000000000000|DK        |Denmark        |Europe     |

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.

Left Outer Join with (+) operator

Oracle

IN -> Oracle_07.sql
SELECT * FROM hr.countries c, hr.regions r
WHERE c.region_id = r.region_id(+)
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;
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

OUT -> Oracle_07.sql
SELECT * FROM
hr.countries c,
hr.regions r
WHERE c.region_id = r.region_id(+)
ORDER BY country_id
FETCH FIRST 10 ROWS ONLY;
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 with (+) operator

Oracle

IN -> Oracle_08.sql
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;
COUNTRY_ID|COUNTRY_NAME            |REGION_ID|REGION_ID|REGION_NAME           |
----------+------------------------+---------+---------+----------------------+
          |                        |         |        5|Oceania               |
ZW        |Zimbabwe                |        4|        4|Middle East and Africa|
ZM        |Zambia                  |        4|        4|Middle East and Africa|
US        |United States of America|        2|        2|Americas              |
UK        |United Kingdom          |        1|        1|Europe                |
SG        |Singapore               |        3|        3|Asia                  |
NL        |Netherlands             |        1|        1|Europe                |
NG        |Nigeria                 |        4|        4|Middle East and Africa|
MX        |Mexico                  |        2|        2|Americas              |
ML        |Malaysia                |        3|        3|Asia                  |

Snowflake

OUT -> Oracle_08.sql
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;
COUNTRY_ID|COUNTRY_NAME            |REGION_ID            |REGION_ID            |REGION_NAME           |
----------+------------------------+---------------------+---------------------+----------------------+
          |                        |                     |5.0000000000000000000|Oceania               |
ZW        |Zimbabwe                |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
ZM        |Zambia                  |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
US        |United States of America|2.0000000000000000000|2.0000000000000000000|Americas              |
UK        |United Kingdom          |1.0000000000000000000|1.0000000000000000000|Europe                |
SG        |Singapore               |3.0000000000000000000|3.0000000000000000000|Asia                  |
NL        |Netherlands             |1.0000000000000000000|1.0000000000000000000|Europe                |
NG        |Nigeria                 |4.0000000000000000000|4.0000000000000000000|Middle East and Africa|
MX        |Mexico                  |2.0000000000000000000|2.0000000000000000000|Americas              |
ML        |Malaysia                |3.0000000000000000000|3.0000000000000000000|Asia                  |

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

IN -> Oracle_09.sql
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;
|COUNTRY_ID|COUNTRY_NAME  |REGION_ID|REGION_NAME|LOCATION_ID|STREET_ADDRESS                          |POSTAL_CODE|CITY       |
|----------|--------------|---------|-----------|-----------|----------------------------------------|-----------|-----------|
|          |              |1        |Europe     |2000       |40-5-12 Laogianggen                     |190518     |Beijing    |
|CH        |Switzerland   |1        |Europe     |3000       |Murtenstrasse 921                       |3095       |Bern       |
|          |              |1        |Europe     |2100       |1298 Vileparle (E)                      |490231     |Bombay     |
|CH        |Switzerland   |1        |Europe     |2900       |20 Rue des Corps-Saints                 |1730       |Geneva     |
|          |              |1        |Europe     |1300       |9450 Kamiya-cho                         |6823       |Hiroshima  |
|UK        |United Kingdom|1        |Europe     |2400       |8204 Arthur St                          |           |London     |
|          |              |1        |Europe     |3200       |Mariano Escobedo 9991                   |11932      |Mexico City|
|DE        |Germany       |1        |Europe     |2700       |Schwanthalerstr. 7031                   |80925      |Munich     |
|UK        |United Kingdom|1        |Europe     |2500       |Magdalen Centre, The Oxford Science Park|OX9 9ZB    |Oxford     |
|IT        |Italy         |1        |Europe     |1000       |1297 Via Cola di Rie                    |00989      |Roma       |

Snowflake

OUT -> Oracle_09.sql
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.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;
COUNTRY_ID|COUNTRY_NAME  |REGION_ID            |REGION_NAME|LOCATION_ID|STREET_ADDRESS                          |POSTAL_CODE|CITY       |
----------+--------------+---------------------+-----------+-----------+----------------------------------------+-----------+-----------+
          |              |1.0000000000000000000|Europe     |       2000|40-5-12 Laogianggen                     |190518     |Beijing    |
CH        |Switzerland   |1.0000000000000000000|Europe     |       3000|Murtenstrasse 921                       |3095       |Bern       |
          |              |1.0000000000000000000|Europe     |       2100|1298 Vileparle (E)                      |490231     |Bombay     |
CH        |Switzerland   |1.0000000000000000000|Europe     |       2900|20 Rue des Corps-Saints                 |1730       |Geneva     |
          |              |1.0000000000000000000|Europe     |       1300|9450 Kamiya-cho                         |6823       |Hiroshima  |
UK        |United Kingdom|1.0000000000000000000|Europe     |       2400|8204 Arthur St                          |           |London     |
          |              |1.0000000000000000000|Europe     |       3200|Mariano Escobedo 9991                   |11932      |Mexico City|
DE        |Germany       |1.0000000000000000000|Europe     |       2700|Schwanthalerstr. 7031                   |80925      |Munich     |
UK        |United Kingdom|1.0000000000000000000|Europe     |       2500|Magdalen Centre, The Oxford Science Park|OX9 9ZB    |Oxford     |
IT        |Italy         |1.0000000000000000000|Europe     |       1000|1297 Via Cola di Rie                    |00989      |Roma       |

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

IN -> Oracle_10.sql
SELECT * FROM hr.regions r
WHERE
r.region_name (+) LIKE 'A%'
ORDER BY region_id;
REGION_ID|REGION_NAME|
---------+-----------+
        2|Americas   |
        3|Asia       |

Snowflake

OUT -> Oracle_10.sql
SELECT * FROM
hr.regions r
WHERE
r.region_name LIKE 'A%'
ORDER BY region_id;
REGION_ID            |REGION_NAME|
---------------------+-----------+
2.0000000000000000000|Americas   |
3.0000000000000000000|Asia       |

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

IN -> Oracle_11.sql
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;
COUNTRY_ID|COUNTRY_NAME  |REGION_ID|REGION_ID|REGION_NAME|LOCATION_ID|STREET_ADDRESS                          |POSTAL_CODE|CITY       |STATE_PROVINCE   |COUNTRY_ID|
----------+--------------+---------+---------+-----------+-----------+----------------------------------------+-----------+-----------+-----------------+----------+
          |              |         |        1|Europe     |       2000|40-5-12 Laogianggen                     |190518     |Beijing    |                 |CN        |
CH        |Switzerland   |        1|        1|Europe     |       3000|Murtenstrasse 921                       |3095       |Bern       |BE               |CH        |
          |              |         |        1|Europe     |       2100|1298 Vileparle (E)                      |490231     |Bombay     |Maharashtra      |IN        |
CH        |Switzerland   |        1|        1|Europe     |       2900|20 Rue des Corps-Saints                 |1730       |Geneva     |Geneve           |CH        |
          |              |         |        1|Europe     |       1300|9450 Kamiya-cho                         |6823       |Hiroshima  |                 |JP        |
UK        |United Kingdom|        1|        1|Europe     |       2400|8204 Arthur St                          |           |London     |                 |UK        |
          |              |         |        1|Europe     |       3200|Mariano Escobedo 9991                   |11932      |Mexico City|Distrito Federal,|MX        |
DE        |Germany       |        1|        1|Europe     |       2700|Schwanthalerstr. 7031                   |80925      |Munich     |Bavaria          |DE        |
UK        |United Kingdom|        1|        1|Europe     |       2500|Magdalen Centre, The Oxford Science Park|OX9 9ZB    |Oxford     |Oxford           |UK        |
IT        |Italy         |        1|        1|Europe     |       1000|1297 Via Cola di Rie                    |00989      |Roma       |                 |IT        |

Snowflake

OUT -> Oracle_11.sql
SELECT
*
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;
REGION_ID            |REGION_NAME|LOCATION_ID|STREET_ADDRESS                          |POSTAL_CODE|CITY       |STATE_PROVINCE   |COUNTRY_ID|COUNTRY_ID|COUNTRY_NAME  |REGION_ID            |
---------------------+-----------+-----------+----------------------------------------+-----------+-----------+-----------------+----------+----------+--------------+---------------------+
1.0000000000000000000|Europe     |       2000|40-5-12 Laogianggen                     |190518     |Beijing    |                 |CN        |          |              |                     |
1.0000000000000000000|Europe     |       3000|Murtenstrasse 921                       |3095       |Bern       |BE               |CH        |CH        |Switzerland   |1.0000000000000000000|
1.0000000000000000000|Europe     |       2100|1298 Vileparle (E)                      |490231     |Bombay     |Maharashtra      |IN        |          |              |                     |
1.0000000000000000000|Europe     |       2900|20 Rue des Corps-Saints                 |1730       |Geneva     |Geneve           |CH        |CH        |Switzerland   |1.0000000000000000000|
1.0000000000000000000|Europe     |       1300|9450 Kamiya-cho                         |6823       |Hiroshima  |                 |JP        |          |              |                     |
1.0000000000000000000|Europe     |       2400|8204 Arthur St                          |           |London     |                 |UK        |UK        |United Kingdom|1.0000000000000000000|
1.0000000000000000000|Europe     |       3200|Mariano Escobedo 9991                   |11932      |Mexico City|Distrito Federal,|MX        |          |              |                     |
1.0000000000000000000|Europe     |       2700|Schwanthalerstr. 7031                   |80925      |Munich     |Bavaria          |DE        |DE        |Germany       |1.0000000000000000000|
1.0000000000000000000|Europe     |       2500|Magdalen Centre, The Oxford Science Park|OX9 9ZB    |Oxford     |Oxford           |UK        |UK        |United Kingdom|1.0000000000000000000|
1.0000000000000000000|Europe     |       1000|1297 Via Cola di Rie                    |00989      |Roma       |                 |IT        |IT        |Italy         |1.0000000000000000000|

2. 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

IN -> Oracle_13.sql
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;
COUNTRY_ID|COUNTRY_NAME|REGION_ID|REGION_ID|REGION_NAME|LOCATION_ID|STREET_ADDRESS                          |POSTAL_CODE|CITY       |STATE_PROVINCE   |COUNTRY_ID|
----------+------------+---------+---------+-----------+-----------+----------------------------------------+-----------+-----------+-----------------+----------+
          |            |         |        1|Europe     |       2000|40-5-12 Laogianggen                     |190518     |Beijing    |                 |CN        |
          |            |         |        1|Europe     |       3000|Murtenstrasse 921                       |3095       |Bern       |BE               |CH        |
          |            |         |        1|Europe     |       2100|1298 Vileparle (E)                      |490231     |Bombay     |Maharashtra      |IN        |
          |            |         |        1|Europe     |       2900|20 Rue des Corps-Saints                 |1730       |Geneva     |Geneve           |CH        |
          |            |         |        1|Europe     |       1300|9450 Kamiya-cho                         |6823       |Hiroshima  |                 |JP        |
          |            |         |        1|Europe     |       2400|8204 Arthur St                          |           |London     |                 |UK        |
          |            |         |        1|Europe     |       3200|Mariano Escobedo 9991                   |11932      |Mexico City|Distrito Federal,|MX        |
          |            |         |        1|Europe     |       2700|Schwanthalerstr. 7031                   |80925      |Munich     |Bavaria          |DE        |
          |            |         |        1|Europe     |       2500|Magdalen Centre, The Oxford Science Park|OX9 9ZB    |Oxford     |Oxford           |UK        |
          |            |         |        1|Europe     |       1000|1297 Via Cola di Rie                    |00989      |Roma       |                 |IT        |

Snowflake

OUT -> Oracle_13.sql
SELECT
*
FROM
hr.countries c,
hr.regions r,
hr.locations l WHERE
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0090 - 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

Check this to set up the sample database.

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 more information regarding this operator in Snowflake, check .

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

Oracle SQL Language Reference Outer Joins Subsection
here
section
Snowflake SQL Language Reference JOIN
this
SSC-EWI-OR0090