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
Previous:
LEFT OUTER JOIN
Next:
FULL OUTER JOIN