SQL OUTER JOIN

What is an SQL OUTER JOIN?

OUTER JOIN returns matching rows plus the non-matching rows from one or both joined tables, filling gaps with NULLs.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL OUTER JOIN: PostgreSQL, MySQL 8+, MariaDB, SQL Server, Oracle, Snowflake, Redshift, BigQuery, DB2. SQLite supports LEFT/RIGHT; FULL requires work-around.

SQL OUTER JOIN Full Explanation

OUTER JOIN is a family of JOIN operations (LEFT, RIGHT, and FULL) that augment an INNER JOIN by also including rows that do not satisfy the join predicate. • LEFT OUTER JOIN keeps all rows from the left table. Non-matching columns from the right table are returned as NULL.• RIGHT OUTER JOIN keeps all rows from the right table. Non-matching columns from the left table are returned as NULL.• FULL OUTER JOIN keeps all rows from both tables, returning NULLs where a partner row is absent.Because unmatched data is preserved, OUTER JOIN is ideal for exception reporting, coverage analysis, slowly changing dimensions, and optional relationships. Performance depends on index quality and predicate selectivity. Many databases allow the optional OUTER keyword; writing LEFT JOIN is identical to LEFT OUTER JOIN. SQLite lacks FULL OUTER JOIN natively; work-arounds use UNION or subqueries. Cartesian explosion is impossible because only one copy of each preserved row is returned.

SQL OUTER JOIN Syntax

-- LEFT OUTER JOIN
SELECT column_list
FROM   table1
LEFT [OUTER] JOIN table2 ON table1.key = table2.key;

-- RIGHT OUTER JOIN
SELECT column_list
FROM   table1
RIGHT [OUTER] JOIN table2 ON table1.key = table2.key;

-- FULL OUTER JOIN
SELECT column_list
FROM   table1
FULL [OUTER] JOIN table2 ON table1.key = table2.key;

SQL OUTER JOIN Parameters

table1 (table or subquery) - The left input set.
table2 (table or subquery) - The right input set.
join_condition (boolean expression) - Predicate that defines row matching.
join_type (keyword) - LEFT, RIGHT, or FULL determines which side(s) preserve unmatched rows.

Example Queries Using SQL OUTER JOIN

-- Show customers and any orders they placed (customers without orders included)
SELECT c.customer_id,
       c.name,
       o.order_id,
       o.order_date
FROM   customers AS c
LEFT OUTER JOIN orders AS o ON c.customer_id = o.customer_id;

-- List orders and related shipment records (orders without shipments included)
SELECT o.order_id,
       o.total_amount,
       s.shipped_at
FROM   orders AS o
RIGHT OUTER JOIN shipments AS s ON o.order_id = s.order_id;

-- Inventory audit: products with or without sales, and sales with or without products (edge cases)
SELECT p.product_id,
       p.product_name,
       s.quantity_sold
FROM   products  AS p
FULL OUTER JOIN sales AS s ON p.product_id = s.product_id;

Expected Output Using SQL OUTER JOIN

LEFT OUTER JOIN query returns every customer
For customers with no orders, order_id and order_date are NULL
RIGHT OUTER JOIN query returns every shipment
For shipments that do not match an order, order columns are NULL
FULL OUTER JOIN query returns every product and every sale
Where a product lacks a sale or a sale lacks a product, the opposite side's columns are NULL

Use Cases with SQL OUTER JOIN

Keep master records that have no related detail rows.
Produce exception lists such as orders without shipments.
Combine slowly changing dimension tables where gaps must be shown.
Perform data quality checks to find orphaned rows.
Create comparative period reports that show missing dates or categories.

Common Mistakes with SQL OUTER JOIN

Forgetting the ON clause, which causes a syntax error.
Assuming LEFT JOIN will filter out NULLs - it preserves them.
Reversing table order and accidentally changing which rows are preserved.
Expecting FULL OUTER JOIN support in SQLite or older MySQL versions.
Using WHERE conditions that nullify the OUTER effect (e.g., filtering on right_table.column IS NOT NULL after a LEFT JOIN).

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is the difference between LEFT OUTER JOIN and RIGHT OUTER JOIN?

LEFT OUTER JOIN keeps every row from the left table, while RIGHT OUTER JOIN keeps every row from the right table. The non-preserved side returns NULLs where no match exists.

Is OUTER JOIN slower than INNER JOIN?

Usually yes, because the database must keep additional rows. Indexes on join keys and selective predicates reduce the gap.

How do I emulate FULL OUTER JOIN in SQLite?

Combine two queries: one LEFT JOIN and one RIGHT JOIN (or a UNION of LEFT JOIN with reversed tables) and remove duplicates with UNION or UNION ALL plus filtering.

When should I avoid OUTER JOIN?

Avoid when you only need matching rows, because OUTER JOIN adds overhead and complexity. Use INNER JOIN instead for pure intersection queries.