LEFT and RIGHT JOIN

LEFT and RIGHT JOIN

LEFT JOIN

LEFT JOIN returns all rows from the left table, plus matching rows from the right. Where there is no match, the right columns are NULL.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

"Around the Horn" has no order, so OrderID is NULL - but the customer still appears.

RIGHT JOIN

RIGHT JOIN is the mirror: all rows from the right table, plus matching left rows.

SELECT Customers.CustomerName, Orders.OrderID
FROM Orders
RIGHT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

Which side is which?

• The table written first (after FROM) is the left table.
• The table after JOIN is the right table.
• LEFT JOIN keeps all of the left; RIGHT JOIN keeps all of the right.

TL;DR

• LEFT JOIN keeps every left row; missing right values become NULL.
• RIGHT JOIN keeps every right row; missing left values become NULL.
• The order of the tables decides left and right.