{"id":22549,"date":"2023-06-15T17:49:39","date_gmt":"2023-06-15T17:49:39","guid":{"rendered":"https:\/\/machinelearningplus.com\/?p=22549"},"modified":"2023-06-18T14:54:11","modified_gmt":"2023-06-18T14:54:11","slug":"sql-full-join","status":"publish","type":"post","link":"https:\/\/machinelearningplus.com\/sql\/sql-full-join\/","title":{"rendered":"SQL Full Join &#8211; A Comprehensive Guide on SQL Full Join"},"content":{"rendered":"<p><strong>Full Join in SQL is a method to combine rows from two or more tables based on a related column<\/strong><\/p>\n<p>In Structured Query Language, we often find ourselves needing to combine data from multiple tables, and there are several techniques to help us accomplish this. One of these methods, which provides a comprehensive view of our data, is the SQL Full Join.<\/p>\n<h2>SQL Full Join Syntax<\/h2>\n<p>A Full Join in SQL, also known as Full Outer Join, returns all records when there is a match in either the left table or the right table. If there is no match, the result is <code>NULL<\/code> on either side.<\/p>\n<p>Here is how the syntax looks:<\/p>\n<pre><code class=\"language-python\">SELECT table1.column1, table2.column2...\nFROM table1\nFULL JOIN table2\nON table1.matching_column = table2.matching_column;\n<\/code><\/pre>\n<p>Executing this command will result in a new table that includes rows from both the left and right tables, regardless of whether there&#8217;s a match.<\/p>\n<h2>Unpacking the Syntax with Examples<\/h2>\n<p>To illustrate this concept, let&#8217;s use our familiar <code>Orders<\/code> and <code>Customers<\/code> tables.<\/p>\n<p>The <code>Orders<\/code> table is as follows:<\/p>\n<pre><code class=\"language-python\">| OrderID | CustomerID | Product    |\n|---------|------------|------------|\n| 1       | 101        | Apples     |\n| 2       | 102        | Bananas    |\n| 3       | 103        | Cherries   |\n| 4       | 104        | Dates      |\n| 5       | 105        | Eucalyptus |\n<\/code><\/pre>\n<p>And the <code>Customers<\/code> table looks like this:<\/p>\n<pre><code class=\"language-python\">| CustomerID | Name     | Country  |\n|------------|----------|----------|\n| 101        | Alice    | USA      |\n| 102        | Bob      | UK       |\n| 103        | Charlie  | Canada   |\n| 104        | David    | Australia|\n| 106        | James    | UK       |\n<\/code><\/pre>\n<p>Our goal is to create a table that includes all orders from the <code>Orders<\/code> table and all customers from the <code>Customers<\/code> table, matching them where possible.<\/p>\n<p>Here&#8217;s how I&#8217;d use the Full Join syntax to achieve this:<\/p>\n<pre><code class=\"language-python\">SELECT Orders.OrderID, Customers.Name, Orders.Product\nFROM Customers\nFULL JOIN Orders\nON Customers.CustomerID = Orders.CustomerID;\n<\/code><\/pre>\n<p>Executing this command gives us a new table:<\/p>\n<pre><code class=\"language-python\">| OrderID | Name     | Product   |\n|---------|----------|-----------|\n| 1       | Alice    | Apples    |\n| 2       | Bob      | Bananas   |\n| 3       | Charlie  | Cherries  |\n| 4       | David    | Dates     |\n| 5       | NULL     | Eucalyptus|\n| NULL    | James    | NULL      |\n<\/code><\/pre>\n<p>In this result, all orders and all customers are included. If an order does not have a matching customer, or a customer does not have a matching order, the respective fields will be <code>NULL<\/code>.<\/p>\n<h2>Wrapping Up<\/h2>\n<p>Mastering the Full Join operation in SQL is a crucial step in becoming proficient with handling and analyzing relational data. It allows you to compile and correlate data from multiple tables based on shared attributes, ensuring that no data from either table is overlooked.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Full Join in SQL is a method to combine rows from two or more tables based on a related column In Structured Query Language, we often find ourselves needing to combine data from multiple tables, and there are several techniques to help us accomplish this. One of these methods, which provides a comprehensive view of [&hellip;]<\/p>\n","protected":false},"author":38,"featured_media":22642,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"site-sidebar-layout":"default","site-content-layout":"default","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[2091],"tags":[2094,2092],"class_list":["post-22549","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-data-engineering","tag-sql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL Full Join - A Comprehensive Guide on SQL Full Join - machinelearningplus<\/title>\n<meta name=\"description\" content=\"A Comprehensive Guide on SQL Full Join\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/localhost:8080\/sql\/sql-full-join\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Full Join - A Comprehensive Guide on SQL Full Join - machinelearningplus\" \/>\n<meta property=\"og:description\" content=\"A Comprehensive Guide on SQL Full Join\" \/>\n<meta property=\"og:url\" content=\"https:\/\/localhost:8080\/sql\/sql-full-join\/\" \/>\n<meta property=\"og:site_name\" content=\"machinelearningplus\" \/>\n<meta property=\"article:published_time\" content=\"2023-06-15T17:49:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-06-18T14:54:11+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/localhost:8080\/wp-content\/uploads\/2023\/06\/SQL-Full-Join.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1080\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Jagdeesh\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jagdeesh\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"TechArticle\",\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-full-join\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-full-join\\\/\"},\"author\":{\"name\":\"Jagdeesh\",\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#\\\/schema\\\/person\\\/b1493170590ee4eb9622bcaea5b3b7da\"},\"headline\":\"SQL Full Join &#8211; A Comprehensive Guide on SQL Full Join\",\"datePublished\":\"2023-06-15T17:49:39+00:00\",\"dateModified\":\"2023-06-18T14:54:11+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-full-join\\\/\"},\"wordCount\":299,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-full-join\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/uploads\\\/2023\\\/06\\\/SQL-Full-Join.png\",\"keywords\":[\"Data Engineering\",\"SQL\"],\"articleSection\":[\"SQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-full-join\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-full-join\\\/\",\"url\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-full-join\\\/\",\"name\":\"SQL Full Join - A Comprehensive Guide on SQL Full Join - machinelearningplus\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-full-join\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-full-join\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/uploads\\\/2023\\\/06\\\/SQL-Full-Join.png\",\"datePublished\":\"2023-06-15T17:49:39+00:00\",\"dateModified\":\"2023-06-18T14:54:11+00:00\",\"description\":\"A Comprehensive Guide on SQL Full Join\",\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-full-join\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/localhost:8080\\\/sql\\\/sql-full-join\\\/#primaryimage\",\"url\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/uploads\\\/2023\\\/06\\\/SQL-Full-Join.png\",\"contentUrl\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/uploads\\\/2023\\\/06\\\/SQL-Full-Join.png\",\"width\":1080,\"height\":1080,\"caption\":\"SQL Full Join\"},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#website\",\"url\":\"https:\\\/\\\/machinelearningplus.com\\\/\",\"name\":\"machinelearningplus\",\"description\":\"Learn Data Science (AI \\\/ ML) Online\",\"publisher\":{\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/machinelearningplus.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#organization\",\"name\":\"machinelearningplus\",\"url\":\"https:\\\/\\\/machinelearningplus.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/MachineLearningplus-logo.svg\",\"contentUrl\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/MachineLearningplus-logo.svg\",\"width\":348,\"height\":36,\"caption\":\"machinelearningplus\"},\"image\":{\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/#\\\/schema\\\/person\\\/b1493170590ee4eb9622bcaea5b3b7da\",\"name\":\"Jagdeesh\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/litespeed\\\/avatar\\\/8a303ceb6bbccb62c3dbc14787297f84.jpg?ver=1776363391\",\"url\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/litespeed\\\/avatar\\\/8a303ceb6bbccb62c3dbc14787297f84.jpg?ver=1776363391\",\"contentUrl\":\"https:\\\/\\\/machinelearningplus.com\\\/wp-content\\\/litespeed\\\/avatar\\\/8a303ceb6bbccb62c3dbc14787297f84.jpg?ver=1776363391\",\"caption\":\"Jagdeesh\"},\"url\":\"https:\\\/\\\/machinelearningplus.com\\\/author\\\/jagdeesh\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Full Join - A Comprehensive Guide on SQL Full Join - machinelearningplus","description":"A Comprehensive Guide on SQL Full Join","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/localhost:8080\/sql\/sql-full-join\/","og_locale":"en_US","og_type":"article","og_title":"SQL Full Join - A Comprehensive Guide on SQL Full Join - machinelearningplus","og_description":"A Comprehensive Guide on SQL Full Join","og_url":"https:\/\/localhost:8080\/sql\/sql-full-join\/","og_site_name":"machinelearningplus","article_published_time":"2023-06-15T17:49:39+00:00","article_modified_time":"2023-06-18T14:54:11+00:00","og_image":[{"width":1080,"height":1080,"url":"https:\/\/localhost:8080\/wp-content\/uploads\/2023\/06\/SQL-Full-Join.png","type":"image\/png"}],"author":"Jagdeesh","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Jagdeesh","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"TechArticle","@id":"https:\/\/localhost:8080\/sql\/sql-full-join\/#article","isPartOf":{"@id":"https:\/\/localhost:8080\/sql\/sql-full-join\/"},"author":{"name":"Jagdeesh","@id":"https:\/\/machinelearningplus.com\/#\/schema\/person\/b1493170590ee4eb9622bcaea5b3b7da"},"headline":"SQL Full Join &#8211; A Comprehensive Guide on SQL Full Join","datePublished":"2023-06-15T17:49:39+00:00","dateModified":"2023-06-18T14:54:11+00:00","mainEntityOfPage":{"@id":"https:\/\/localhost:8080\/sql\/sql-full-join\/"},"wordCount":299,"commentCount":0,"publisher":{"@id":"https:\/\/machinelearningplus.com\/#organization"},"image":{"@id":"https:\/\/localhost:8080\/sql\/sql-full-join\/#primaryimage"},"thumbnailUrl":"https:\/\/machinelearningplus.com\/wp-content\/uploads\/2023\/06\/SQL-Full-Join.png","keywords":["Data Engineering","SQL"],"articleSection":["SQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/localhost:8080\/sql\/sql-full-join\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/localhost:8080\/sql\/sql-full-join\/","url":"https:\/\/localhost:8080\/sql\/sql-full-join\/","name":"SQL Full Join - A Comprehensive Guide on SQL Full Join - machinelearningplus","isPartOf":{"@id":"https:\/\/machinelearningplus.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/localhost:8080\/sql\/sql-full-join\/#primaryimage"},"image":{"@id":"https:\/\/localhost:8080\/sql\/sql-full-join\/#primaryimage"},"thumbnailUrl":"https:\/\/machinelearningplus.com\/wp-content\/uploads\/2023\/06\/SQL-Full-Join.png","datePublished":"2023-06-15T17:49:39+00:00","dateModified":"2023-06-18T14:54:11+00:00","description":"A Comprehensive Guide on SQL Full Join","inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/localhost:8080\/sql\/sql-full-join\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/localhost:8080\/sql\/sql-full-join\/#primaryimage","url":"https:\/\/machinelearningplus.com\/wp-content\/uploads\/2023\/06\/SQL-Full-Join.png","contentUrl":"https:\/\/machinelearningplus.com\/wp-content\/uploads\/2023\/06\/SQL-Full-Join.png","width":1080,"height":1080,"caption":"SQL Full Join"},{"@type":"WebSite","@id":"https:\/\/machinelearningplus.com\/#website","url":"https:\/\/machinelearningplus.com\/","name":"machinelearningplus","description":"Learn Data Science (AI \/ ML) Online","publisher":{"@id":"https:\/\/machinelearningplus.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/machinelearningplus.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/machinelearningplus.com\/#organization","name":"machinelearningplus","url":"https:\/\/machinelearningplus.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/machinelearningplus.com\/#\/schema\/logo\/image\/","url":"https:\/\/machinelearningplus.com\/wp-content\/uploads\/2022\/05\/MachineLearningplus-logo.svg","contentUrl":"https:\/\/machinelearningplus.com\/wp-content\/uploads\/2022\/05\/MachineLearningplus-logo.svg","width":348,"height":36,"caption":"machinelearningplus"},"image":{"@id":"https:\/\/machinelearningplus.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/machinelearningplus.com\/#\/schema\/person\/b1493170590ee4eb9622bcaea5b3b7da","name":"Jagdeesh","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/machinelearningplus.com\/wp-content\/litespeed\/avatar\/8a303ceb6bbccb62c3dbc14787297f84.jpg?ver=1776363391","url":"https:\/\/machinelearningplus.com\/wp-content\/litespeed\/avatar\/8a303ceb6bbccb62c3dbc14787297f84.jpg?ver=1776363391","contentUrl":"https:\/\/machinelearningplus.com\/wp-content\/litespeed\/avatar\/8a303ceb6bbccb62c3dbc14787297f84.jpg?ver=1776363391","caption":"Jagdeesh"},"url":"https:\/\/machinelearningplus.com\/author\/jagdeesh\/"}]}},"_links":{"self":[{"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/posts\/22549","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/users\/38"}],"replies":[{"embeddable":true,"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/comments?post=22549"}],"version-history":[{"count":0,"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/posts\/22549\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/media\/22642"}],"wp:attachment":[{"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/media?parent=22549"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/categories?post=22549"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/machinelearningplus.com\/wp-json\/wp\/v2\/tags?post=22549"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}