Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

Oracle Outer Joins – Standard/ANSI

Leave a Comment / SQL, JOINS / By

Oracle Outer Joins – Standard/ANSI

Oracle Outer Join Overview:

We generally use Joins in oracle to fetch the matching rows from two or more tables.

But if we want fetch those rows which do not have any matching pair in other tables then we will use Outer Join.

Setup for Outer Joins:

CREATE TABLE DEPARTMENTS_OUTER
AS
SELECT * FROM DEPARTMENTS
WHERE 1 = 2;
INSERT INTO DEPARTMENTS_OUTER
SELECT * FROM DEPARTMENTS
WHERE DEPARTMENT_ID IN (10, 20, 30, 40, 200, 210, 230);
CREATE TABLE EMPLOYEES_OUTER
AS
SELECT * FROM EMPLOYEES
WHERE 1 = 2;
INSERT INTO EMPLOYEES_OUTER
SELECT * FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (10, 20, 30, 40);
--Oracle Outer Joins - Standard/ANSI : Example

Insert into EMPLOYEES_OUTER 
(EMPLOYEE_ID,
 FIRST_NAME,
 LAST_NAME,EMAIL,
 PHONE_NUMBER,
 HIRE_DATE,JOB_ID,
 SALARY,
 COMMISSION_PCT,
 MANAGER_ID,
 DEPARTMENT_ID
) 
 values 
(103,
 'Alexander',
 'Hunold',
 'AHUNOLD',
 '590.423.4567',
 to_date('03-01-06','DD-MM-RR'),
 'IT_PROG',
 9000,
 null,
 102,
 NULL);
--
Insert into EMPLOYEES_OUTER 
(EMPLOYEE_ID,
 FIRST_NAME,
 LAST_NAME,EMAIL,
 PHONE_NUMBER,
 HIRE_DATE,JOB_ID,
 SALARY,
 COMMISSION_PCT,
 MANAGER_ID,
 DEPARTMENT_ID
)  
values 
(104,
 'Bruce',
 'ErnsT',
 'berNST@123',
 '590.423.4568',
 to_date('21-05-07','DD-MM-RR'),
 'IT_PROG',
 6000,
 null,
 103,
 NULL);

--
Insert into EMPLOYEES_OUTER 
(EMPLOYEE_ID,
 FIRST_NAME,
 LAST_NAME,EMAIL,
 PHONE_NUMBER,
 HIRE_DATE,JOB_ID,
 SALARY,
 COMMISSION_PCT,
 MANAGER_ID,
 DEPARTMENT_ID
) 
 values 
(105,
 'David',
 'Austin',
 'DAUSTIN@123',
 '590.423.4569',
 to_date('25-06-05','DD-MM-RR'),
 'IT_PROG',
 4800,
 null,
 103,
 NULL);

--
Insert into EMPLOYEES_OUTER 
(EMPLOYEE_ID,
 FIRST_NAME,
 LAST_NAME,EMAIL,
 PHONE_NUMBER,
 HIRE_DATE,JOB_ID,
 SALARY,
 COMMISSION_PCT,
 MANAGER_ID,
 DEPARTMENT_ID
) 
 values 
(106,
 'Valli',
 'Pataballa',
 'VPATABAL',
 '590.423.4560',
 to_date('05-02-06','DD-MM-RR'),
 'IT_PROG',
 4800,
 null,
 103,
 NULL);

--
Insert into EMPLOYEES_OUTER 
(EMPLOYEE_ID,
 FIRST_NAME,
 LAST_NAME,EMAIL,
 PHONE_NUMBER,
 HIRE_DATE,JOB_ID,
 SALARY,
 COMMISSION_PCT,
 MANAGER_ID,
 DEPARTMENT_ID
) 
 values 
(107,
 'Diana',
 'Lorentz',
 'DLORENTZ',
 '590.423.5567',
 to_date('07-02-07','DD-MM-RR'),
 'IT_PROG',
 4200,
 null,
 103,
 NULL);

COMMIT;
SELECT * FROM DEPARTMENTS_OUTER;
SELECT * FROM EMPLOYEES_OUTER;

In the above setup we are using two tables DEPARTMENTS_OUTER and EMPLOYEES_OUTER. Few data in DEPARTMENTS_OUTER has no matching rows in EMPLOYEES_OUTER table. Similarly few data in EMPLOYEES_OUTER table has no matching rows in DEPARMENTS_OUTER table.

Matching Data with Normal Join:

--Oracle Outer Joins - Standard/ANSI : Example

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A,
     DEPARTMENTS_OUTER B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;

Left Outer Join:

Left Outer Join shows the data exists in driving table but not exists in other table. Here in the example, EMPLOYEES_OUTER is the driving table and DEPARTMENTS_OUTER is the other table. Left Outer Join will include those employees who are not assigned to any departments.

Standard Format:
--Oracle Outer Joins - Standard/ANSI : Example

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A,
     DEPARTMENTS_OUTER B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID(+);
