SQL Full Outer Join in Oracle SQL | Oracle SQL Tutorials -23

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.

 

 

 

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.

 205 views last month,  2 views today

Mehmet Salih Deveci

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