Menu
I often get asked the questions in an interview that "what is an outer join in SQL"?
While it can be answered, I wonder what might be some classic and good real life examples where a (LEFT) OUTER JOIN is used?
390
We use the SQL OUTER JOIN to match rows between tables. We might want to get match rows along with unmatched rows as well from one or both of the tables.
An full outer join is a method of combining tables so that the result includes unmatched rows of both tables. If you are joining two tables and want the result set to include unmatched rows from both tables, use a FULL OUTER JOIN clause.
Outer joins are joins that return matched values and unmatched values from either or both tables. There are a few types of outer joins: LEFT JOIN returns only unmatched rows from the left table, as well as matched rows in both tables.
OUTER JOIN is used to retrieve all records from tables, even for those records with no matching value in the other table based on the JOIN condition. In such cases, it returns NULL as the value for the missing columns.
In the Northwind database on the Customers and Orders table.
Doing an inner join will only give you customers that have placed orders.
Doing an outer join will get all customers and orders for customers that have placed orders.
81
To add to Robin Day's answer, you can also use a Left Outer Join to grab only customers who have NOT placed orders by checking for NULL.
SELECT *
FROM Customer
LEFT OUTER JOIN Order
ON Customer.CustomerId = Order.CustomerId
WHERE Order.CustomerId IS NULL
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
