SnowConvert AI - Oracle - Joins¶

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query. (Oracle SQL Language Reference JOINS)

Antijoin¶

Note

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

Description¶

An antijoin returns rows from the left side of the predicate for which there are no corresponding rows on the right side of the predicate. It returns rows that fail to match (NOT IN) the subquery on the right side. Antijoin transformation cannot be done if the subquery is on an OR branch of the WHERE clause. (Oracle SQL Language Reference Anti Join).

No special transformation is performed for this kind of Join since Snowflake supports the same syntax.

Sample Source Patterns¶

Note

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

Note

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

Note

Check this section to set up the sample database.

Where Not In¶

Oracle¶

SELECT e.employee_id, e.first_name, e.last_name FROM hr.employees e
WHERE e.department_id NOT IN

    (SELECT h.department_id FROM hr.departments h WHERE location_id = 1700)
    
ORDER BY e.last_name
FETCH FIRST 10 ROWS ONLY;

Result¶


EMPLOYEE_ID	FIRST_NAME	LAST_NAME
174	Ellen	Abel
166	Sundar	Ande
130	Mozhe	Atkinson
105	David	Austin
204	Hermann	Baer
167	Amit	Banda
172	Elizabeth	Bates
192	Sarah	Bell
151	David	Bernstein
129	Laura	Bissot

Snowflake¶

SELECT e.employee_id, e.first_name, e.last_name FROM
    hr.employees e
WHERE e.department_id NOT IN
        !!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!!
    (SELECT h.department_id FROM
            hr.departments h WHERE location_id = 1700)

ORDER BY e.last_name
    FETCH FIRST 10 ROWS ONLY;

Result¶


EMPLOYEE_ID	FIRST_NAME	LAST_NAME
174	Ellen	Abel
166	Sundar	Ande
130	Mozhe	Atkinson
105	David	Austin
204	Hermann	Baer
167	Amit	Banda
172	Elizabeth	Bates
192	Sarah	Bell
151	David	Bernstein
129	Laura	Bissot

Where Not Exists¶

Oracle¶

SELECT   d.department_id, d.department_name
FROM     hr.departments d
WHERE    NOT EXISTS
         
         (SELECT 1 FROM hr.employees E WHERE
         e.department_id = d.department_id)
         
ORDER BY d.department_id
FETCH FIRST 10 ROWS ONLY;

Result¶


DEPARTMENT_ID	DEPARTMENT_NAME
120	Treasury
130	Corporate Tax
140	Control And Credit
150	Shareholder Services
160	Benefits
170	Manufacturing
180	Construction
190	Contracting
200	Operations
210	IT Support

Snowflake¶

SELECT   d.department_id, d.department_name
FROM
         hr.departments d
WHERE    NOT EXISTS
                  !!!RESOLVE EWI!!! /*** SSC-EWI-0108 - THE FOLLOWING SUBQUERY MATCHES AT LEAST ONE OF THE PATTERNS CONSIDERED INVALID AND MAY PRODUCE COMPILATION ERRORS ***/!!!
         (SELECT 1 FROM
                           hr.employees E WHERE
         e.department_id = d.department_id)

ORDER BY d.department_id
         FETCH FIRST 10 ROWS ONLY;

Result¶


DEPARTMENT_ID	DEPARTMENT_NAME
120	Treasury
130	Corporate Tax
140	Control And Credit
150	Shareholder Services
160	Benefits
170	Manufacturing
180	Construction
190	Contracting
200	Operations
210	IT Support

Known issues¶

1. Results ordering mismatch between languages¶

The result of the query will have the same content in both database engines but the order might be different if no Order By clause is defined in the query.

Band Join¶

Note

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

Description¶

A band join is a special type of nonequijoin in which key values in one data set must fall within the specified range (“band”) of the second data set. The same table can serve as both the first and second data sets. (Oracle SQL Language Reference BandJoin)

In this section, we will see how a band join is executed in Snowflake and the execution plan is very similar to the improved version of Oracle.

Sample Source Patterns¶

Note

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

Note

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

Note

Check this section to set up the sample database.

Warning

If you migrate this code without the create tables, the converter won’t be able to load semantic information of the columns and a warning will appear on the arithmetic operations.

Basic Band Join case¶

Oracle¶

SELECT  e1.last_name || 
        ' has salary between 100 less and 100 more than ' || 
        e2.last_name AS "SALARY COMPARISON"
FROM    employees e1, 
        employees e2
WHERE   e1.salary 
BETWEEN e2.salary - 100 
AND     e2.salary + 100
ORDER BY "SALARY COMPARISON"
FETCH FIRST 10 ROWS ONLY

Result¶


