SQL Right Outer Join in Oracle SQL | Oracle SQL Tutorials -22

Hi,

I will explain SQL Right Outer Join in Oracle SQL in this post of Oracle SQL Tutorial series.

Read the previous post of this tutorial series before this.

SQL Left Outer Join in Oracle SQL | Oracle SQL Tutorials -21

 

 

 

The Right Outer Join

The Right Outer Join is used to list the records from the right table and the matched records from the left table.

 

 

Right (Outer) JOIN Syntax

Right Outer Join syntax is as follows.

 

SELECT column1,column2,column(n)...
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
blank

You can use right outer join as follows. Right outer join and Right join clause run the same as follows.

SQL> SELECT first_name,e.last_name, e.department_id, d.department_name
  2     FROM   hr.employees e RIGHT OUTER JOIN hr.departments d
  3     ON   (e.department_id = d.department_id) ;

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Jennifer             Whalen                               10 Administration
Michael              Hartstein                            20 Marketing
Pat                  Fay                                  20 Marketing
Den                  Raphaely                             30 Purchasing
Alexander            Khoo                                 30 Purchasing
Shelli               Baida                                30 Purchasing
Sigal                Tobias                               30 Purchasing
Guy                  Himuro                               30 Purchasing
Karen                Colmenares                           30 Purchasing
Susan                Mavris                               40 Human Resources
Matthew              Weiss                                50 Shipping

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Adam                 Fripp                                50 Shipping
Payam                Kaufling                             50 Shipping
Shanta               Vollman                              50 Shipping
Kevin                Mourgos                              50 Shipping
Julia                Nayer                                50 Shipping
Irene                Mikkilineni                          50 Shipping
James                Landry                               50 Shipping
Steven               Markle                               50 Shipping
Laura                Bissot                               50 Shipping
Mozhe                Atkinson                             50 Shipping
James                Marlow                               50 Shipping

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
TJ                   Olson                                50 Shipping
Jason                Mallin                               50 Shipping
Michael              Rogers                               50 Shipping
Ki                   Gee                                  50 Shipping
Hazel                Philtanker                           50 Shipping
Renske               Ladwig                               50 Shipping
Stephen              Stiles                               50 Shipping
John                 Seo                                  50 Shipping
Joshua               Patel                                50 Shipping
Trenna               Rajs                                 50 Shipping
Curtis               Davies                               50 Shipping

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Randall              Matos                                50 Shipping
Peter                Vargas                               50 Shipping
Winston              Taylor                               50 Shipping
Jean                 Fleaur                               50 Shipping
Martha               Sullivan                             50 Shipping
Girard               Geoni                                50 Shipping
Nandita              Sarchand                             50 Shipping
Alexis               Bull                                 50 Shipping
Julia                Dellinger                            50 Shipping
Anthony              Cabrio                               50 Shipping
Kelly                Chung                                50 Shipping

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Jennifer             Dilly                                50 Shipping
Timothy              Gates                                50 Shipping
Randall              Perkins                              50 Shipping
Sarah                Bell                                 50 Shipping
Britney              Everett                              50 Shipping
Samuel               McCain                               50 Shipping
Vance                Jones                                50 Shipping
Alana                Walsh                                50 Shipping
Kevin                Feeney                               50 Shipping
Donald               OConnell                             50 Shipping
Douglas              Grant                                50 Shipping

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Alexander            Hunold                               60 IT
Bruce                Ernst                                60 IT
David                Austin                               60 IT
Valli                Pataballa                            60 IT
Diana                Lorentz                              60 IT
Hermann              Baer                                 70 Public Relations
John                 Russell                              80 Sales
Karen                Partners                             80 Sales
Alberto              Errazuriz                            80 Sales
Gerald               Cambrault                            80 Sales
Eleni                Zlotkey                              80 Sales

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Peter                Tucker                               80 Sales
David                Bernstein                            80 Sales
Peter                Hall                                 80 Sales
Christopher          Olsen                                80 Sales
Nanette              Cambrault                            80 Sales
Oliver               Tuvault                              80 Sales
Janette              King                                 80 Sales
Patrick              Sully                                80 Sales
Allan                McEwen                               80 Sales
Lindsey              Smith                                80 Sales
Louise               Doran                                80 Sales

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Sarath               Sewall                               80 Sales
Clara                Vishney                              80 Sales
Danielle             Greene                               80 Sales
Mattea               Marvins                              80 Sales
David                Lee                                  80 Sales
Sundar               Ande                                 80 Sales
Amit                 Banda                                80 Sales
Lisa                 Ozer                                 80 Sales
Harrison             Bloom                                80 Sales
Tayler               Fox                                  80 Sales
William              Smith                                80 Sales

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Elizabeth            Bates                                80 Sales
Sundita              Kumar                                80 Sales
Ellen                Abel                                 80 Sales
Alyssa               Hutton                               80 Sales
Jonathon             Taylor                               80 Sales
Jack                 Livingston                           80 Sales
Charles              Johnson                              80 Sales
Neena                Kochhar                              90 Executive
Lex                  De Haan                              90 Executive
Nancy                Greenberg                           100 Finance
Daniel               Faviet                              100 Finance

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
John                 Chen                                100 Finance
Ismael               Sciarra                             100 Finance
Jose Manuel          Urman                               100 Finance
Luis                 Popp                                100 Finance
Shelley              Higgins                             110 Accounting
William              Gietz                               110 Accounting
                                                             Treasury
                                                             Corporate Tax
                                                             Control And Credit
                                                             Shareholder Services
                                                             Benefits

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
                                                             Manufacturing
                                                             Construction
                                                             Contracting
                                                             Operations
                                                             IT Support
                                                             NOC
                                                             IT Helpdesk
                                                             Government Sales
                                                             Retail Sales
                                                             Recruiting
                                                             Payroll

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
                                                             Computer Engineering
                                                             IT TUTORIAL

