What are some good examples where SQL's OUTER JOIN is used?

Question

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?

Answer 1

A LEFT OUTER JOIN can be used when you want all records from one table, as well as records from another table if any.

E.g., given table User and Address, where Address has a FK to User and there could be 0 or more addresses per user:

select *
from User u
left outer join Address a on u.UserID = a.UserID

This will ensure you get all User records, regardless of whether there was a corresponding Address record or not.

If you want to show all Users that do not have addresses, you can do this:

select *
from User u
left outer join Address a on u.UserID = a.UserID
where a.UserID is null

Answer 2

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.

Answer 3

Classic example is cutomers and orders. Some customers have orders and others do not. You want to show a list of customers with total sales. So you do a left outer join from the customer to the order and get:

Customer A: $100; Customer B: $0; Customer C: $500

instead of:

Customer A: $100; Customer C: $500

Answer 4

Get a list of all customers including any details of orders they have made. Some customers may not have made orders and therefore an INNER JOIN would exclude them from this list.

SELECT
    *
FROM
    Customer
LEFT OUTER JOIN
    Order
ON
    Customer.CustomerId = Order.CustomerId

Answer 5

Here is an example:

I need a list of all customers, with their vouchers, I also need the customers that never used vouchers.

SELECT *
FROM Customer
LEFT OUTER JOIN Voucher
 ON Customer.CustomerId = Voucher.CustomerId

Answer 6

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

Answer 7

Following is the visual represntation of the left outer join

SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key

read more about joins in the below article http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx ( one of the best article must read )