Outer join and full outer join are not the same thing
In SQL, outer join is a category that includes three different types of joins: left outer join, right outer join, and full outer join. When someone says "outer join" without specifying which type, they usually mean one of the first two. A full outer join is a specific join type that returns all rows from both tables, whether or not they match. The difference matters because each one produces different results, and using the wrong one will give you incomplete or unexpected data.
Most databases support left and right outer joins natively. Full outer joins are less common — MySQL does not support them directly, though you can build one using a union of left and right joins. Understanding which join you actually need prevents you from writing queries that return the wrong rows or that fail to run at all.
Key Takeaways
- Outer join is a category containing left outer join, right outer join, and full outer join — not a single join type.
- A left outer join returns all rows from the left table plus matching rows from the right table, with nulls where no match exists.
- A right outer join returns all rows from the right table plus matching rows from the left table, with nulls where no match exists.
- A full outer join returns all rows from both tables, with nulls where no match exists — and not all databases support it directly.
- The join type you choose determines which rows appear in your results and whether you lose data from either table.
How left and right outer joins work
A left outer join keeps every row from the table on the left side of the join, and adds matching rows from the table on the right. If a row from the left table has no match on the right, the result still includes that row — but the columns from the right table show as null (empty).
A right outer join does the opposite: it keeps every row from the table on the right, and adds matching rows from the left. Unmatched rows from the right table still appear, with nulls in the left columns.
The choice between left and right is often just about which table you list first in your query. If you write SELECT * FROM customers LEFT OUTER JOIN orders, you keep all customers. If you write SELECT * FROM orders RIGHT OUTER JOIN customers, you also keep all customers — the result is identical, just written differently. Most developers pick left outer join and arrange their tables in the order that makes sense for the question they are asking.
What full outer join actually does
A full outer join returns every row from both tables. If a row from the left table has no match on the right, it appears with nulls in the right columns. If a row from the right table has no match on the left, it appears with nulls in the left columns. If a row matches, it appears once with data from both sides.
This is useful when you need to see everything — all customers whether or not they have orders, and all orders whether or not they have a matching customer record. A full outer join catches data quality problems: if you find orders with no customer, you know something is wrong in your database.
The catch is that MySQL, one of the most common databases, does not support full outer join syntax. PostgreSQL, SQL Server, and Oracle do. If you are using MySQL and need a full outer join, you build it by combining a left outer join and a right outer join with a UNION operator, which merges the results and removes duplicates.
When to use each join type
Use a left outer join when you want to keep all rows from your primary table and add information from a secondary table where it exists. For example: show all customers and their order totals, with null for customers who have never ordered.
Use a right outer join when the table on the right is your primary table. This is less common in practice because you can always rewrite it as a left join by swapping the table order, but it exists for readability when the right table is logically the main one.
Use a full outer join when you need to see all rows from both tables, regardless of whether they match. This is common in reconciliation queries, data migration work, and quality checks. It is also the join type that most clearly shows you when your data is incomplete or broken.
A concrete example
Imagine two tables: customers (with customer IDs and names) and orders (with order IDs and customer IDs). Customer 5 exists but has no orders. Order 99 exists but has no matching customer record.
A left outer join on customers returns customer 5 with null in the order columns, but does not return order 99 at all. A right outer join on orders returns order 99 with null in the customer columns, but does not return customer 5 at all. A full outer join returns both: customer 5 with nulls in the order columns, and order 99 with nulls in the customer columns.
Which one you use depends on your question. If you are analyzing customer behavior, a left join on customers is correct — you care about all customers, not all orders. If you are auditing orders, a right join on orders is correct. If you are checking data integrity, a full join is correct.
Database support and syntax differences
PostgreSQL, SQL Server, and Oracle support full outer join with straightforward syntax: SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id = table2.id. MySQL does not recognize this syntax and will return an error.
In MySQL, you build a full outer join by writing a left join and a right join, then combining them with UNION: SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id UNION SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id. This is longer but produces the same result.
SQLite supports full outer join in recent versions (3.39.0 and later), but older versions do not. If you are working with an older SQLite database, you will need the UNION approach.
Common mistakes when choosing a join
The most common mistake is using an inner join when you meant an outer join. An inner join returns only rows that match in both tables — it silently drops unmatched rows. If you run an inner join and get fewer rows than you expected, you have lost data.
Another mistake is using a left join when you need a full join, then wondering why certain rows are missing. If you are building a reconciliation report and you only see half the discrepancies, check whether you should be using a full join instead.
A third mistake is assuming that "outer join" means full outer join. It does not. Always specify which type — left, right, or full — to be clear about what you want and to avoid confusion when someone else reads your code.
Frequently Asked Questions
Can I use a full outer join in MySQL?
MySQL does not support full outer join syntax directly. You can achieve the same result by writing a left outer join and a right outer join, then combining them with UNION. This returns all rows from both tables, which is what a full outer join does.
What is the difference between outer join and inner join?
An inner join returns only rows that match in both tables. An outer join (left, right, or full) returns matching rows plus unmatched rows from at least one table, with nulls where no match exists. Inner joins lose data; outer joins preserve it.
If I use a left outer join, will I see all rows from the right table?
No. A left outer join returns all rows from the left table, but only matching rows from the right table. Rows in the right table with no match on the left do not appear. Use a full outer join if you need to see all rows from both tables.
Does the order of tables matter in an outer join?
Yes. A left outer join keeps all rows from the left table, and a right outer join keeps all rows from the right table. You can rewrite any right join as a left join by swapping the table order, so the choice is often about readability rather than functionality.
When should I use a full outer join instead of a left or right join?
Use a full outer join when you need to see all rows from both tables, such as in data reconciliation, quality audits, or migration work. Use a left or right join when one table is your primary source and the other is supplementary information.