SALARY COMPARISON
Abel has salary between 100 less and 100 more than Abel
Abel has salary between 100 less and 100 more than Cambrault
Abel has salary between 100 less and 100 more than Raphaely
Ande has salary between 100 less and 100 more than Ande
Ande has salary between 100 less and 100 more than Mavris
Ande has salary between 100 less and 100 more than Vollman
Atkinson has salary between 100 less and 100 more than Atkinson
Atkinson has salary between 100 less and 100 more than Baida
Atkinson has salary between 100 less and 100 more than Gates
Atkinson has salary between 100 less and 100 more than Geoni

Snowflake¶

SELECT
                NVL(  e1.last_name :: STRING, '') ||
                ' has salary between 100 less and 100 more than ' || NVL(
                e2.last_name :: STRING, '') AS "SALARY COMPARISON"
FROM
                employees e1,
                employees e2
WHERE   e1.salary
BETWEEN
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '-' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!! e2.salary - 100
AND
        !!!RESOLVE EWI!!! /*** SSC-EWI-OR0036 - TYPES RESOLUTION ISSUES, ARITHMETIC OPERATION '+' MAY NOT BEHAVE CORRECTLY BETWEEN unknown AND Number ***/!!!     e2.salary + 100
ORDER BY "SALARY COMPARISON"
FETCH FIRST 10 ROWS ONLY;

Result¶


SALARY COMPARISON
Abel has salary between 100 less and 100 more than Abel
Abel has salary between 100 less and 100 more than Cambrault
Abel has salary between 100 less and 100 more than Raphaely
Ande has salary between 100 less and 100 more than Ande
Ande has salary between 100 less and 100 more than Mavris
Ande has salary between 100 less and 100 more than Vollman
Atkinson has salary between 100 less and 100 more than Atkinson
Atkinson has salary between 100 less and 100 more than Baida
Atkinson has salary between 100 less and 100 more than Gates
Atkinson has salary between 100 less and 100 more than Geoni

Warning

Migrating some SELECT statements without the corresponding tables could generate the SSC-EWI-OR0036: Types resolution issues. To avoid this warning, include the CREATE TABLE inside the file.

The results are the same making the BAND JOIN functional equivalent.

Execution plan

As extra information, the special thing about the band joins is the execution plan.

The following image shows the enhanced execution plan (implemented since Oracle 12c) for the test query:

And in the following image, we will see the execution plan in Snowflake:

Note

The execution plan in Snowflake is very similar to Oracle’s optimized version. The final duration and performance of the query will be affected by many other factors and are completely dependent on each DBMS internal functionality.

Known Issues¶

1. Results ordering mismatch between languages¶

The query result will have the same content in both database engines but the order might be different if no Order By clause is defined in the query.

Related EWIs¶

SSC-EWI-OR0036 : Types resolution issues, the arithmetic operation may not behave correctly between string and date.

Cartesian Products¶

Note

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

If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product. Oracle combines each row of one table with each row of the other. (Oracle SQL Reference Cartesian Products Subsection)

Oracle and Snowflake are also compatible with the ANSI Cross Join syntax that has the same behavior of a cartesian product.

No special transformation is performed for this kind of Join since Snowflake supports the same syntax.

Sample Source Patterns¶

Note

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

Note

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

Note

Check this section to set up the sample database.

Implicit Syntax¶

Oracle¶

-- Resulting rows
SELECT * FROM hr.employees, hr.departments
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY;

-- Resulting total rows
SELECT COUNT(*) FROM hr.employees, hr.departments;

Result 1¶


EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	MANAGER_ID	DEPARTMENT_ID	DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10 00:00:00.000	ST_MAN	8200	100	50	10	Administration	200	1700
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10 00:00:00.000	ST_MAN	8200	100	50	50	Shipping	121	1500
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10 00:00:00.000	ST_MAN	8200	100	50	40	Human Resources	203	2400
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10 00:00:00.000	ST_MAN	8200	100	50	30	Purchasing	114	1700
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10 00:00:00.000	ST_MAN	8200	100	50	20	Marketing	201	1800

Result 2¶


COUNT(*)
2889

Snowflake¶

-- Resulting rows
SELECT * FROM
hr.employees,
hr.departments
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY;

-- Resulting total rows
SELECT COUNT(*) FROM
hr.employees,
hr.departments;

Result 1¶


EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	MANAGER_ID	DEPARTMENT_ID	DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10	ST_MAN	8200.00	100	50	40	Human Resources	203	2400
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10	ST_MAN	8200.00	100	50	20	Marketing	201	1800
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10	ST_MAN	8200.00	100	50	10	Administration	200	1700
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10	ST_MAN	8200.00	100	50	50	Shipping	121	1500
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10	ST_MAN	8200.00	100	50	30	Purchasing	114	1700

