Skip to main content
Learning DUB

How to Use Full Outer Joins in SQL

A FULL JOIN (also called a FULL OUTER JOIN) combines the results of both left and right outer joins and returns all the records from each table even if there are no matches in the joined table. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

The syntax for a FULL JOIN is as follows:
SELECT table1.column, table2.column
FROM table1
FULL [OUTER] JOIN table2 ON (table1.column=table2.column)
WHERE conditions

Use the SQL-92 FULL OUTER JOIN syntax

The following example returns the product name and any corresponding sales orders in the SalesOrderDetail table in the AdventureWorks2022 database. It also returns any sales orders that have no product listed in the Product table, and any products with a sales order other than the one listed in the Product table.

-- The OUTER keyword following the FULL keyword is optional.
SELECT p.Name,
sod.SalesOrderID
FROM SalesLT.Product AS p
FULL JOIN SalesLT.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY p.Name;
GO

//////////////////////// ///////////////////New Copy Code //////////////////////////
SQL
SQL
SQL

You can also use a FULL outer join to preserve unmatched rows from both sides of the join (all customers, including those who haven't placed an order; and all orders, including those with no matching customer), though in practice this is used less frequently.

///////////////////New Copy Code //////////////////////////
SQL
SQL
SQL
/////////////////////////////////////// ///////////////////// PUB Database Examples ////////////////////////////
How to Use Full Outer Joins in SQL
To retain the nonmatching information by including nonmatching rows in the results of a join, use a full outer join. Microsoft® SQL Server™ provides the full outer join operator, FULL OUTER JOIN, which includes all rows from both tables, regardless of whether or not the other table has a matching value. Consider a join of the authors table and the publishers table on their city columns. The results show only the authors who live in cities in which a publisher is located (in this case, Abraham Bennet and Cheryl Carson). The SQL-92 FULL OUTER JOIN operator indicates that all rows from both tables are to be included in the results, regardless of whether there is matching data in the tables. To include all publishers and all authors in the results, regardless of whether a city has a publisher located in the same city, or whether a publisher is located in the same city, use a full outer join. Here is the query and results of the Transact-SQL full outer join: USE pubs SELECT a.au_fname, a.au_lname, p.pub_name FROM authors a FULL OUTER JOIN publishers p ON a.city = p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
**

Here is the result set:

///////////////////////////

Related Content

Scroll to Top