Back to articles list April 13, 2021 - 7 minutes read What FULL JOIN Is and When to Use It Zahin Rahman Nuclear Engineer by day and Data Scientist by night, Zahin is passionate about driving business with data. He has 5+ years of industry experience in energy and power generation and has a master’s degree in Engineering with a focus in Data Science. He loves learning, whether from an industry veteran or a high school science enthusiast. Tags: sql learn sql sql joins Learn about FULL JOIN, how to implement it, how it compares with the other types of SQL JOINs, and some of its unique use cases. Before we jump into FULL JOINs, let’s quickly recap what an SQL JOIN is. At its core, a JOIN combines data from two or more tables within a database. Tables are usually linked together using unique identifiers in each table, i.e. primary and foreign keys. To demonstrate a simple SQL JOIN – also known as an INNER JOIN – in action, let’s consider the Products and OrderDetails tables from the well-known Northwind sample database. The Products table contains a list of all products and the OrderDetails table contains a list of all recent orders. PRODUCTS ProductIDProductNameSupplierIDCategoryIDUnitPrice 1Chais1110 boxes x 20 bags18 2Chang1124 - 12 oz bottles19 3Aniseed Syrup1212 - 550 ml bottles10 .................. ORDERDETAILS OrderDetaiIDOrderIDProductIDQuantity 1102481112 2102484210 310248725 ............ Here’s the query: SELECT Products.ProductName, OrderDetails.OrderID, OrderDetails.Quantity FROM Products JOIN OrderDetails ON Products.ProductID=OrderDetails.ProductID ORDER BY Quantity Desc; And the result: ProductNameOrderIDQuantity Pâté chinois10398120 Steeleye Stout10286100 Sirop d'érable1044090 The above example uses ProductID (which is available in both tables) as the key to join the two tables and display each order sorted in descending order by Quantity. Note that a simple JOIN like the one above only returns matching rows from the two tables. In this case, the results would only include recently-ordered products and orders that are linked to a valid ProductID. FULL JOIN: An Introduction Unlike INNER JOIN, a FULL JOIN returns all the rows from both joined tables, whether they have a matching row or not. Hence, a FULL JOIN is also referred to as a FULL OUTER JOIN. A FULL JOIN returns unmatched rows from both tables as well as the overlap between them. When no matching rows exist for a row in the left table, the columns of the right table will have NULLs for those records. Similarly, when no matching rows exist for a row in the right table, the columns of the left table will have NULLs. To demonstrate the difference between a simple SQL JOIN and a FULL OUTER JOIN, let’s consider the Projects and Employees tables as shown below. The Projects table contains a list of all projects undertaken by the company (in-house projects as well as outsourced), while the Employees table contains a list of all current employees and specifies if they are involved in any company projects. PROJECTS ProjectIDProjectNameCostYTDBusinessUnit 1CRM Upgrade45640Customer Relations 2Cybersecurity Protocol Implementation80200Cybersecurity 3HQ Office Renovations145000Facilities 4ERP Integration110000Corporate 5Database Stack Upgrade25000Engineering ............ 15Automated QA10000Engineering EMPLOYEES EmployeeIDEmployeeNameProjectIDStartDateBusinessUnit 001Albert Ross32012-02-11Facilities 002Hummer BairdNULL2012-02-11Corporate 003Matthias Dias152012-07-15Engineering 004Al Cooper12014-04-15Customer Relations 005Macron RalfNULL2014-04-15Legal ............... 025Hamza ImranNULL2020-07-11Engineering If we were to do JOINs between these two tables by matching the tables through ProjectID as the primary key, the results of a simple INNER JOIN and a FULL OUTER JOIN would be quite different. An INNER JOIN would output a table containing only the results where there is a match between the two corresponding entries in both tables. The INNER JOIN result contains employee names and the corresponding project names: EmployeeNameProjectName Albert RossHQ Office Renovations Matthias DiasAutomated QA Al CooperCRM Upgrade On the other hand, a FULL OUTER JOIN will output both tables’ data, irrespective whether it has a match in the other table: EmployeeNameProjectName Albert RossHQ Office Renovations Hummer BairdNULL Matthias DiasAutomated QA Al CooperCRM Upgrade ....... Hamza ImranNULL NULLERP Integration The result of FULL JOIN contains all employee names, regardless of whether they are assigned to a project, and all project names, even if there are no employees assigned to that project. FULL JOIN Syntax The basic syntax of a FULL JOIN is similar to other types of JOINs: SELECT left_table.column1, right_table.column2,... FROM left_table FULL OUTER JOIN right_table ON left_table.key = right_table.key; The OUTER keyword is optional and may be omitted. Example Now, let’s run through that example of a FULL OUTER JOIN that shows both the EmployeeName and ProjectName again. Here's the query: SELECT Employees.EmployeeName, Projects.ProjectName FROM Employees FULL OUTER JOIN Projects ON Employees.ProjectID=Projects.ProjectID ORDER BY EmployeeID; And the result: EmployeeNameProjectName Albert RossHQ Office Renovations Hummer BairdNULL Matthias DiasAutomated QA Al CooperCRM Upgrade ....... Hamza ImranNULL NULLERP Integration From the result table, we see that a FULL JOIN also outputs NULL results from both the Projects and Employees tables – even though there were no matches on the other table. NULLs in the ProjectName column indicate that employee is not specifically assigned to any project and likely serves other functions in the company. On the other hand, a NULL in the EmployeeName column suggests that the specific project is likely outsourced and not directly managed by any company employee. Alternatively, a NULL value could also point to a potential error or inadvertent data deletion in the database or the system. (We will discuss this more below.) It’s good to note that some databases, like MySQL, do not support full joins. In that case, you can use the UNION ALL operator to combine the LEFT JOIN and RIGHT JOIN. Our prior article on SQL JOINs has a few more examples that you can examine. Our SQL JOINs course also provides comprehensive examples of FULL JOINs and includes practical exercises to polish your knowledge. FULL JOIN Use Cases Compared to the other types of SQL JOINs, you will likely use FULL JOIN less frequently. That being said, it is a very handy tool for a few unique situations. Some of its use cases are: To retrieve all records from both tables, whether there is a match or not. This is the example we saw above. To find mismatched or orphaned data from both tables. An orphanedrecord is a record whose foreign key value references a non-existent primary key value; this often happens in rapidly scaling or very old systems. To run exception reports. This is a form of data analysis comparing one set of data against a desired/expected base data set and highlighting items that don't match. Although the application of FULL JOINs is quite unique, they are a great way to find and diagnose potential data integrity issues. Types of JOINs: Recap As we had mentioned above, there are a few more types of JOINs. An INNER JOIN returns rows when the JOIN condition is satisfied in both the left and right tables. In other words, it returns only the matched records from the tables. This is the most common type of SQL JOIN and is the default when you haven’t specified the type of JOIN. An OUTER JOIN returns all the rows from one table and some or all of the rows from another table (depending on the type of OUTER JOIN). Aside from the FULL OUTER JOIN, there are two other types: A LEFT OUTER JOIN returns all rows from the left table, even if no matching rows were found in the right table. If there are no matching records in the right table, the query will return NULL values for those columns. A RIGHT OUTER JOIN returns all rows from the right table. If there are no matching records in the left table, NULL values are returned for those columns – the reverse of a LEFT JOIN. A CROSS JOIN (also called a Cartesian JOIN) returns every possible combination of rows from the tables that have been joined. Since it returns all possible combinations, this is the only JOIN type that does not need a JOIN condition and therefore does not require an ON clause. For a more detailed look at each of the JOIN types, check out the article SQL JOIN Types Explained. Additionally, our SQL JOINs course covers all the different types and use cases of JOINs in great detail. It also goes into more advanced topics, such as non-equi JOINs and multiple JOINs. JOINs are one of the most fundamental and commonly used features of SQL and an essential part of any SQL user’s toolkit. They’re featured in common interview questions as well; see our article The Top 10 SQL JOIN Interview Questions and How to Answer Them for helpful tips. Increase Your Knowledge of SQL FULL JOINs In this tutorial, you’ve learned the specifics of SQL FULL JOINs and their core use cases in diagnosing data integrity. You’ve also seen a brief comparison between FULL JOINs and the other types of JOINs. To really get a grasp of FULL JOINs, you need to dive into the deep end, practice some exercises, and get challenged. I highly recommend trying our SQL JOINs course as you move to the next level of this very important SQL feature. Tags: sql learn sql sql joins You may also like SQL JOIN Tips for Beginners Four tips for learning and using SQL JOINs. Our examples show you why these should become second nature when joining tables. Read more SQL JOIN Cheat Sheet This 2-page SQL JOIN Cheat Sheet covers the syntax of different JOINs (even the rare ones!) Download it in PDF or PNG format. Read more SQL JOIN Types Explained Learn how to join tables in SQL. Review the different SQL join types and when to use inner join, left join, right join, or full join. Read more How to Learn SQL JOINs Learn how to use SQL JOINs to effectively combine data across multiple tables and analyze sophisticated data sets. Read more How to Join the Same Table Twice Learn when and why you need to join the same table twice in SQL, including when you should use self joins. Read more How to Join 3 Tables (or More) in SQL Have you ever wondered how to join three tables in SQL? It's easy when you know the basics. Joining three tables can be as easy as joining two tables. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.