SQL OUTER

What is the SQL OUTER keyword?

OUTER is an optional keyword in JOIN clauses that explicitly marks a LEFT, RIGHT, or FULL join as an outer join, preserving unmatched rows as 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: PostgreSQL: LEFT/RIGHT/FULL OUTER JOIN MySQL: LEFT/RIGHT OUTER JOIN (FULL not supported) SQL Server: LEFT/RIGHT/FULL OUTER JOIN Oracle: LEFT/RIGHT/FULL OUTER JOIN SQLite: LEFT OUTER JOIN only

SQL OUTER Full Explanation

In the ANSI-SQL join syntax, OUTER appears only in combination with LEFT, RIGHT, or FULL before the JOIN keyword (e.g., LEFT OUTER JOIN). An outer join returns all rows from one or both participating tables even when no matching row exists in the other table, filling non-matching columns with NULL. The word OUTER is purely descriptive – omitting it (LEFT JOIN) produces identical results because most SQL engines default to an outer interpretation when LEFT, RIGHT, or FULL precede JOIN. OUTER cannot be used alone, cannot follow INNER or CROSS, and has no impact outside join clauses. Some dialects lack FULL OUTER JOIN but still allow OUTER in LEFT or RIGHT joins. Because OUTER is optional, its primary purpose is readability and standards compliance rather than functionality.

SQL OUTER Syntax

-- General pattern
SELECT column_list
FROM   table1
LEFT  OUTER JOIN table2 ON join_condition;

SELECT column_list
FROM   table1
RIGHT OUTER JOIN table2 ON join_condition;

SELECT column_list
FROM   table1
FULL  OUTER JOIN table2 ON join_condition;

SQL OUTER Parameters

Example Queries Using SQL OUTER

-- 1. Return all customers even if they placed no orders
SELECT c.id, c.name, o.order_id
FROM   customers c
LEFT OUTER JOIN orders o ON c.id = o.customer_id;

-- 2. Show any orders that refer to a missing customer row (data quality check)
SELECT o.order_id, c.id AS customer_id
FROM   customers c
RIGHT OUTER JOIN orders o ON c.id = o.customer_id
WHERE  c.id IS NULL;

-- 3. Combine employee and contractor tables, keeping non-overlapping records
SELECT COALESCE(e.id, ct.id)   AS person_id,
       e.name                  AS employee_name,
       ct.name                 AS contractor_name
FROM   employees  e
FULL OUTER JOIN contractors ct ON e.email = ct.email;

Expected Output Using SQL OUTER

Each query returns all rows from the preserved side(s) of the join
Where no matching row exists, the columns from the other table are NULL
The result set cardinality equals the total preserved rows, not just the matching ones

Use Cases with SQL OUTER

Keep all master records (customers, products) while pulling optional details
Identify orphaned records or data quality gaps
Merge datasets that overlap only partially
Generate metric denominators while attaching sparse fact data

Common Mistakes with SQL OUTER

Believing OUTER is required (LEFT JOIN works the same)
Writing OUTER JOIN without LEFT, RIGHT, or FULL – this is invalid
Forgetting the ON clause, causing a cartesian product
Expecting FULL OUTER JOIN support in MySQL or SQLite (not available)

First Introduced In

ANSI SQL-92

Frequently Asked Questions

What does OUTER mean in a JOIN?

OUTER marks a join that keeps rows that fail to match, filling the other table's columns with NULL.

Is OUTER required after LEFT, RIGHT, or FULL?

No. Including OUTER improves readability but omitting it yields the same result.

Which databases support FULL OUTER JOIN?

PostgreSQL, SQL Server, and Oracle support FULL OUTER JOIN. MySQL and SQLite do not.

How can I emulate FULL OUTER JOIN in MySQL?

Use a UNION of a LEFT JOIN and a RIGHT JOIN filtered to non-overlapping rows.