Inner Join

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

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

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

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

Snowflake

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

Cross Inner Join

Oracle

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

Snowflake

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

Natural Inner Join

Oracle

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

Snowflake

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

Cross Natural Join

Oracle

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

Snowflake

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

Natural Cross Join

Oracle

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

Snowflake

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

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

  1. MSCEWI1001: Error parsing the source code.

PreviousCartesian ProductsNextOuter Join

Last updated