Course contentsShow
← Data Science
Lesson 940 of 2,14524. SQL JoinsFree lesson

Database Support and Alternatives

Understanding which databases support FULL OUTER JOIN and how to emulate it using UNION when needed.

Database Support and Alternatives

What You'll Learn

You'll discover which popular databases support FULL OUTER JOIN natively and how to replicate its functionality in systems that don't.

Not All Databases Are Created Equal

While FULL OUTER JOIN is part of the SQL standard, not every database management system implements it. Knowing your database's capabilities—and workarounds—is crucial for writing portable, effective queries.

Databases with Native Support

Most modern relational databases fully support FULL OUTER JOIN:

  • PostgreSQL ✓
  • SQL Server ✓
  • Oracle ✓
  • DB2 ✓

Databases Without Native Support

MySQL (versions before 8.0.19) and SQLite notably lack native FULL OUTER JOIN support. If you're working with these systems, you'll need an alternative.

The UNION Workaround

When FULL OUTER JOIN isn't available, you can emulate it by combining a LEFT JOIN and a RIGHT JOIN using UNION:

-- Emulating FULL OUTER JOIN with UNION
SELECT *
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id

UNION

SELECT *
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id

This works because UNION automatically removes duplicates, giving you the same result as a FULL OUTER JOIN: all matched rows plus unmatched rows from both tables.

Think of it like this: If you can't get a group photo with everyone standing together, you take one photo from the left side and another from the right side, then merge them—making sure no one appears twice.

Practical Considerations

Always check your database documentation before writing queries. If portability matters, either test your UNION alternative or stick with databases that support the full SQL standard.

Key Takeaway: FULL OUTER JOIN isn't universally supported—PostgreSQL, SQL Server, and Oracle have it natively, but MySQL and SQLite require a UNION-based workaround combining LEFT and RIGHT JOINs.