Oracle Outer Joins - Standard/ANSI: Output
ANSI Format:
--Oracle Outer Joins - Standard/ANSI : Example

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A
LEFT OUTER JOIN DEPARTMENTS_OUTER B
ON (A.DEPARTMENT_ID = B.DEPARTMENT_ID);

Right Outer Join:

Right Outer Join shows the data not exists in driving table but exists in other table. In the example, Right Outer Join will show all the Departments where no employee is assigned.

Standard Format:
--Oracle Outer Joins - Standard/ANSI : Example

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A,
     DEPARTMENTS_OUTER B
WHERE A.DEPARTMENT_ID(+) = B.DEPARTMENT_ID;
Oracle Outer Joins – Standard/ANSI : Output
ANSI Format:
SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A
RIGHT OUTER JOIN DEPARTMENTS_OUTER B
ON (A.DEPARTMENT_ID = B.DEPARTMENT_ID);

Full Outer Join:

If we want to show all the matching and non matching rows from both EMPLOYEES_OUTER and DEPARTMENTS_OUTER tables then we will use Full Outer Join.

Standard Format:

For Standard format no such specified format is available. But we can use the below query. It is an expensive query from performance perspective.

--Oracle Outer Joins - Standard/ANSI : Example

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A,
     DEPARTMENTS_OUTER B
WHERE A.DEPARTMENT_ID(+) = B.DEPARTMENT_ID
UNION
SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A,
     DEPARTMENTS_OUTER B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID(+)
ORDER BY DEPARTMENT_ID NULLS LAST;
ANSI Format:
--Oracle Outer Joins - Standard/ANSI : Example ANSI

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A
FULL OUTER JOIN DEPARTMENTS_OUTER B
ON (A.DEPARTMENT_ID = B.DEPARTMENT_ID);

LATERAL Inline Views:

  • Normally its not possible to refer any table outside the Inline View definition.
  • A Lateral Inline View allows us to refer any table left side of Inline View definition in the from clause.
  • Its allowing the Inline View to be correlated with the outer table.
  • Its available from 12c onwards
--Oracle Outer Joins - Standard/ANSI : Example LATERAL View

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A,
     (SELECT * FROM DEPARTMENTS_OUTER
      WHERE DEPARTMENT_ID = A.DEPARTMENT_ID) B;
--Oracle Outer Joins - Standard/ANSI : Example LATERAL View

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A,
     LATERAL (SELECT * FROM DEPARTMENTS_OUTER
              WHERE DEPARTMENT_ID = A.DEPARTMENT_ID) B;
Using Additional Condition:
--Oracle Outer Joins - Standard/ANSI : Example LATERAL View

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A,
     LATERAL (SELECT * FROM DEPARTMENTS_OUTER
              WHERE DEPARTMENT_ID = A.DEPARTMENT_ID) B
WHERE A.DEPARTMENT_ID = 30;

CROSS APPLY:

  • Its a variant of ANSI Cross Join which supports Correlation
  • It works like normal join between 2 tables
  • It returns all the rows from left hand tables where at least one row is returned by the table from the right
  • The table from right side join can refer columns from the left side of the join in the From clause
  • Its available from 12c onwards
--Oracle Outer Joins - Standard/ANSI : Example CROSS APPLY

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A
     CROSS APPLY (SELECT * FROM DEPARTMENTS_OUTER
                  WHERE DEPARTMENT_ID = A.DEPARTMENT_ID
                  AND DEPARTMENT_ID = 30) B;

If the right side returns no rows then the query will also return no rows.

--Oracle Outer Joins - Standard/ANSI : Example CROSS APPLY

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A
     CROSS APPLY (SELECT * FROM DEPARTMENTS_OUTER
                  WHERE DEPARTMENT_ID = A.DEPARTMENT_ID
                  AND DEPARTMENT_ID = 330) B;

OUTER APPLY:

  • OUTER APPLY a variant of LEFT OUTER JOIN which supports correlation
  • Its works like Left Outer Join
  • Its usage is similar to CROSSS APPLY Join. It returns all the rows from left side of the join.
  • If the tables from right side of the join returns no rows then the corresponding column will contain nulls.
  • Its available from 12c onwards
--Oracle Outer Joins - Standard/ANSI : Example OUTER APPLY

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A
     OUTER APPLY (SELECT * FROM DEPARTMENTS_OUTER
                  WHERE DEPARTMENT_ID = A.DEPARTMENT_ID
                 ) B;

Using condition which returns no rows from right side of the join.

--Oracle Outer Joins - Standard/ANSI : Example OUTER APPLY

SELECT A.EMPLOYEE_ID,
       A.FIRST_NAME,
       A.LAST_NAME,
       A.JOB_ID,
       A.SALARY,
       A.DEPARTMENT_ID,
       B.DEPARTMENT_NAME
FROM EMPLOYEES_OUTER A
     OUTER APPLY (SELECT * FROM DEPARTMENTS_OUTER
                  WHERE DEPARTMENT_ID = A.DEPARTMENT_ID
                  AND DEPARTMENT_ID = 330
                 ) B;

RELATED TOPICS:

Author

Leave a Comment

Your email address will not be published.