IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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;
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
