Joins in Oracle

Different types of Joins in Oracle

different_types_of_joins_in_oracle

Sometimes it is necessary to work with multiple tables as though they were a single entity.Then a single SQL sentence can manipulate data from all the tables. Joins are used to achieve this.

Join is a method used to combine two or more tables, views or materialized views based on a common condition.

Different types of Joins

  1. CROSS JOIN/CARTESIAN PRODUCT
  2. NATURAL JOIN
  3. [INNER] JOIN ... USING
  4. [INNER] JOIN ... ON
  5. Multiple Joins
  6. {LEFT | RIGHT | FULL} [OUTER] JOIN
    1. LEFT OUTER JOIN
    2. RIGHT JOIN
    3. FULL OUTER JOIN
  7. SELF JOIN

CROSS JOIN

The CROSS JOIN produces a Cartesian product. It is the equivalent of not specifying a join criteria.This join lists all the rows in all tables.

ANSI/ISO Syntax Existing Syntax/Theta Style
SELECT e.employee_name,
       d.department_name
FROM   employees e
       CROSS JOIN departments d;
SELECT e.employee_name,
       d.department_name
FROM   employees e, departments d;

NATURAL JOIN

The NATURAL JOIN performs a join for all columns with matching names in the two tables.

ANSI/ISO Syntax Existing Syntax/Theta Style
SELECT e.employee_name,
       d.department_name
FROM   employees e
       NATURAL JOIN departments d;

Can't reference the join column in a filter, so this isn't allowed.

SELECT e.employee_name,
       d.department_name
FROM   employees e
       NATURAL JOIN departments d
WHERE  d.department_id = 20

No equivalent syntax that doesn't reference columns.

[INNER] JOIN ... USING

The USING clause is used if several columns share the same name, but you do not wish to join using all of these common columns. The columns listed in the USING clause cannot have any qualifiers in the statement, including the WHERE clause.

ANSI/ISO Syntax Existing Syntax/Theta Style
SELECT e.employee_name,
       d.department_name
FROM   employees e
       JOIN departments d USING (department_id);

Can't reference the join column in a filter, so this isn't allowed.

SELECT e.employee_name,
       d.department_name
FROM   employees e
       JOIN departments d USING (department_id)
WHERE  d.department_id = 20;
SELECT d.department_name,
       e.employee_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id;

[INNER] JOIN ... ON

The ON clause is used to join tables where the column names do not match. The join conditions are removed from the filter conditions in the where clause.

ANSI/ISO Syntax Existing Syntax/Theta Style
SELECT d.department_name,
       e.employee_name
FROM   employees e
       JOIN departments d ON e.department_id = d.department_id;
SELECT d.department_name,
       e.employee_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id;

Multiple Joins

Multiple Joins are those where more than two tables are joined. The SQL: 1999 standard assumes the tables are joined from the left to the right, with the join conditions only being able to reference columns relating to the current join and any previous joins to the left.

ANSI/ISO Syntax Existing Syntax/Theta Style
SELECT employee_id,
       city,
       department_name
FROM   locations l
       JOIN departments d ON (d.location_id = l.location_id)
       JOIN employees e ON (d.department_id = e.department_id);
SELECT employee_id,
       city,
       department_name
FROM   locations l,
       departments d,
       employees e
WHERE  d.location_id   = l.location_id
AND    d.department_id = e.department_id;

{LEFT | RIGHT | FULL} [OUTER] JOIN

There are three variations on the outer join.

  1. Left Outer Join :
    The LEFT [OUTER] JOIN returns all the rows from the table on the left side of the join, along with the values from the right hand side, or NULLs if a matching row doesn't exist.
    ANSI/ISO Syntax Existing Syntax/Theta Style
    SELECT d.department_name,
           e.employee_name     
    FROM   departments d
           LEFT OUTER JOIN employees e ON d.department_id = e.department_id;
    SELECT d.department_name,
           e.employee_name
    FROM   departments d,
           employees e
    WHERE  d.department_id = e.department_id(+);

    Table order is irrelevant for non-ANSI syntax. It is placement of "(+)" that counts.

  2. Right Outer Join :
    The RIGHT [OUTER] JOIN returns all the rows from the table on the right side of the join, along with the values from the left hand side, or NULLs if a matching row doesn't exist.
    ANSI/ISO Syntax Existing Syntax/Theta Style
    SELECT d.department_name,
           e.employee_name     
    FROM   employees e
           RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;
    SELECT d.department_name,
           e.employee_name
    FROM   departments d,
           employees e
    WHERE  d.department_id = e.department_id(+);

    Table order is irrelevant for non-ANSI syntax. It is placement of "(+)" that counts.

  3. Full Outer Join :
    The FULL [OUTER] JOIN returns all rows from both tables, filling in any blanks with nulls.
    ANSI/ISO Syntax Existing Syntax/Theta Style
    SELECT d.department_name,
           e.employee_name     
    FROM   employees e
           FULL OUTER JOIN departments d ON e.department_id = d.department_id;
    
    SELECT d.department_name,
           e.employee_name      
    FROM   employees e, departments d
    WHERE  e.department_id = d.department_id (+)
    UNION ALL
    SELECT d.department_name,
           e.employee_name      
    FROM   departments d, employees e
    WHERE  d.department_id = e.department_id (+)
    AND    e.employee_name IS NULL;

    No equivalent as single join. Must use UNION or UNION ALL of two outer joins.

SELF JOIN

In some situations, it is necessary to join a table to itself, as though joining two separate tables. This is referred to as a SELF JOIN.

SELECT e.employee_name "Employee",
       d.fname "Manager"
FROM   employees e, 
       employees d 
WHERE e.mngr_no=d.empno;

Read Oracle Anti-Joins and Semi-Joins


If you like dEexams.com and would like to contribute, you can also write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.


Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.


Python if , elif and else

Python Conditions and If statements

  • 0
Python for beginners

Learning Python Part 1

  • 3
Struct Alignment and Padding

Struct Alignment and Padding in C++ And C

  • 0
Friend function

Friend function C++

  • 0
Pointers

C++ Pointers

  • 0
Structures

C++ Structures

  • 0
Types of Inheritance in C++

Inheritance and access specifiers C++

  • 0
Java date pattern

Java Date Pattern Syntax

  • 0
Java Date and Calendar

Java Date formats

  • 0
JAVA Data Type

Data types in Java

  • 0
Java unreachable code

Unreachable Code Error in Java

  • 0

Post Comment

Comments(0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.