FULL OUTER JOIN availability across databases in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a FULL OUTER JOIN in SQL?

A FULL OUTER JOIN returns all rows when there is a match in either left or right table. Rows without a match in one table will have NULLs for columns from the other table.

intermediate

Does MySQL support FULL OUTER JOIN natively?

No, MySQL does not support FULL OUTER JOIN directly. You can simulate it using a UNION of LEFT JOIN and RIGHT JOIN.

beginner

Which popular databases support FULL OUTER JOIN natively?

PostgreSQL, SQL Server, and Oracle support FULL OUTER JOIN natively. SQLite does not support FULL OUTER JOIN natively as of version 3.39.0.

intermediate

How can you simulate FULL OUTER JOIN in databases that don't support it?

You can simulate FULL OUTER JOIN by combining LEFT JOIN and RIGHT JOIN with UNION, ensuring all rows from both tables appear.

advanced

Why might FULL OUTER JOIN be less common in some database systems?

Because FULL OUTER JOIN can be expensive to compute and some databases prioritize simpler joins, they may omit native support to encourage more efficient queries.

Which SQL join returns all rows from both tables, filling NULLs when no match exists?

A LEFT JOIN

B INNER JOIN

C CROSS JOIN

D FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables, with NULLs where there is no match.

Does MySQL support FULL OUTER JOIN directly?

A Only in latest versions

B Yes, fully supported

C No, it must be simulated

D Only with special extensions

MySQL does not support FULL OUTER JOIN natively; it requires simulation using UNION of LEFT and RIGHT JOIN.

Which database added native FULL OUTER JOIN support starting from version 3.39.0?

A SQLite

B MySQL

C MariaDB

D MongoDB

SQLite does not support FULL OUTER JOIN natively as of version 3.39.0.

What SQL operation can simulate FULL OUTER JOIN in databases without native support?

A UNION of LEFT JOIN and RIGHT JOIN

B INNER JOIN with WHERE clause

C CROSS JOIN with filter

D GROUP BY with HAVING

Combining LEFT JOIN and RIGHT JOIN with UNION simulates FULL OUTER JOIN.

Which of these databases supports FULL OUTER JOIN natively?

A MySQL

B PostgreSQL

C MariaDB

D None of the above

PostgreSQL supports FULL OUTER JOIN natively.

Explain what a FULL OUTER JOIN does and how you can achieve similar results in MySQL.

Think about combining two types of joins to get all rows.

List some popular databases that support FULL OUTER JOIN natively and one that does not.

Consider common open source and commercial databases.