FULL OUTER JOIN

A FULL OUTER JOIN combines two tables and keeps every row from both sides, matched where possible and padded with blanks where no match exists.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Auditing Catalogue and Inventory with FULL OUTER JOIN

SELECT p.product_id AS catalogue_product_id,
       p.brand,
       p.category,
       i.product_id AS inventory_product_id,
       i.stock_count,
       i.region
FROM products p
FULL OUTER JOIN inventory i
  ON p.product_id = i.product_id

Result:

catalogue_product_id	brand	category	inventory_product_id	stock_count	region
P001	NovaTech	Electronics	P001	120	West
P002	UrbanWear	Clothing
P003	GlowUp	Beauty	P003	45	East
P004	FitGear	Fashion
			P005	200	Central
			P006	30	West

Step by step

AS gives a column a friendlier name in the result.

SELECT p.product_id AS catalogue_product_id,
       p.brand,
       p.category,
       i.product_id AS inventory_product_id,
       i.stock_count,
       i.region

FROM tells SQL which table to pull the data from.

SELECT p.product_id AS catalogue_product_id,
       p.brand,
       p.category,
       i.product_id AS inventory_product_id,
       i.stock_count,
       i.region
FROM products p

FULL JOIN returns all rows from both tables, matched where possible.

SELECT p.product_id AS catalogue_product_id,
       p.brand,
       p.category,
       i.product_id AS inventory_product_id,
       i.stock_count,
       i.region
FROM products p
FULL OUTER JOIN inventory i
  ON p.product_id = i.product_id;

Practice

Try each one in your head first, then open the answer.

1. Write a query that returns every row from both orders and customers, joined on customer_id, including unmatched rows from either side.

SELECT * FROM orders FULL OUTER JOIN customers ON orders.customer_id = customers.customer_id

2. Full outer join orders and returns on order_id, returning all rows from both tables including those with no match.

SELECT * FROM orders FULL OUTER JOIN returns ON orders.order_id = returns.order_id

3. Look at the products and reviews tables. Find their shared column, then write a FULL OUTER JOIN so all products and all reviews appear - even those with no match on either side.

SELECT * FROM products FULL OUTER JOIN reviews ON products.product_id = reviews.product_id

4. Examine the orders and shipments tables. Identify the column they share, then write a FULL OUTER JOIN returning every order and every shipment regardless of a match.

SELECT * FROM orders FULL OUTER JOIN shipments ON orders.order_id = shipments.order_id

5. Look at the suppliers and supplier_products tables. Find the column they have in common, then write a FULL OUTER JOIN so every row from both tables appears.

SELECT * FROM suppliers FULL OUTER JOIN supplier_products ON suppliers.supplier_id = supplier_products.supplier_id

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed