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


Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Licensing       EULA       Sitemap      
© Data & Object Factory, LLC.
Made with    in Austin, Texas