SqlDatabaseAdvanced

FULL OUTER JOIN

Returns all rows from both tables, matching where possible (NULLs otherwise).

Review the syntaxStudy the examplesOpen the coding app
SELECT columns FROM a FULL OUTER JOIN b ON a.id = b.a_id;
Open Coding Reference

This static page keeps the syntax and examples indexed for search, while the coding app handles interactive exploration and saved references.

What it does

Overview

Returns all rows from both tables, matching where possible (NULLs otherwise).

A FULL OUTER JOIN (or FULL JOIN) returns all records from both the left and right tables, matching records where available and filling with NULL values when a match is absent on either side. It conceptually functions as a UNION of a LEFT OUTER JOIN and a RIGHT OUTER JOIN. From a performance standpoint, it is significantly more taxing than INNER or LEFT joins because the database engine must identify orphaned rows from both sets. Execution plans often involve Hash Match or Merge Join operators; however, if the join condition is not optimized with indexes, it can degrade into a costly nested loop. It is an essential tool for data reconciliation and full-spectrum reporting where data gaps must be visible. Note that while PostgreSQL, SQL Server, and Oracle support this natively, MySQL requires a UNION-based workaround. When comparing dates within the join or filter, developers should utilize ISO 8601 strings (e.g., 'YYYY-MM-DD') for consistent ordering and comparison across different database locales.

Quick reference

Syntax

SELECT columns FROM a FULL OUTER JOIN b ON a.id = b.a_id;

Inputs

Parameters

aFirst table.
bSecond table.
ON conditionJoin condition for matching rows.

See it in practice

Examples

1

Reconciling Inventory Across Warehouses

SELECT 
  COALESCE(a.Product_ID, b.Product_ID) AS Product_ID, 
  a.Stock AS Warehouse_A_Qty, 
  b.Stock AS Warehouse_B_Qty 
FROM Warehouse_A a 
FULL OUTER JOIN Warehouse_B b 
  ON a.Product_ID = b.Product_ID;
Output:
Product_ID | Warehouse_A_Qty | Warehouse_B_Qty 101 | 50 | 45 102 | 10 | NULL NULL | NULL | 25

Uses COALESCE to show the Product ID regardless of which warehouse contains the stock, highlighting items missing from one side.

2

Comparing Marketing Leads and Sales Accounts

SELECT 
  l.Email AS Lead_Email, 
  s.Email AS Sales_Email 
FROM Leads l 
FULL OUTER JOIN Sales s 
  ON l.Email = s.Email;
Output:
Lead_Email | Sales_Email bob@test.com | bob@test.com alice@test.com | NULL NULL | charlie@test.com

Identifies leads that haven't become accounts and accounts that didn't originate from the leads table.

3

Auditing Monthly Payments against Invoices

SELECT 
  i.Invoice_ID, 
  p.Payment_ID 
FROM Invoices i 
FULL OUTER JOIN Payments p 
  ON i.Invoice_ID = p.Invoice_ID 
WHERE i.Invoice_Date >= '2023-01-01' 
   OR p.Payment_Date >= '2023-01-01';
Output:
Invoice_ID | Payment_ID INV-001 | PAY-99 INV-002 | NULL NULL | PAY-101

Finds unpaid invoices and unlinked payments. Uses ISO 8601 date strings for reliable filtering.

Debug faster

Common Errors

1

LogicError

Cause: Applying a WHERE filter to a nullable column from the join targets without handling NULLs, which implicitly converts the FULL JOIN into an INNER JOIN.

Fix: Move the condition to the JOIN's ON clause or include an IS NULL check in the WHERE clause.

SELECT * FROM TableA a FULL JOIN TableB b ON a.id = b.id WHERE b.status = 'Active'; -- Removes all unmatched rows from TableA
2

LogicError

Cause: Using non-standard date formats (like MM/DD/YYYY) in the join condition, leading to missed matches or errors in different SQL environments.

Fix: Always use ISO 8601 strings ('YYYY-MM-DD') or cast to a timestamp for deterministic comparisons.

SELECT * FROM A FULL JOIN B ON A.LogDate = '12/01/2023'; -- Dangerous depending on server locale

Runtime support

Compatibility

PostgreSQL/SQL Server/Oracle support; MySQL/SQLite may need workaroundsSQL-92 (Standard)

Check DB support

Source: MDN Web Docs

Common questions

Frequently Asked Questions

Returns all rows from both tables, matching where possible (NULLs otherwise).

a: First table. b: Second table. ON condition: Join condition for matching rows.

LogicError: Move the condition to the JOIN's ON clause or include an IS NULL check in the WHERE clause. LogicError: Always use ISO 8601 strings ('YYYY-MM-DD') or cast to a timestamp for deterministic comparisons.