All 7 Joins questions
More in Joins
What is an INNER JOIN, and how does it combine data from two tables? What is the difference between a LEFT JOIN and a RIGHT JOIN? How do you find rows that exist in one table but have no matching row in another table using a join? What common mistakes do developers make when writing multi table joins that can lead to unexpectedly large or incorrect result sets? How does a CROSS JOIN work, and what are some legitimate use cases for it despite the risk of accidentally creating a huge result set? How does SQL Server's query optimizer decide which physical join algorithm, such as nested loops, merge join, or hash join, to actually use for a given query?
Advertisement
SQL Server › Joins

What is a FULL OUTER JOIN, and when would you use one instead of a LEFT or RIGHT JOIN?

Intermediate

Answer

A FULL OUTER JOIN returns all rows from both tables, matching them where possible and filling in NULL values on either side when no match exists, which is useful when you need to see the complete picture from both tables, including unmatched rows from either side, rather than favoring one table over the other.
SELECT c.Name, o.OrderId
FROM Customers c
FULL OUTER JOIN Orders o ON c.CustomerId = o.CustomerId;
Real-world example A data reconciliation report uses a FULL OUTER JOIN to compare two systems, showing customers with no matching orders and orders with no matching customers, helping identify data inconsistencies in either direction.

Common follow-ups: How common is FULL OUTER JOIN compared to INNER and LEFT JOIN in typical applications?;What does the result look like for rows that match in both tables?

Data Types & Schema Design;Query Optimization & Plans
All 7 Joins questions