Result 2¶


COUNT(*)
2889

Cross Join Syntax¶

Oracle¶

-- Resulting rows
SELECT * FROM hr.employees CROSS join hr.departments
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY;

-- Resulting total rows
SELECT COUNT(*) FROM hr.employees CROSS join hr.departments;

Result 1¶


EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	MANAGER_ID	DEPARTMENT_ID	DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10 00:00:00.000	ST_MAN	8200	100	50	10	Administration	200	1700
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10 00:00:00.000	ST_MAN	8200	100	50	50	Shipping	121	1500
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10 00:00:00.000	ST_MAN	8200	100	50	40	Human Resources	203	2400
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10 00:00:00.000	ST_MAN	8200	100	50	30	Purchasing	114	1700
121	Adam	Fripp	AFRIPP	650.123.2234	2005-04-10 00:00:00.000	ST_MAN	8200	100	50	20	Marketing	201	1800

Result 2¶


COUNT(*)
2889

Snowflake¶

-- Resulting rows
SELECT * FROM
hr.employees
CROSS join hr.departments
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY;

-- Resulting total rows
SELECT COUNT(*) FROM
hr.employees
CROSS join hr.departments;

Known issues¶

1. Results ordering mismatch between languages¶

The result of the query will have the same content in both database engines but the order might be different if no Order By clause is defined in the query.

Related EWIs¶

No related EWIs.

Equijoin¶

Note

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

Description¶

An equijoin is an implicit form of the join with a join condition containing an equality operator. For more information, see the Oracle Equijoin documentation.

No special transformation is performed for this kind of Join since Snowflake supports the same syntax.

Sample Source Patterns¶

Note

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

Note

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

Note

Check this section to set up the sample database.

Basic Equijoin case¶

Oracle¶

 SELECT last_name, job_id, hr.departments.department_id, department_name
FROM hr.employees, hr.departments
WHERE hr.employees.department_id = hr.departments.department_id
ORDER BY last_name
FETCH FIRST 5 ROWS ONLY;

Result¶


LAST_NAME	JOB_ID	DEPARTMENT_ID	DEPARTMENT_NAME
Abel	SA_REP	80	Sales
Ande	SA_REP	80	Sales
Atkinson	ST_CLERK	50	Shipping
Austin	IT_PROG	60	IT
Baer	PR_REP	70	Public Relations

Snowflake¶

 SELECT last_name, job_id, hr.departments.department_id, department_name
FROM
hr.employees,
hr.departments
WHERE hr.employees.department_id = hr.departments.department_id
ORDER BY last_name
FETCH FIRST 5 ROWS ONLY;

Result¶


LAST_NAME	JOB_ID	DEPARTMENT_ID	DEPARTMENT_NAME
Abel	SA_REP	80	Sales
Ande	SA_REP	80	Sales
Atkinson	ST_CLERK	50	Shipping
Austin	IT_PROG	60	IT
Baer	PR_REP	70	Public Relations

Known issues¶

1. Results ordering mismatch between languages¶

The result of the query will have the same content in both database engines but the order might be different if no Order By clause is defined in the query.

Related EWIs¶

No related EWIs.

Inner Join¶

Note

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

Description¶

An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition. (Oracle SQL Reference Inner Join Subsection).

{ [ INNER ] JOIN table_reference
 { ON condition
 | USING (column [, column ]...)
 }
| { CROSS
 | NATURAL [ INNER ]
 }
 JOIN table_reference
}

Sample Source Patterns¶

Note

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

Note

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

Note

Check this section to set up the sample database.

Basic Inner Join¶

In the Inner Join clause “INNER” is an optional keyword, the following queries have two selects that retrieve the same data set.

Oracle¶

 SELECT 
    *
FROM 
    hr.employees
INNER JOIN hr.departments ON
    hr.departments.department_id = hr.employees.department_id
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;

SELECT 
    *
FROM 
    hr.employees
JOIN hr.departments ON
    hr.departments.department_id = hr.employees.department_id
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;

Result¶


EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	MANAGER_ID	DEPARTMENT_ID	DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
100	Steven	King	SKING	515.123.4567	2003-06-17 00:00:00.000	AD_PRES	24000		90	90	Executive	100	1700
101	Neena	Kochhar	NKOCHHAR	515.123.4568	2005-09-21 00:00:00.000	AD_VP	17000	100	90	90	Executive	100	1700
102	Lex	De Haan	LDEHAAN	515.123.4569	2001-01-13 00:00:00.000	AD_VP	17000	100	90	90	Executive	100	1700
103	Alexander	Hunold	AHUNOLD	590.423.4567	2006-01-03 00:00:00.000	IT_PROG	9000	102	60	60	IT	103	1400
104	Bruce	Ernst	BERNST	590.423.4568	2007-05-21 00:00:00.000	IT_PROG	6000	103	60	60	IT	103	1400
105	David	Austin	DAUSTIN	590.423.4569	2005-06-25 00:00:00.000	IT_PROG	4800	103	60	60	IT	103	1400
106	Valli	Pataballa	VPATABAL	590.423.4560	2006-02-05 00:00:00.000	IT_PROG	4800	103	60	60	IT	103	1400
107	Diana	Lorentz	DLORENTZ	590.423.5567	2007-02-07 00:00:00.000	IT_PROG	4200	103	60	60	IT	103	1400
108	Nancy	Greenberg	NGREENBE	515.124.4569	2002-08-17 00:00:00.000	FI_MGR	12008	101	100	100	Finance	108	1700
109	Daniel	Faviet	DFAVIET	515.124.4169	2002-08-16 00:00:00.000	FI_ACCOUNT	9000	108	100	100	Finance	108	1700

Snowflake¶

 SELECT
    *
FROM
hr.employees
INNER JOIN
    hr.departments
    ON
    hr.departments.department_id = hr.employees.department_id
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;

SELECT
    *
FROM
    hr.employees
JOIN
    hr.departments
    ON
    hr.departments.department_id = hr.employees.department_id
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;

Result¶


EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	MANAGER_ID	DEPARTMENT_ID	DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000.00		90	90	Executive	100	1700
101	Neena	Kochhar	NKOCHHAR	515.123.4568	2005-09-21	AD_VP	17000.00	100	90	90	Executive	100	1700
102	Lex	De Haan	LDEHAAN	515.123.4569	2001-01-13	AD_VP	17000.00	100	90	90	Executive	100	1700
103	Alexander	Hunold	AHUNOLD	590.423.4567	2006-01-03	IT_PROG	9000.00	102	60	60	IT	103	1400
104	Bruce	Ernst	BERNST	590.423.4568	2007-05-21	IT_PROG	6000.00	103	60	60	IT	103	1400
105	David	Austin	DAUSTIN	590.423.4569	2005-06-25	IT_PROG	4800.00	103	60	60	IT	103	1400
106	Valli	Pataballa	VPATABAL	590.423.4560	2006-02-05	IT_PROG	4800.00	103	60	60	IT	103	1400
107	Diana	Lorentz	DLORENTZ	590.423.5567	2007-02-07	IT_PROG	4200.00	103	60	60	IT	103	1400
108	Nancy	Greenberg	NGREENBE	515.124.4569	2002-08-17	FI_MGR	12008.00	101	100	100	Finance	108	1700
109	Daniel	Faviet	DFAVIET	515.124.4169	2002-08-16	FI_ACCOUNT	9000.00	108	100	100	Finance	108	1700

Inner Join with using clause¶

Oracle¶

SELECT 
    *
FROM 
    hr.employees
INNER JOIN hr.departments
    USING(department_id)
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;

Result¶


DEPARTMENT_ID	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	MANAGER_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
90	100	Steven	King	SKING	515.123.4567	2003-06-17 00:00:00.000	AD_PRES	24000		Executive	100	1700
90	101	Neena	Kochhar	NKOCHHAR	515.123.4568	2005-09-21 00:00:00.000	AD_VP	17000	100	Executive	100	1700
90	102	Lex	De Haan	LDEHAAN	515.123.4569	2001-01-13 00:00:00.000	AD_VP	17000	100	Executive	100	1700
60	103	Alexander	Hunold	AHUNOLD	590.423.4567	2006-01-03 00:00:00.000	IT_PROG	9000	102	IT	103	1400
60	104	Bruce	Ernst	BERNST	590.423.4568	2007-05-21 00:00:00.000	IT_PROG	6000	103	IT	103	1400
60	105	David	Austin	DAUSTIN	590.423.4569	2005-06-25 00:00:00.000	IT_PROG	4800	103	IT	103	1400
60	106	Valli	Pataballa	VPATABAL	590.423.4560	2006-02-05 00:00:00.000	IT_PROG	4800	103	IT	103	1400
60	107	Diana	Lorentz	DLORENTZ	590.423.5567	2007-02-07 00:00:00.000	IT_PROG	4200	103	IT	103	1400
100	108	Nancy	Greenberg	NGREENBE	515.124.4569	2002-08-17 00:00:00.000	FI_MGR	12008	101	Finance	108	1700
100	109	Daniel	Faviet	DFAVIET	515.124.4169	2002-08-16 00:00:00.000	FI_ACCOUNT	9000	108	Finance	108	1700

Snowflake¶

SELECT
    *
FROM
hr.employees
INNER JOIN
    hr.departments
    USING(department_id)
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;

