Which type of join returns all rows in which there is a match in either one of the tables?
SQL FULL OUTER JOIN KeywordThe FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records. Show
Tip: FULL OUTER JOIN and FULL JOIN are the same. FULL OUTER JOIN Syntax SELECT column_name(s)  Note: FULL OUTER JOIN can potentially return very large result-sets! Demo DatabaseIn this tutorial we will use the well-known Northwind sample database. Below is a selection from the "Customers" table:
And a selection from the "Orders" table:
SQL FULL OUTER JOIN ExampleThe following SQL statement selects all customers, and all orders: SELECT Customers.CustomerName, Orders.OrderID A selection from the result set may look like this:
Note: The FULL OUTER JOIN keyword returns all matching records from both tables whether the other table matches or not. So, if there are rows in "Customers" that do not have matches in "Orders", or if there are rows in "Orders" that do not have matches in "Customers", those rows will be listed as well. 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 OrderDetailstables from the well-known Northwind sample database. The Productstable contains a list of all products and the OrderDetailstable contains a list of all recent orders.
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:
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 IntroductionUnlike 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 Employeestables as shown below. The Projectstable 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.
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:
On the other hand, a FULL OUTER JOIN will output both tables’ data, irrespective whether it has a match in the other table:
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 SyntaxThe 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. ExampleNow, 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:
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 CasesCompared 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:
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: RecapAs 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 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 JOINsIn 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. Which type of join returns all rows in which there is a match in both tables?Here are the different types of the JOINs in SQL: (INNER) JOIN : Returns records that have matching values in both tables.
Which type of join is used to returns all from a table even when there are no matching rows?LEFT JOIN is used; this will return ALL rows from Table1 , regardless of whether or not there is a matching row in Table2 .
What type of join returns all rows from the first table and any matching rows from the second table?A cross join, also known as a Cartesian Product join, returns a result table where each row from the first table is combined with each row from the second table.
Which join returns all the rows from the right table with the matching rows in the left table?SQL cross join
It generates all the rows from the left table which is then combined with all the rows from the right table. This type of join is also known as a Cartesian product(A*B).
|
