Outer Join and Full Outer Join: What's Actually Different

No, they are not the same, but the difference depends on your database system

In standard SQL, OUTER JOIN by itself is incomplete syntax — it requires a direction. FULL OUTER JOIN is a complete join type that returns all rows from both tables, matching where possible and filling in nulls where no match exists. Some databases treat a bare "OUTER JOIN" as an error. Others treat it as shorthand for LEFT OUTER JOIN, which returns all rows from the left table and matching rows from the right. This inconsistency is why you should always write the full phrase.

The practical difference matters when you write code that needs to run on multiple systems. PostgreSQL, SQL Server, and Oracle all support FULL OUTER JOIN explicitly. MySQL does not — it will reject the syntax entirely. If you write "OUTER JOIN" without specifying LEFT or RIGHT, you are relying on your database to guess your intent, which is a source of bugs.

Key Takeaways

FULL OUTER JOIN returns all rows from both tables with nulls where no match exists; a bare OUTER JOIN is incomplete and may be interpreted differently across databases.
PostgreSQL, SQL Server, and Oracle support FULL OUTER JOIN directly; MySQL does not and will reject the syntax.
Always write LEFT OUTER JOIN or RIGHT OUTER JOIN explicitly rather than relying on OUTER JOIN alone, because different databases treat the bare phrase differently.
To simulate FULL OUTER JOIN in MySQL, use UNION to combine a LEFT JOIN and a RIGHT JOIN, excluding rows already matched in the first query.

What FULL OUTER JOIN actually does

A FULL OUTER JOIN returns every row from the left table and every row from the right table. When a row from the left table has a matching row in the right table (based on your ON condition), they appear together in one result row. When a row has no match, it still appears in the result, but the columns from the other table are filled with NULL.

For example, if you join a customers table to an orders table on customer ID, a FULL OUTER JOIN shows you every customer (even those with no orders) and every order (even those with no matching customer record). A customer with no orders appears with NULL values in all the order columns. An order with no matching customer appears with NULL values in all the customer columns.

This is useful when you need to find mismatches or gaps: customers who never placed an order, orders that were entered with an invalid customer ID, or both at once. Most other join types hide one side or the other.

Why OUTER JOIN alone is ambiguous

The SQL standard defines three directional outer joins: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. A bare OUTER JOIN is not a complete instruction. It tells the database to do an outer join, but not which kind.

PostgreSQL, SQL Server, and Oracle interpret a bare OUTER JOIN as a syntax error and require you to specify the direction. MySQL does not support FULL OUTER JOIN at all, so the question does not arise in the same way. SQLite accepts FULL OUTER JOIN but not a bare OUTER JOIN. The inconsistency means that code written with just "OUTER JOIN" will fail on some systems and succeed on others, possibly with unexpected results.

The safest practice is to always write the full phrase: LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN. This makes your intent clear to anyone reading the code and ensures the query behaves the same way across different databases.

How to write FULL OUTER JOIN in databases that support it

In PostgreSQL, SQL Server, and Oracle, the syntax is straightforward:

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

This returns every customer and every order. Customers with no orders show NULL in the order_id column. Orders with no matching customer show NULL in the name column. The ON clause defines what counts as a match — in this case, when the customer ID in the orders table equals the ID in the customers table.

You can filter the results afterward with a WHERE clause to find only the mismatches. For instance, WHERE customers.id IS NULL OR orders.customer_id IS NULL returns only rows where one side had no match.

Simulating FULL OUTER JOIN in MySQL

MySQL does not support FULL OUTER JOIN syntax, so you need to build the same result using UNION. The idea is to run a LEFT JOIN and a RIGHT JOIN separately, then combine them while excluding duplicates.

Here is the pattern:

SELECT customers.name, orders.order_id FROM customers LEFT JOIN orders ON customers.id = orders.customer_id UNION SELECT customers.name, orders.order_id FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id;

The LEFT JOIN gets all customers and matching orders. The RIGHT JOIN gets all orders and matching customers. UNION combines both result sets and removes exact duplicates (rows that appear in both). The result is the same as a FULL OUTER JOIN: every customer and every order, with NULLs where no match exists.

This approach is slower than a native FULL OUTER JOIN because the database has to run two separate joins and then deduplicate. If you are working with large tables, the performance difference can be noticeable. But it is the standard workaround when your database does not support the syntax.

When to use FULL OUTER JOIN versus other joins

Use FULL OUTER JOIN when you need to see all rows from both tables, including those without matches. Common cases include data reconciliation (comparing two datasets), finding orphaned records (orders with no customer, or customers with no orders), and generating complete reports that should include every entity even if some have no activity.

Use LEFT OUTER JOIN when you want all rows from the left table plus matching rows from the right. This is common when you are starting with a primary list (like all customers) and adding related data (like their orders) without losing any customers.

Use RIGHT OUTER JOIN when you want all rows from the right table plus matching rows from the left. This is less common in practice because you can usually rewrite it as a LEFT JOIN by swapping the table order.

Use INNER JOIN when you only want rows that have matches in both tables. This is the most common join type and is often the fastest because the database can skip unmatched rows entirely.

Checking your database documentation

If you are unsure whether your database supports FULL OUTER JOIN, check the official documentation or test it. PostgreSQL, SQL Server, Oracle, and SQLite all support it. MySQL, MariaDB, and older versions of some other systems do not.

Most database management tools (like pgAdmin for PostgreSQL, SQL Server Management Studio, or DBeaver) will highlight syntax errors in real time, so you will know immediately if FULL OUTER JOIN is not recognized. If you see an error, use the UNION approach or switch to a different join type depending on what you actually need.

When writing code that needs to work across multiple databases, document which join types you are using and test on each target system. This prevents surprises when you deploy to production.

Frequently Asked Questions

Can I use OUTER JOIN without LEFT or RIGHT in any database?

No standard database accepts a bare OUTER JOIN as valid syntax. PostgreSQL, SQL Server, and Oracle reject it. Some databases may have non-standard extensions, but you should not rely on this. Always write the full phrase to ensure portability.

Is FULL OUTER JOIN slower than LEFT JOIN?

FULL OUTER JOIN is usually slightly slower because the database has to check both tables for unmatched rows. In MySQL, the UNION workaround is noticeably slower because it runs two separate joins. But the difference is usually small unless you are working with very large tables. Correctness matters more than micro-optimizations.

What happens if I use FULL OUTER JOIN in MySQL?

MySQL will return a syntax error and refuse to run the query. You must use the UNION approach with LEFT JOIN and RIGHT JOIN instead. If you are migrating code from PostgreSQL or SQL Server to MySQL, you will need to rewrite any FULL OUTER JOINs.

Can I filter FULL OUTER JOIN results to show only unmatched rows?

Yes. Add a WHERE clause that checks for NULL values on one side: WHERE customers.id IS NULL OR orders.customer_id IS NULL. This returns only rows where one table had no match, which is useful for finding orphaned or missing records.

Should I use OUTER JOIN or FULL OUTER JOIN in my code?

Always use FULL OUTER JOIN (or LEFT OUTER JOIN or RIGHT OUTER JOIN) explicitly. Never use a bare OUTER JOIN. It is clearer, more portable, and less likely to cause bugs when your code runs on a different database system.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.