SnowflakeDocumentation Home

Inner Join

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

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 this clause to retrieve the entire result set.

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

IN -> Oracle_01.sql
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;

Snowflake

OUT -> Oracle_01.sql
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;

Inner Join with using clause

Oracle

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