{"id":16798,"date":"2020-07-07T13:04:18","date_gmt":"2020-07-07T13:04:18","guid":{"rendered":"https:\/\/ittutorial.org\/?p=16798"},"modified":"2020-07-07T13:04:18","modified_gmt":"2020-07-07T13:04:18","slug":"sql-full-outer-join-in-oracle-sql-oracle-sql-tutorials-23","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-full-outer-join-in-oracle-sql-oracle-sql-tutorials-23\/","title":{"rendered":"SQL Full Outer Join in Oracle SQL | Oracle SQL Tutorials -23"},"content":{"rendered":"<p align=\"left\">Hi,<\/p>\n<p align=\"left\">I will explain SQL Full Outer Join in Oracle SQL in this post of Oracle SQL Tutorial series.<\/p>\n<p align=\"left\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-16079\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/oracle-sql-tutorial.png\" alt=\"\" width=\"387\" height=\"311\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/oracle-sql-tutorial.png 387w, https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/oracle-sql-tutorial-300x241.png 300w\" sizes=\"auto, (max-width: 387px) 100vw, 387px\" \/><\/p>\n<p align=\"left\"><!--more--><\/p>\n<p align=\"left\"><span style=\"color: #ff0000;\"><strong>Read the previous post of this tutorial series before this.<\/strong><\/span><\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"d0B5uz08ZE\"><p><a href=\"https:\/\/ittutorial.org\/sql-right-outer-join-in-oracle-sql-oracle-sql-tutorials-22\/\">SQL Right Outer Join in Oracle SQL | Oracle SQL Tutorials -22<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;SQL Right Outer Join in Oracle SQL | Oracle SQL Tutorials -22&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/sql-right-outer-join-in-oracle-sql-oracle-sql-tutorials-22\/embed\/#?secret=sdkXeTONdp#?secret=d0B5uz08ZE\" data-secret=\"d0B5uz08ZE\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<h4><span style=\"color: #ff0000;\">The Full Outer Join<\/span><\/h4>\n<p>The Full Outer Join is used to list the records when there is a match in either first or other table.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h4><span style=\"color: #ff0000;\">Full (Outer) JOIN Syntax<\/span><\/h4>\n<p>Full Outer Join syntax is as follows.<\/p>\n<p>&nbsp;<\/p>\n<pre><span class=\"sqlcolor\"><span class=\"sqlkeywordcolor\">SELECT\u00a0<em>column1,column2,column(n)...\r\n<\/em> <strong><span style=\"color: #ff0000;\">FROM\u00a0<em>table1<\/em><\/span><\/strong> \r\n<strong><span style=\"color: #ff0000;\">FULL OUTER JOIN\u00a0<\/span><\/strong><em><strong><span style=\"color: #ff0000;\">table2<\/span><\/strong> \r\n<\/em>ON\u00a0<em>table1.column_name\u00a0<\/em>=<em>\u00a0table2.column_name<\/em>;\r\n\r\nSELECT<\/span>\u00a0<em>column1,column2,column(n)...<\/em>\r\n<strong><span style=\"color: #ff0000;\"><span class=\"sqlkeywordcolor\">FROM<\/span>\u00a0<em>table1<\/em><\/span><\/strong>\r\n<strong><span style=\"color: #ff0000;\"><span class=\"sqlkeywordcolor\">FULL<\/span>\u00a0<span class=\"sqlkeywordcolor\">JOIN<\/span>\u00a0<\/span><\/strong><em><strong><span style=\"color: #ff0000;\">table2<\/span><\/strong>\r\n<\/em><span class=\"sqlkeywordcolor\">ON<\/span>\u00a0<em>table1.column_name\u00a0<\/em>=<em>\u00a0table2.column_name<\/em>;<\/span><\/pre>\n<div class=\"w3-example\">\n<pre class=\"w3-code notranslate sqlHigh\"><\/pre>\n<\/div>\n<div><\/div>\n<div><\/div>\n<div><\/div>\n<div><\/div>\n<div><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-16799\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/full-outer-join-in-oracle-sql.jpg\" alt=\"\" width=\"910\" height=\"322\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/full-outer-join-in-oracle-sql.jpg 910w, https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/full-outer-join-in-oracle-sql-300x106.jpg 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/07\/full-outer-join-in-oracle-sql-768x272.jpg 768w\" sizes=\"auto, (max-width: 910px) 100vw, 910px\" \/><\/div>\n<div><\/div>\n<div><\/div>\n<p>You can use full outer join as follows. Full outer join and Full join clause run the same as follows.<\/p>\n<p>&nbsp;<\/p>\n<pre>SQL&gt; SELECT first_name,e.last_name, e.department_id, d.department_name\r\n  2     FROM   hr.employees e <span style=\"color: #ff0000;\"><strong>FULL OUTER JOIN<\/strong><\/span> hr.departments d\r\n  3*    ON   (e.department_id = d.department_id)\r\n\r\nFIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME\r\n-------------------- ------------------------- ------------- ------------------------------\r\nNeena                Kochhar                              90 Executive\r\nLex                  De Haan                              90 Executive\r\nAlexander            Hunold                               60 IT\r\nBruce                Ernst                                60 IT\r\nDavid                Austin                               60 IT\r\nValli                Pataballa                            60 IT\r\nDiana                Lorentz                              60 IT\r\nNancy                Greenberg                           100 Finance\r\nDaniel               Faviet                              100 Finance\r\nJohn                 Chen                                100 Finance\r\nIsmael               Sciarra                             100 Finance\r\n\r\nFIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME\r\n-------------------- ------------------------- ------------- ------------------------------\r\nJose Manuel          Urman                               100 Finance\r\nLuis                 Popp                                100 Finance\r\nDen                  Raphaely                             30 Purchasing\r\nAlexander            Khoo                                 30 Purchasing\r\nShelli               Baida                                30 Purchasing\r\nSigal                Tobias                               30 Purchasing\r\nGuy                  Himuro                               30 Purchasing\r\nKaren                Colmenares                           30 Purchasing\r\nMatthew              Weiss                                50 Shipping\r\nAdam                 Fripp                                50 Shipping\r\nPayam                Kaufling                             50 Shipping\r\n\r\nFIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME\r\n-------------------- ------------------------- ------------- ------------------------------\r\nShanta               Vollman                              50 Shipping\r\nKevin                Mourgos                              50 Shipping\r\nJulia                Nayer                                50 Shipping\r\nIrene                Mikkilineni                          50 Shipping\r\nJames                Landry                               50 Shipping\r\nSteven               Markle                               50 Shipping\r\nLaura                Bissot                               50 Shipping\r\nMozhe                Atkinson                             50 Shipping\r\nJames                Marlow                               50 Shipping\r\nTJ                   Olson                                50 Shipping\r\nJason                Mallin                               50 Shipping\r\n\r\nFIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME\r\n-------------------- ------------------------- ------------- ------------------------------\r\nMichael              Rogers                               50 Shipping\r\nKi                   Gee                                  50 Shipping\r\nHazel                Philtanker                           50 Shipping\r\nRenske               Ladwig                               50 Shipping\r\nStephen              Stiles                               50 Shipping\r\nJohn                 Seo                                  50 Shipping\r\nJoshua               Patel                                50 Shipping\r\nTrenna               Rajs                                 50 Shipping\r\nCurtis               Davies                               50 Shipping\r\nRandall              Matos                                50 Shipping\r\nPeter                Vargas                               50 Shipping\r\n\r\nFIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME\r\n-------------------- ------------------------- ------------- ------------------------------\r\nJohn                 Russell                              80 Sales\r\nKaren                Partners                             80 Sales\r\nAlberto              Errazuriz                            80 Sales\r\nGerald               Cambrault                            80 Sales\r\nEleni                Zlotkey                              80 Sales\r\nPeter                Tucker                               80 Sales\r\nDavid                Bernstein                            80 Sales\r\nPeter                Hall                                 80 Sales\r\nChristopher          Olsen                                80 Sales\r\nNanette              Cambrault                            80 Sales\r\nOliver               Tuvault                              80 Sales\r\n\r\nFIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME\r\n-------------------- ------------------------- ------------- ------------------------------\r\nJanette              King                                 80 Sales\r\nPatrick              Sully                                80 Sales\r\nAllan                McEwen                               80 Sales\r\nLindsey              Smith                                80 Sales\r\nLouise               Doran                                80 Sales\r\nSarath               Sewall                               80 Sales\r\nClara                Vishney                              80 Sales\r\nDanielle             Greene                               80 Sales\r\nMattea               Marvins                              80 Sales\r\nDavid                Lee                                  80 Sales\r\nSundar               Ande                                 80 Sales\r\n\r\nFIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME\r\n-------------------- ------------------------- ------------- ------------------------------\r\nAmit                 Banda                                80 Sales\r\nLisa                 Ozer                                 80 Sales\r\nHarrison             Bloom                                80 Sales\r\nTayler               Fox                                  80 Sales\r\nWilliam              Smith                                80 Sales\r\nElizabeth            Bates                                80 Sales\r\nSundita              Kumar                                80 Sales\r\nEllen                Abel                                 80 Sales\r\nAlyssa               Hutton                               80 Sales\r\nJonathon             Taylor                               80 Sales\r\nJack                 Livingston                           80 Sales\r\n\r\nFIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME\r\n-------------------- ------------------------- ------------- ------------------------------\r\nKimberely            Grant\r\nCharles              Johnson                              80 Sales\r\nWinston              Taylor                               50 Shipping\r\nJean                 Fleaur                               50 Shipping\r\nMartha               Sullivan                             50 Shipping\r\nGirard               Geoni                                50 Shipping\r\nNandita              Sarchand                             50 Shipping\r\nAlexis               Bull                                 50 Shipping\r\nJulia                Dellinger                            50 Shipping\r\nAnthony              Cabrio                               50 Shipping\r\nKelly                Chung                                50 Shipping\r\n\r\nFIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME\r\n-------------------- ------------------------- ------------- ------------------------------\r\nJennifer             Dilly                                50 Shipping\r\nTimothy              Gates                                50 Shipping\r\nRandall              Perkins                              50 Shipping\r\nSarah                Bell                                 50 Shipping\r\nBritney              Everett                              50 Shipping\r\nSamuel               McCain                               50 Shipping\r\nVance                Jones                                50 Shipping\r\nAlana                Walsh                                50 Shipping\r\nKevin                Feeney                               50 Shipping\r\nDonald               OConnell                             50 Shipping\r\nDouglas              Grant                                50 Shipping\r\n\r\nFIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME\r\n-------------------- ------------------------- ------------- ------------------------------\r\nJennifer             Whalen                               10 Administration\r\nMichael              Hartstein                            20 Marketing\r\nPat                  Fay                                  20 Marketing\r\nSusan                Mavris                               40 Human Resources\r\nHermann              Baer                                 70 Public Relations\r\nShelley              Higgins                             110 Accounting\r\nWilliam              Gietz                               110 Accounting\r\n                                                             NOC\r\n                                                             Manufacturing\r\n                                                             Government Sales\r\n                                                             IT TUTORIAL\r\n\r\nFIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME\r\n-------------------- ------------------------- ------------- ------------------------------\r\n                                                             IT Support\r\n                                                             Benefits\r\n                                                             Shareholder Services\r\n                                                             Retail Sales\r\n                                                             Control And Credit\r\n                                                             Recruiting\r\n                                                             Operations\r\n                                                             Treasury\r\n                                                             Payroll\r\n                                                             Corporate Tax\r\n                                                             Construction\r\n\r\nFIRST_NAME           LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME\r\n-------------------- ------------------------- ------------- ------------------------------\r\n                                                             Computer Engineering\r\n                                                             Contracting\r\n                                                             IT Helpdesk\r\n\r\n124 rows selected.\r\n\r\nSQL&gt; \r\nSQL&gt;\r\n\r\n\r\n\r\n<\/pre>\n<p>Second example for the Full join is as follows.<\/p>\n<pre>SQL&gt; select department_name,street_address,city from hr.departments d full join hr.locations l on d.location_id=l.location_id;\r\n\r\nDEPARTMENT_NAME                STREET_ADDRESS                           CITY\r\n------------------------------ ---------------------------------------- ------------------------------\r\nAdministration                 2004 Charade Rd                          Seattle\r\nMarketing                      147 Spadina Ave                          Toronto\r\nPurchasing                     2004 Charade Rd                          Seattle\r\nHuman Resources                8204 Arthur St                           London\r\nShipping                       2011 Interiors Blvd                      South San Francisco\r\nIT                             2014 Jabberwocky Rd                      Southlake\r\nPublic Relations               Schwanthalerstr. 7031                    Munich\r\nSales                          Magdalen Centre, The Oxford Science Park Oxford\r\nExecutive                      2004 Charade Rd                          Seattle\r\nFinance                        2004 Charade Rd                          Seattle\r\nAccounting                     2004 Charade Rd                          Seattle\r\n\r\nDEPARTMENT_NAME                STREET_ADDRESS                           CITY\r\n------------------------------ ---------------------------------------- ------------------------------\r\nTreasury                       2004 Charade Rd                          Seattle\r\nCorporate Tax                  2004 Charade Rd                          Seattle\r\nControl And Credit             2004 Charade Rd                          Seattle\r\nShareholder Services           2004 Charade Rd                          Seattle\r\nBenefits                       2004 Charade Rd                          Seattle\r\nManufacturing                  2004 Charade Rd                          Seattle\r\nConstruction                   2004 Charade Rd                          Seattle\r\nContracting                    2004 Charade Rd                          Seattle\r\nOperations                     2004 Charade Rd                          Seattle\r\nIT Support                     2004 Charade Rd                          Seattle\r\nNOC                            2004 Charade Rd                          Seattle\r\n\r\nDEPARTMENT_NAME                STREET_ADDRESS                           CITY\r\n------------------------------ ---------------------------------------- ------------------------------\r\nIT Helpdesk                    2004 Charade Rd                          Seattle\r\nGovernment Sales               2004 Charade Rd                          Seattle\r\nRetail Sales                   2004 Charade Rd                          Seattle\r\nRecruiting                     2004 Charade Rd                          Seattle\r\nPayroll                        2004 Charade Rd                          Seattle\r\nComputer Engineering           2004 Charade Rd                          Seattle\r\nIT TUTORIAL\r\n                               40-5-12 Laogianggen                      Beijing\r\n                               Murtenstrasse 921                        Bern\r\n                               2017 Shinjuku-ku                         Tokyo\r\n                               12-98 Victoria Street                    Sydney\r\n\r\nDEPARTMENT_NAME                STREET_ADDRESS                           CITY\r\n------------------------------ ---------------------------------------- ------------------------------\r\n                               Pieter Breughelstraat 837                Utrecht\r\n                               2007 Zagora St                           South Brunswick\r\n                               1298 Vileparle (E)                       Bombay\r\n                               20 Rue des Corps-Saints                  Geneva\r\n                               Rua Frei Caneca 1360                     Sao Paulo\r\n                               93091 Calle della Testa                  Venice\r\n                               6092 Boxwood St                          Whitehorse\r\n                               198 Clementi North                       Singapore\r\n                               9450 Kamiya-cho                          Hiroshima\r\n                               9702 Chester Road                        Stretford\r\n                               1297 Via Cola di Rie                     Roma\r\n\r\nDEPARTMENT_NAME                STREET_ADDRESS                           CITY\r\n------------------------------ ---------------------------------------- ------------------------------\r\n                               Mariano Escobedo 9991                    Mexico City\r\n\r\n45 rows selected.\r\n\r\nSQL&gt; \r\n\r\n<\/pre>\n<p>&nbsp;<\/p>\n<h4><span style=\"color: #ff0000;\">Dou want to learn Oracle SQL Tutorial for Beginners, then read the following articles.<\/span><\/h4>\n<blockquote class=\"wp-embedded-content\" data-secret=\"X19uYGRVUJ\"><p><a href=\"https:\/\/ittutorial.org\/oracle-sql-tutorials-for-beginners-learn-sql-tutorials-sql-course\/\">Oracle SQL Tutorials For Beginners &#8211; Learn Oracle SQL from scratch with Oracle SQL Online Course<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Oracle SQL Tutorials For Beginners &#8211; Learn Oracle SQL from scratch with Oracle SQL Online Course&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/oracle-sql-tutorials-for-beginners-learn-sql-tutorials-sql-course\/embed\/#?secret=NOEDweZrYB#?secret=X19uYGRVUJ\" data-secret=\"X19uYGRVUJ\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, I will explain SQL Full Outer Join in Oracle SQL in this post of Oracle SQL Tutorial series.<\/p>\n","protected":false},"author":1,"featured_media":16079,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3019],"tags":[11976,11986,11991,12005,11982,11981,11977,11987,11985,9037,12004,12003,11975,11990,11972,11979,11974,11351,11304,11973,3001,11302,2673,11301,11983,3715,11978,12006,6843,11984,11980,11988],"class_list":["post-16798","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle-sql","tag-cross-join-oracle","tag-cross-join-sql","tag-full-outer-join-oracle","tag-full-outer-join-vs-union","tag-inner-join-sql","tag-left-join-sql","tag-left-outer-join-in-oracle-with-multiple-conditions","tag-left-outer-join-sql","tag-natural-join-sql","tag-oracle-dba-tutorial","tag-oracle-full-outer-join-multiple-tables","tag-oracle-full-outer-join-syntax-using","tag-oracle-inner-join-multiple-tables","tag-oracle-inner-join-vs-join","tag-oracle-join-multiple-tables","tag-oracle-join-using","tag-oracle-left-outer-join-multiple-tables","tag-oracle-live-sql","tag-oracle-sql-developer-tutorial","tag-oracle-sql-join-3-tables","tag-oracle-sql-tutorial","tag-oracle-sql-tutorial-pdf","tag-oracle-tutorial","tag-oracle-tutorial-pdf","tag-outer-join-sql","tag-pl-sql-tutorial","tag-right-outer-join-oracle","tag-self-join-in-oracle","tag-sql-join","tag-sql-join-3-tables","tag-sql-join-and-join-types-in-oracle-sql","tag-sql-join-multiple-tables-with-conditions"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2020\/05\/oracle-sql-tutorial.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/16798","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=16798"}],"version-history":[{"count":1,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/16798\/revisions"}],"predecessor-version":[{"id":16806,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/16798\/revisions\/16806"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/16079"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=16798"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=16798"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=16798"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}