SQL RIGHT JOIN

What is a RIGHT JOIN in SQL?

A RIGHT JOIN performs a join starting with the second (right-most) table and then any matching first (left-most) table records. RIGHT JOIN and RIGHT OUTER JOIN are the same.

The SQL RIGHT JOIN syntax

The general syntax is

SELECT column-names
  FROM table-name1 RIGHT JOIN table-name2 
    ON column-name1 = column-name2
 WHERE condition

The general RIGHT OUTER JOIN syntax is:

SELECT column-names
  FROM table-name1 RIGHT OUTER JOIN table-name2 
    ON column-name1 = column-name2
 WHERE condition

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount

SQL RIGHT JOIN Examples

Problem: List customers that have not placed orders

SELECT TotalAmount, FirstName, LastName, City, Country
  FROM [Order] O RIGHT JOIN Customer C
    ON O.CustomerId = C.Id
WHERE TotalAmount IS NULL

This returns customers that, when joined, have no matching order.

Result: 2 records

TotalAmount	FirstName	LastName	City	Country
NULL	Diego	Roel	Madrid	Spain
NULL	Marie	Bertrand	Paris	France

Problem: List all customers (with or without orders) and a count of the orders that include a 2kg box with Konbu (product with Id = 13).

CUSTOMER
Id
FirstName
LastName
City
Country
Phone

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount

ORDERITEM
Id
OrderId
ProductId
UnitPrice
Quantity

SELECT DISTINCT (C.Id), Firstname, LastName, COUNT(O.Id) AS Orders
  FROM [Order] O  
  JOIN OrderItem I ON O.Id = I.OrderId AND I.ProductId = 13
 RIGHT JOIN Customer C ON C.Id = O.CustomerId
 GROUP BY C.Id, FirstName, LastName
 ORDER BY COUNT(O.Id)

This returns all customers whether they have orders or not. The ones with orders we check if productId = 13 is present.

Result: 91 records (all customers)

Id	FirstName	LastName	Orders
1	Maria	Anders	0
3	Antonio	Moreno	0
6	Hanna	Moos	0

36	Yoshi	Latimer	1
88	Paula	Patenta	1
85	Paul	Henriot	1

11	Victoria	Ashworth	2
17	Sven	Ottlieb	2
20	Roland	Mendel	2

71	Jose	Pavarotti	4