Result¶


DEPARTMENT_ID	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	MANAGER_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
90	100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000.00		Executive	100	1700
90	101	Neena	Kochhar	NKOCHHAR	515.123.4568	2005-09-21	AD_VP	17000.00	100	Executive	100	1700
90	102	Lex	De Haan	LDEHAAN	515.123.4569	2001-01-13	AD_VP	17000.00	100	Executive	100	1700
60	103	Alexander	Hunold	AHUNOLD	590.423.4567	2006-01-03	IT_PROG	9000.00	102	IT	103	1400
60	104	Bruce	Ernst	BERNST	590.423.4568	2007-05-21	IT_PROG	6000.00	103	IT	103	1400
60	105	David	Austin	DAUSTIN	590.423.4569	2005-06-25	IT_PROG	4800.00	103	IT	103	1400
60	106	Valli	Pataballa	VPATABAL	590.423.4560	2006-02-05	IT_PROG	4800.00	103	IT	103	1400
60	107	Diana	Lorentz	DLORENTZ	590.423.5567	2007-02-07	IT_PROG	4200.00	103	IT	103	1400
100	108	Nancy	Greenberg	NGREENBE	515.124.4569	2002-08-17	FI_MGR	12008.00	101	Finance	108	1700
100	109	Daniel	Faviet	DFAVIET	515.124.4169	2002-08-16	FI_ACCOUNT	9000.00	108	Finance	108	1700

Cross Inner Join¶

Oracle¶

SELECT 
    *
FROM 
    hr.employees
CROSS JOIN hr.departments
ORDER BY department_name, employee_id
FETCH NEXT 10 ROWS ONLY;

Result¶


EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	MANAGER_ID	DEPARTMENT_ID	DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
100	Steven	King	SKING	515.123.4567	2003-06-17 00:00:00.000	AD_PRES	24000		90	110	Accounting	205	1700
101	Neena	Kochhar	NKOCHHAR	515.123.4568	2005-09-21 00:00:00.000	AD_VP	17000	100	90	110	Accounting	205	1700
102	Lex	De Haan	LDEHAAN	515.123.4569	2001-01-13 00:00:00.000	AD_VP	17000	100	90	110	Accounting	205	1700
103	Alexander	Hunold	AHUNOLD	590.423.4567	2006-01-03 00:00:00.000	IT_PROG	9000	102	60	110	Accounting	205	1700
104	Bruce	Ernst	BERNST	590.423.4568	2007-05-21 00:00:00.000	IT_PROG	6000	103	60	110	Accounting	205	1700
105	David	Austin	DAUSTIN	590.423.4569	2005-06-25 00:00:00.000	IT_PROG	4800	103	60	110	Accounting	205	1700
106	Valli	Pataballa	VPATABAL	590.423.4560	2006-02-05 00:00:00.000	IT_PROG	4800	103	60	110	Accounting	205	1700
107	Diana	Lorentz	DLORENTZ	590.423.5567	2007-02-07 00:00:00.000	IT_PROG	4200	103	60	110	Accounting	205	1700
108	Nancy	Greenberg	NGREENBE	515.124.4569	2002-08-17 00:00:00.000	FI_MGR	12008	101	100	110	Accounting	205	1700
109	Daniel	Faviet	DFAVIET	515.124.4169	2002-08-16 00:00:00.000	FI_ACCOUNT	9000	108	100	110	Accounting	205	1700

Snowflake¶

 SELECT
    *
FROM
hr.employees
CROSS JOIN hr.departments
ORDER BY department_name, employee_id
FETCH NEXT 10 ROWS ONLY;

Result¶


EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	MANAGER_ID	DEPARTMENT_ID	DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000.00		90	110	Accounting	205	1700
101	Neena	Kochhar	NKOCHHAR	515.123.4568	2005-09-21	AD_VP	17000.00	100	90	110	Accounting	205	1700
102	Lex	De Haan	LDEHAAN	515.123.4569	2001-01-13	AD_VP	17000.00	100	90	110	Accounting	205	1700
103	Alexander	Hunold	AHUNOLD	590.423.4567	2006-01-03	IT_PROG	9000.00	102	60	110	Accounting	205	1700
104	Bruce	Ernst	BERNST	590.423.4568	2007-05-21	IT_PROG	6000.00	103	60	110	Accounting	205	1700
105	David	Austin	DAUSTIN	590.423.4569	2005-06-25	IT_PROG	4800.00	103	60	110	Accounting	205	1700
106	Valli	Pataballa	VPATABAL	590.423.4560	2006-02-05	IT_PROG	4800.00	103	60	110	Accounting	205	1700
107	Diana	Lorentz	DLORENTZ	590.423.5567	2007-02-07	IT_PROG	4200.00	103	60	110	Accounting	205	1700
108	Nancy	Greenberg	NGREENBE	515.124.4569	2002-08-17	FI_MGR	12008.00	101	100	110	Accounting	205	1700
109	Daniel	Faviet	DFAVIET	515.124.4169	2002-08-16	FI_ACCOUNT	9000.00	108	100	110	Accounting	205	1700

Natural Inner Join¶

Oracle¶

SELECT 
    *
FROM 
    hr.employees
NATURAL JOIN hr.departments
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;

Result¶


MANAGER_ID	DEPARTMENT_ID	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	DEPARTMENT_NAME	LOCATION_ID
100	90	101	Neena	Kochhar	NKOCHHAR	515.123.4568	2005-09-21 00:00:00.000	AD_VP	17000	Executive	1700
100	90	102	Lex	De Haan	LDEHAAN	515.123.4569	2001-01-13 00:00:00.000	AD_VP	17000	Executive	1700
103	60	104	Bruce	Ernst	BERNST	590.423.4568	2007-05-21 00:00:00.000	IT_PROG	6000	IT	1400
103	60	105	David	Austin	DAUSTIN	590.423.4569	2005-06-25 00:00:00.000	IT_PROG	4800	IT	1400
103	60	106	Valli	Pataballa	VPATABAL	590.423.4560	2006-02-05 00:00:00.000	IT_PROG	4800	IT	1400
103	60	107	Diana	Lorentz	DLORENTZ	590.423.5567	2007-02-07 00:00:00.000	IT_PROG	4200	IT	1400
108	100	109	Daniel	Faviet	DFAVIET	515.124.4169	2002-08-16 00:00:00.000	FI_ACCOUNT	9000	Finance	1700
108	100	110	John	Chen	JCHEN	515.124.4269	2005-09-28 00:00:00.000	FI_ACCOUNT	8200	Finance	1700
108	100	111	Ismael	Sciarra	ISCIARRA	515.124.4369	2005-09-30 00:00:00.000	FI_ACCOUNT	7700	Finance	1700
108	100	112	Jose Manuel	Urman	JMURMAN	515.124.4469	2006-03-07 00:00:00.000	FI_ACCOUNT	7800	Finance	1700

Snowflake¶

SELECT
    *
FROM
hr.employees
NATURAL JOIN
    hr.departments
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;

Result¶


MANAGER_ID	DEPARTMENT_ID	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	DEPARTMENT_NAME	LOCATION_ID
100	90	101	Neena	Kochhar	NKOCHHAR	515.123.4568	2005-09-21	AD_VP	17000.00	Executive	1700
100	90	102	Lex	De Haan	LDEHAAN	515.123.4569	2001-01-13	AD_VP	17000.00	Executive	1700
103	60	104	Bruce	Ernst	BERNST	590.423.4568	2007-05-21	IT_PROG	6000.00	IT	1400
103	60	105	David	Austin	DAUSTIN	590.423.4569	2005-06-25	IT_PROG	4800.00	IT	1400
103	60	106	Valli	Pataballa	VPATABAL	590.423.4560	2006-02-05	IT_PROG	4800.00	IT	1400
103	60	107	Diana	Lorentz	DLORENTZ	590.423.5567	2007-02-07	IT_PROG	4200.00	IT	1400
108	100	109	Daniel	Faviet	DFAVIET	515.124.4169	2002-08-16	FI_ACCOUNT	9000.00	Finance	1700
108	100	110	John	Chen	JCHEN	515.124.4269	2005-09-28	FI_ACCOUNT	8200.00	Finance	1700
108	100	111	Ismael	Sciarra	ISCIARRA	515.124.4369	2005-09-30	FI_ACCOUNT	7700.00	Finance	1700
108	100	112	Jose Manuel	Urman	JMURMAN	515.124.4469	2006-03-07	FI_ACCOUNT	7800.00	Finance	1700

Cross Natural Join¶

Oracle¶

SELECT 
    *
FROM 
    hr.employees
CROSS NATURAL JOIN hr.departments
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;

Result¶


