Hi,
I will explain SQL Full Outer Join in Oracle SQL in this post of Oracle SQL Tutorial series.
Read the previous post of this tutorial series before this.
SQL Right Outer Join in Oracle SQL | Oracle SQL Tutorials -22
The Full Outer Join
The Full Outer Join is used to list the records when there is a match in either first or other table.
Full (Outer) JOIN Syntax
Full Outer Join syntax is as follows.
SELECT column1,column2,column(n)...
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;
SELECT column1,column2,column(n)...
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;
You can use full outer join as follows. Full outer join and Full 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 FULL OUTER JOIN hr.departments d
3* ON (e.department_id = d.department_id)
FIRST_NAME LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Neena Kochhar 90 Executive
Lex De Haan 90 Executive
Alexander Hunold 60 IT
Bruce Ernst 60 IT
David Austin 60 IT
Valli Pataballa 60 IT
Diana Lorentz 60 IT
Nancy Greenberg 100 Finance
Daniel Faviet 100 Finance
John Chen 100 Finance
Ismael Sciarra 100 Finance
FIRST_NAME LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Jose Manuel Urman 100 Finance
Luis Popp 100 Finance
Den Raphaely 30 Purchasing
Alexander Khoo 30 Purchasing
Shelli Baida 30 Purchasing
Sigal Tobias 30 Purchasing
Guy Himuro 30 Purchasing
Karen Colmenares 30 Purchasing
Matthew Weiss 50 Shipping
Adam Fripp 50 Shipping
Payam Kaufling 50 Shipping
FIRST_NAME LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
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
TJ Olson 50 Shipping
Jason Mallin 50 Shipping
FIRST_NAME LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
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
Randall Matos 50 Shipping
Peter Vargas 50 Shipping
FIRST_NAME LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
John Russell 80 Sales
Karen Partners 80 Sales
Alberto Errazuriz 80 Sales
Gerald Cambrault 80 Sales
Eleni Zlotkey 80 Sales
Peter Tucker 80 Sales
David Bernstein 80 Sales
Peter Hall 80 Sales
Christopher Olsen 80 Sales
Nanette Cambrault 80 Sales
Oliver Tuvault 80 Sales
FIRST_NAME LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Janette King 80 Sales
Patrick Sully 80 Sales
Allan McEwen 80 Sales
Lindsey Smith 80 Sales
Louise Doran 80 Sales
Sarath Sewall 80 Sales
Clara Vishney 80 Sales
Danielle Greene 80 Sales
Mattea Marvins 80 Sales
David Lee 80 Sales
Sundar Ande 80 Sales
FIRST_NAME LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Amit Banda 80 Sales
Lisa Ozer 80 Sales
Harrison Bloom 80 Sales
Tayler Fox 80 Sales
William Smith 80 Sales
Elizabeth Bates 80 Sales
Sundita Kumar 80 Sales
Ellen Abel 80 Sales
Alyssa Hutton 80 Sales
Jonathon Taylor 80 Sales
Jack Livingston 80 Sales
FIRST_NAME LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Kimberely Grant
Charles Johnson 80 Sales
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
-------------------- ------------------------- ------------- ------------------------------
Jennifer Whalen 10 Administration
Michael Hartstein 20 Marketing
Pat Fay 20 Marketing
Susan Mavris 40 Human Resources
Hermann Baer 70 Public Relations
Shelley Higgins 110 Accounting
William Gietz 110 Accounting
NOC
Manufacturing
Government Sales
IT TUTORIAL
FIRST_NAME LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
IT Support
Benefits
Shareholder Services
Retail Sales
Control And Credit
Recruiting
Operations
Treasury
Payroll
Corporate Tax
Construction
FIRST_NAME LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME
-------------------- ------------------------- ------------- ------------------------------
Computer Engineering
Contracting
IT Helpdesk
124 rows selected.
SQL>
SQL>
Second example for the Full join is as follows.
SQL> select department_name,street_address,city from hr.departments d full join hr.locations l on d.location_id=l.location_id;
DEPARTMENT_NAME STREET_ADDRESS CITY
------------------------------ ---------------------------------------- ------------------------------
Administration 2004 Charade Rd Seattle
Marketing 147 Spadina Ave Toronto
Purchasing 2004 Charade Rd Seattle
Human Resources 8204 Arthur St London
Shipping 2011 Interiors Blvd South San Francisco
IT 2014 Jabberwocky Rd Southlake
Public Relations Schwanthalerstr. 7031 Munich
Sales Magdalen Centre, The Oxford Science Park Oxford
Executive 2004 Charade Rd Seattle
Finance 2004 Charade Rd Seattle
Accounting 2004 Charade Rd Seattle
DEPARTMENT_NAME STREET_ADDRESS CITY
------------------------------ ---------------------------------------- ------------------------------
Treasury 2004 Charade Rd Seattle
Corporate Tax 2004 Charade Rd Seattle
Control And Credit 2004 Charade Rd Seattle
Shareholder Services 2004 Charade Rd Seattle
Benefits 2004 Charade Rd Seattle
Manufacturing 2004 Charade Rd Seattle
Construction 2004 Charade Rd Seattle
Contracting 2004 Charade Rd Seattle
Operations 2004 Charade Rd Seattle
IT Support 2004 Charade Rd Seattle
NOC 2004 Charade Rd Seattle
DEPARTMENT_NAME STREET_ADDRESS CITY
------------------------------ ---------------------------------------- ------------------------------
IT Helpdesk 2004 Charade Rd Seattle
Government Sales 2004 Charade Rd Seattle
Retail Sales 2004 Charade Rd Seattle
Recruiting 2004 Charade Rd Seattle
Payroll 2004 Charade Rd Seattle
Computer Engineering 2004 Charade Rd Seattle
IT TUTORIAL
40-5-12 Laogianggen Beijing
Murtenstrasse 921 Bern
2017 Shinjuku-ku Tokyo
12-98 Victoria Street Sydney
DEPARTMENT_NAME STREET_ADDRESS CITY
------------------------------ ---------------------------------------- ------------------------------
Pieter Breughelstraat 837 Utrecht
2007 Zagora St South Brunswick
1298 Vileparle (E) Bombay
20 Rue des Corps-Saints Geneva
Rua Frei Caneca 1360 Sao Paulo
93091 Calle della Testa Venice
6092 Boxwood St Whitehorse
198 Clementi North Singapore
9450 Kamiya-cho Hiroshima
9702 Chester Road Stretford
1297 Via Cola di Rie Roma
DEPARTMENT_NAME STREET_ADDRESS CITY
------------------------------ ---------------------------------------- ------------------------------
Mariano Escobedo 9991 Mexico City
45 rows selected.
SQL>
Dou 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