SQL RIGHT JOIN

SQL RIGHT JOIN Explained

A RIGHT JOIN performs a join starting with the right table.

Then, any matching records from the left table will be included.

Rows without a match return columns with NULL values.

Example

#

Problem: List all products that have no orders.

SELECT ProductName
  FROM OrderItem I 
 RIGHT JOIN Product P ON P.Id = I.ProductId
 WHERE I.Id IS NULL

The results show that only 1 product never sold.

Syntax

RIGHT JOIN syntax.

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

RIGHT OUTER JOIN syntax.

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

RIGHT JOIN and RIGHT OUTER JOIN are the same. The OUTER keyword is optional.


More Examples

SQL RIGHT JOIN example

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List customers that have not placed orders.
SELECT FirstName, LastName, City, Country, TotalAmount
  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
FirstName LastName City Country TotalAmount
Diego Roel Madrid Spain NULL
Marie Bertrand Paris France NULL

SQL RIGHT JOIN and JOIN

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). Order the results by number of orders.
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

You may also like



Guides