LEFT JOIN and RIGHT JOIN

Transact-SQL
1

This is my first post and I'm not sure if I posted to the right category. I've been looking for some online examples that explain the use of LEFT JOIN and RIGHT JOIN. As a beginner I don't quite understand these two types of outer joins.

According to my understanding, LEFT (or RIGHT) JOIN seems to select all records from the left (or right) table regardless of whether those records meet the specified condition. Is my understanding wrong? Appreciate it if you could share some online resources to explain.

2

Your understanding is correct.

For example, let's say I want to list all customer orders, but also list every customer even if they haven't yet placed an order.

SELECT C.*, O.*
FROM Customers C
LEFT OUTER JOIN Orders O ON O.customer_id = C.customer_id
1 Like
3

Note that SQL will automatically assign NULL values to any missing data. For example, in the query above, the O.* columns will all be NULL if the Customer has never placed an Order.

This fact can be very useful when you need to determine if you found a match in the right-hand table or not. For example:

SELECT C.*, O.*, 
    CASE WHEN O.customer_id IS NULL THEN 'Cust has never placed an order.' ELSE '' END AS Message
FROM Customers C
LEFT OUTER JOIN Orders O ON O.customer_id = C.customer_id
1 Like
4

This example is really helpful. Thank you so much for your prompt reply and clear explanation!