Oracle RIGHT OUTER JOIN

What is a right outer join in Oracle?

A RIGHT OUTER JOIN performs an inner join of two tables (supposed table A which writes before the join keyword and table B which writes after the join keyword in the SQL statement ) based on the condition specified after the ON keyword. It returns all rows from the table B as well as the unmatched rows from the table A. For all rows in B that have no matching rows in A, Oracle Database returns null for any select list expressions containing columns of A.

Syntax:

SELECT table1.column, table2.column
FROM table1
RIGHT OUTER JOIN table2
ON (table1.column = table2.column);

Pictorial presentation of Oracle Right Outer Join

Example-1: Oracle Right Outer Join

The following query retrieves all the matching rows in the departments table, and employees table for the criteria same department_id in both tables, and also those rows from employees table even if there is no match in the departments table.

Sample table: employees

Sample table: departments

SELECT e.first_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);

Sample Output:

FIRST_NAME           DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------- ------------------
Jennifer                        10 Administration
Pat                             20 Marketing
Michael                         20 Marketing
Sigal                           30 Purchasing
Karen                           30 Purchasing
Shelli                          30 Purchasing
Den                             30 Purchasing
Timothy                         50 Shipping
............................................
............................................
William                        110 Accounting
Shelley                        110 Accounting
                                   Treasury
                                   Corporate Tax
                                   Control And Credit
                                   Shareholder Services
                                   Benefits
                                   Manufacturing
                                   Construction
.............................................								   

Example-2:

Write a SQL statement to show the department number, name, number of employees in each department , and average salary of all departments, together with the first names, salaries, and jobs of the employees working in each department.

Sample table: employees

Sample table: departments

SELECT a.department_id, a.department_name,
count(m1.employee_id) employees,
NVL(TO_CHAR(AVG(m1.salary), '99999.99'), '- Nil -') avg_sal,
m2.first_name, m2.salary, m2.job_id
FROM departments a RIGHT OUTER JOIN employees m1
ON a.department_id = m1.department_id
RIGHT OUTER JOIN employees m2
ON a.department_id = m2.department_id
GROUP BY a.department_id, a.department_name, m2.first_name,
m2.salary,  m2.job_id
ORDER BY a.department_id, employees;

Sample Output:

DEPARTMENT_ID DEPARTMENT_NAME                 EMPLOYEES AVG_SAL   LAST_NAME                     SALARY JOB_ID
------------- ------------------------------ ---------- --------- ------------------------- ---------- ---------
           10 Administration                          1   4400.00 Whalen                          4400 AD_ASST
           20 Marketing                               2   9500.00 Hartstein                      13000 MK_MAN
           20 Marketing                               2   9500.00 Fay                             6000 MK_REP
           30 Purchasing                              6   4150.00 Baida                           2900 PU_CLERK
           30 Purchasing                              6   4150.00 Himuro                          2600 PU_CLERK
           30 Purchasing                              6   4150.00 Tobias                          2800 PU_CLERK
           30 Purchasing                              6   4150.00 Raphaely                       11000 PU_MAN
           30 Purchasing                              6   4150.00 Colmenares                      2500 PU_CLERK
           30 Purchasing                              6   4150.00 Khoo                            3100 PU_CLERK
           40 Human Resources                         1   6500.00 Mavris                          6500 HR_REP
           50 Shipping                               45   3475.56 Stiles                          3200 ST_CLERK
................................................................................................................
................................................................................................................
          100 Finance                                 6   8601.33 Sciarra                         7700 FI_ACCOUNT
          100 Finance                                 6   8601.33 Faviet                          9000 FI_ACCOUNT
          100 Finance                                 6   8601.33 Greenberg                      12008 FI_MGR
          100 Finance                                 6   8601.33 Urman                           7800 FI_ACCOUNT
          100 Finance                                 6   8601.33 Chen                            8200 FI_ACCOUNT
          100 Finance                                 6   8601.33 Popp                            6900 FI_ACCOUNT
          110 Accounting                              2  10154.00 Gietz                           8300 AC_ACCOUNT
          110 Accounting                              2  10154.00 Higgins                        12008 AC_MGR
                                                      0 - Nil -   Grant                           7000 SA_REP								   

RIGHT JOIN: SQL and other Relational Databases

• SQL RIGHT JOIN
• MySQL RIGHT JOIN
• PostgreSQL RIGHT JOIN

Previous: LEFT OUTER JOIN
Next: FULL OUTER JOIN