MANAGER_ID	DEPARTMENT_ID	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	DEPARTMENT_NAME	LOCATION_ID
100	90	101	Neena	Kochhar	NKOCHHAR	515.123.4568	2005-09-21 00:00:00.000	AD_VP	17000	Executive	1700
100	90	102	Lex	De Haan	LDEHAAN	515.123.4569	2001-01-13 00:00:00.000	AD_VP	17000	Executive	1700
103	60	104	Bruce	Ernst	BERNST	590.423.4568	2007-05-21 00:00:00.000	IT_PROG	6000	IT	1400
103	60	105	David	Austin	DAUSTIN	590.423.4569	2005-06-25 00:00:00.000	IT_PROG	4800	IT	1400
103	60	106	Valli	Pataballa	VPATABAL	590.423.4560	2006-02-05 00:00:00.000	IT_PROG	4800	IT	1400
103	60	107	Diana	Lorentz	DLORENTZ	590.423.5567	2007-02-07 00:00:00.000	IT_PROG	4200	IT	1400
108	100	109	Daniel	Faviet	DFAVIET	515.124.4169	2002-08-16 00:00:00.000	FI_ACCOUNT	9000	Finance	1700
108	100	110	John	Chen	JCHEN	515.124.4269	2005-09-28 00:00:00.000	FI_ACCOUNT	8200	Finance	1700
108	100	111	Ismael	Sciarra	ISCIARRA	515.124.4369	2005-09-30 00:00:00.000	FI_ACCOUNT	7700	Finance	1700
108	100	112	Jose Manuel	Urman	JMURMAN	515.124.4469	2006-03-07 00:00:00.000	FI_ACCOUNT	7800	Finance	1700

Snowflake¶

SELECT
    *
FROM
    hr.employees
    NATURAL JOIN
        hr.departments
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;

Result¶


MANAGER_ID	DEPARTMENT_ID	EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	DEPARTMENT_NAME	LOCATION_ID
100	90	101	Neena	Kochhar	NKOCHHAR	515.123.4568	2005-09-21	AD_VP	17000.00	Executive	1700
100	90	102	Lex	De Haan	LDEHAAN	515.123.4569	2001-01-13	AD_VP	17000.00	Executive	1700
103	60	104	Bruce	Ernst	BERNST	590.423.4568	2007-05-21	IT_PROG	6000.00	IT	1400
103	60	105	David	Austin	DAUSTIN	590.423.4569	2005-06-25	IT_PROG	4800.00	IT	1400
103	60	106	Valli	Pataballa	VPATABAL	590.423.4560	2006-02-05	IT_PROG	4800.00	IT	1400
103	60	107	Diana	Lorentz	DLORENTZ	590.423.5567	2007-02-07	IT_PROG	4200.00	IT	1400
108	100	109	Daniel	Faviet	DFAVIET	515.124.4169	2002-08-16	FI_ACCOUNT	9000.00	Finance	1700
108	100	110	John	Chen	JCHEN	515.124.4269	2005-09-28	FI_ACCOUNT	8200.00	Finance	1700
108	100	111	Ismael	Sciarra	ISCIARRA	515.124.4369	2005-09-30	FI_ACCOUNT	7700.00	Finance	1700
108	100	112	Jose Manuel	Urman	JMURMAN	515.124.4469	2006-03-07	FI_ACCOUNT	7800.00	Finance	1700

Natural Cross Join¶

Oracle¶

SELECT 
    *
FROM 
    hr.employees
NATURAL CROSS JOIN hr.departments
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;

Result¶


EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	DEPARTMENT_ID	DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
100	Steven	King	SKING	515.123.4567	2003-06-17 00:00:00.000	AD_PRES	24000	90	10	Administration	200	1700
100	Steven	King	SKING	515.123.4567	2003-06-17 00:00:00.000	AD_PRES	24000	90	100	Finance	108	1700
100	Steven	King	SKING	515.123.4567	2003-06-17 00:00:00.000	AD_PRES	24000	90	90	Executive	100	1700
100	Steven	King	SKING	515.123.4567	2003-06-17 00:00:00.000	AD_PRES	24000	90	80	Sales	145	2500
100	Steven	King	SKING	515.123.4567	2003-06-17 00:00:00.000	AD_PRES	24000	90	70	Public Relations	204	2700
100	Steven	King	SKING	515.123.4567	2003-06-17 00:00:00.000	AD_PRES	24000	90	60	IT	103	1400
100	Steven	King	SKING	515.123.4567	2003-06-17 00:00:00.000	AD_PRES	24000	90	50	Shipping	121	1500
100	Steven	King	SKING	515.123.4567	2003-06-17 00:00:00.000	AD_PRES	24000	90	40	Human Resources	203	2400
100	Steven	King	SKING	515.123.4567	2003-06-17 00:00:00.000	AD_PRES	24000	90	30	Purchasing	114	1700
100	Steven	King	SKING	515.123.4567	2003-06-17 00:00:00.000	AD_PRES	24000	90	20	Marketing	201	1800

Snowflake¶

SELECT
    *
FROM
    hr.employees
    CROSS JOIN hr.departments
ORDER BY employee_id
FETCH NEXT 10 ROWS ONLY;

Result¶