123 rows selected.

SQL> 


You can run the above sql as follows without OUTER clause, it runs the same result.



SQL> SELECT first_name,e.last_name, e.department_id, d.department_name
  2     FROM   hr.employees e RIGHT JOIN hr.departments d
  3     ON   (e.department_id = d.department_id);

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Jennifer             Whalen                               10 Administration
Michael              Hartstein                            20 Marketing
Pat                  Fay                                  20 Marketing
Den                  Raphaely                             30 Purchasing
Alexander            Khoo                                 30 Purchasing
Shelli               Baida                                30 Purchasing
Sigal                Tobias                               30 Purchasing
Guy                  Himuro                               30 Purchasing
Karen                Colmenares                           30 Purchasing
Susan                Mavris                               40 Human Resources
Matthew              Weiss                                50 Shipping

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Adam                 Fripp                                50 Shipping
Payam                Kaufling                             50 Shipping
Shanta               Vollman                              50 Shipping
Kevin                Mourgos                              50 Shipping
Julia                Nayer                                50 Shipping
Irene                Mikkilineni                          50 Shipping
James                Landry                               50 Shipping
Steven               Markle                               50 Shipping
Laura                Bissot                               50 Shipping
Mozhe                Atkinson                             50 Shipping
James                Marlow                               50 Shipping

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
TJ                   Olson                                50 Shipping
Jason                Mallin                               50 Shipping
Michael              Rogers                               50 Shipping
Ki                   Gee                                  50 Shipping
Hazel                Philtanker                           50 Shipping
Renske               Ladwig                               50 Shipping
Stephen              Stiles                               50 Shipping
John                 Seo                                  50 Shipping
Joshua               Patel                                50 Shipping
Trenna               Rajs                                 50 Shipping
Curtis               Davies                               50 Shipping

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Randall              Matos                                50 Shipping
Peter                Vargas                               50 Shipping
Winston              Taylor                               50 Shipping
Jean                 Fleaur                               50 Shipping
Martha               Sullivan                             50 Shipping
Girard               Geoni                                50 Shipping
Nandita              Sarchand                             50 Shipping
Alexis               Bull                                 50 Shipping
Julia                Dellinger                            50 Shipping
Anthony              Cabrio                               50 Shipping
Kelly                Chung                                50 Shipping

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Jennifer             Dilly                                50 Shipping
Timothy              Gates                                50 Shipping
Randall              Perkins                              50 Shipping
Sarah                Bell                                 50 Shipping
Britney              Everett                              50 Shipping
Samuel               McCain                               50 Shipping
Vance                Jones                                50 Shipping
Alana                Walsh                                50 Shipping
Kevin                Feeney                               50 Shipping
Donald               OConnell                             50 Shipping
Douglas              Grant                                50 Shipping

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Alexander            Hunold                               60 IT
Bruce                Ernst                                60 IT
David                Austin                               60 IT
Valli                Pataballa                            60 IT
Diana                Lorentz                              60 IT
Hermann              Baer                                 70 Public Relations
John                 Russell                              80 Sales
Karen                Partners                             80 Sales
Alberto              Errazuriz                            80 Sales
Gerald               Cambrault                            80 Sales
Eleni                Zlotkey                              80 Sales

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Peter                Tucker                               80 Sales
David                Bernstein                            80 Sales
Peter                Hall                                 80 Sales
Christopher          Olsen                                80 Sales
Nanette              Cambrault                            80 Sales
Oliver               Tuvault                              80 Sales
Janette              King                                 80 Sales
Patrick              Sully                                80 Sales
Allan                McEwen                               80 Sales
Lindsey              Smith                                80 Sales
Louise               Doran                                80 Sales

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Sarath               Sewall                               80 Sales
Clara                Vishney                              80 Sales
Danielle             Greene                               80 Sales
Mattea               Marvins                              80 Sales
David                Lee                                  80 Sales
Sundar               Ande                                 80 Sales
Amit                 Banda                                80 Sales
Lisa                 Ozer                                 80 Sales
Harrison             Bloom                                80 Sales
Tayler               Fox                                  80 Sales
William              Smith                                80 Sales

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Elizabeth            Bates                                80 Sales
Sundita              Kumar                                80 Sales
Ellen                Abel                                 80 Sales
Alyssa               Hutton                               80 Sales
Jonathon             Taylor                               80 Sales
Jack                 Livingston                           80 Sales
Charles              Johnson                              80 Sales
Neena                Kochhar                              90 Executive
Lex                  De Haan                              90 Executive
Nancy                Greenberg                           100 Finance
Daniel               Faviet                              100 Finance

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
John                 Chen                                100 Finance
Ismael               Sciarra                             100 Finance
Jose Manuel          Urman                               100 Finance
Luis                 Popp                                100 Finance
Shelley              Higgins                             110 Accounting
William              Gietz                               110 Accounting
                                                             Treasury
                                                             Corporate Tax
                                                             Control And Credit
                                                             Shareholder Services
                                                             Benefits

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
                                                             Manufacturing
                                                             Construction
                                                             Contracting
                                                             Operations
                                                             IT Support
                                                             NOC
                                                             IT Helpdesk
                                                             Government Sales
                                                             Retail Sales
                                                             Recruiting
                                                             Payroll

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
                                                             Computer Engineering
                                                             IT TUTORIAL

123 rows selected.

SQL>

 

Do you want to learn Oracle SQL Tutorial for Beginners, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course

 371 views last month,  3 views today

About Mehmet Salih Deveci

blank
I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply