Skip to main content

Full Outer Joins

Premium

A FULL OUTER JOIN is a type of join in SQL that combines data from two tables and returns all the matching rows as well as all the non-matching rows from both tables. The result set includes all the rows from both tables, and the non-matching rows are filled in with NULL values.

Basic syntax

Here's the basic syntax for a FULL OUTER JOIN.

SQL
SELECT table1.column1, table2.column2
FROM table1
FULL OUTER JOIN table2
ON table1.column1 = table2.column2;

A FULL OUTER JOIN returns all the rows from both tables, including the rows that don't have matching values in the other table. If a row from one table doesn't have a match in the other table, the result set will contain NULL values for the columns from the non-matching table.

Example

Let's say you have a customers table and an orders table. The customers table contains information about each customer, and the orders table contains information about each order.

SQL
SELECT customers.name, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

This FULL OUTER JOIN returns a result set that includes all the customers and all the orders, including the customers who don't have any orders and the orders that don't have a customer. If a customer doesn't have any orders, the result set will still include the customer with NULL values for the order_id and order_date columns. Likewise, if an order doesn't have a customer, the result set will still include the order with NULL values for the name column.

When should you use a full outer join?

A FULL OUTER JOIN is useful when you want to combine data from two tables and return all the matching rows as well as all the non-matching rows from both tables. Here are a few practical examples of when to use a FULL OUTER JOIN:

  • Combining data from two tables to produce a complete list of all the data: For example, you have a customers table and an orders table. A FULL OUTER JOIN between these two tables would return a complete list of all the customers and all the orders, including the customers who don't have any orders and the orders that don't have a customer.

  • Synchronizing data between two tables: For example, you have a users table and a teams table. A FULL OUTER JOIN between these two tables would return a list of all the users and all the teams, including the users who aren't part of any teams and the teams that don't have any members.

  • Finding missing data: For example, you have a products table and a sales table. A FULL OUTER JOIN between these two tables would return a list of all the products and all the sales, including the products that haven't been sold and the sales that don't have a product.

In general, a FULL OUTER JOIN is useful when you want to combine data from two tables and return a complete set of data, including the non-matching rows. This type of join is especially useful when you want to find missing data or synchronize data between two tables.

Next Lesson