EMPLOYEE_ID	FIRST_NAME	LAST_NAME	EMAIL	PHONE_NUMBER	HIRE_DATE	JOB_ID	SALARY	DEPARTMENT_ID	DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000.00	90	80	Sales	145	2500
100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000.00	90	20	Marketing	201	1800
100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000.00	90	60	IT	103	1400
100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000.00	90	70	Public Relations	204	2700
100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000.00	90	90	Executive	100	1700
100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000.00	90	30	Purchasing	114	1700
100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000.00	90	10	Administration	200	1700
100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000.00	90	100	Finance	108	1700
100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000.00	90	50	Shipping	121	1500
100	Steven	King	SKING	515.123.4567	2003-06-17	AD_PRES	24000.00	90	40	Human Resources	203	2400

Known issues¶

1. Results ordering mismatch between languages¶

The result of the query will have the same content in both database engines but the order might be different if no Order By clause is defined in the query.

Related EWIs¶

No related EWIs.

Outer Join¶

Note

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, see the Snowflake JOIN documentation.

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¶

Note

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

Note

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

Note

Check this section to set up the sample database.

Note

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¶

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¶

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¶

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¶


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¶

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¶


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¶

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¶


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¶

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¶


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.

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¶

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

Result¶


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¶

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

Result¶


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¶

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

Result¶


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¶

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

Result¶


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¶

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

Result¶


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¶

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

Result¶


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.

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

Left Outer Join with (+) operator¶

Oracle¶

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¶


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¶

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¶


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¶

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¶


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¶

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¶


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¶

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¶


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¶

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;

Result¶


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¶

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

Result¶


REGION_ID	REGION_NAME
2	Americas
3	Asia

Snowflake¶

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

Result¶


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 the 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¶

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¶


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¶

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;

Result¶


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¶

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¶


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¶

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¶

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

Self Join¶

Note

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

Description¶

A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition. (Oracle SQL Language Reference Self Join Subsection)

Sample Source Patterns¶

Note

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

Note

Check this section to set up the sample database.

Basic Self Join case¶

Oracle¶

SELECT e1.last_name||' works for '||e2.last_name 
   "Employees and Their Managers"
   FROM hr.employees e1, hr.employees e2 
   WHERE e1.manager_id = e2.employee_id
      AND e1.last_name LIKE 'R%'
   ORDER BY e1.last_name;

Result¶


Employees and Their Managers
Rajs works for Mourgos
Raphaely works for King
Rogers works for Kaufling
Russell works for King

Snowflake¶

SELECT
   NVL( e1.last_name :: STRING, '') || ' works for ' || NVL(e2.last_name :: STRING, '') "Employees and Their Managers"
FROM
   hr.employees e1,
   hr.employees e2
   WHERE e1.manager_id = e2.employee_id
      AND e1.last_name LIKE 'R%'
   ORDER BY e1.last_name;

Result¶


Employees and Their Managers
Rajs works for Mourgos
Raphaely works for King
Rogers works for Kaufling
Russell works for King

Note

As proved previously the self join in Oracle is functionally equivalent to Snowflake.

Known Issues¶

No issues were found.

Related EWIs¶

No related EWIs.

Semijoin¶

Note

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

Description¶

A semijoin returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery. Semijoin transformation cannot be done if the subquery is on an OR branch of the WHERE clause. (Oracle SQL Language Reference Semijoin Subsection)

Sample Source Patterns¶

Note

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

Note

Check this section to set up the sample database.

Basic Semijoin case¶

Oracle¶

SELECT * FROM hr.departments 
   WHERE EXISTS 
   (SELECT * FROM hr.employees 
       WHERE departments.department_id = employees.department_id 
       AND employees.salary > 2500)
   ORDER BY department_name;

Result¶


DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
110	Accounting	205	1700
10	Administration	200	1700
90	Executive	100	1700
100	Finance	108	1700
40	Human Resources	203	2400
60	IT	103	1400
20	Marketing	201	1800
70	Public Relations	204	2700
30	Purchasing	114	1700
80	Sales	145	2500
50	Shipping	121	1500

Snowflake¶

SELECT * FROM
   hr.departments
   WHERE EXISTS
   (SELECT * FROM
         hr.employees
       WHERE departments.department_id = employees.department_id
       AND employees.salary > 2500)
   ORDER BY department_name;

Result¶


DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID
110	Accounting	205	1700
10	Administration	200	1700
90	Executive	100	1700
100	Finance	108	1700
40	Human Resources	203	2400
60	IT	103	1400
20	Marketing	201	1800
70	Public Relations	204	2700
30	Purchasing	114	1700
80	Sales	145	2500
50	Shipping	121	1500

Note

As proved previously the semijoin in Oracle is functionally equivalent to Snowflake.

Known Issues¶

No issues were found.

Related EWIs¶

No related EWIs.