SQL Right Join Example

This article explains SQL RIGHT JOIN syntax and gives an example on how to use RIGHT JOIN

Right join returns all the values from the right table, plus matched values from the left table. If there are no matches in the left table for given record from the right table, the resulting value will be returned as NULL.

RIGHT JOIN and RIGHT OUTER JOIN are the same in terms of terminology.

You can use following links to view the different types of SQL JOINs :

1. Inner Join
2. Left Join
3. Right Join
4. Outer Join

Right Join Visual Representation

Right Join Syntax

This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B)

SELECT Table_A.column1, Table_B.column2...
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key

Right Join Example

We will create 3 tables

1. CUSTOMER
2. PRODUCT
3. ORDER

A customer can order products. In the ORDER table we hold the customer ID and the quantity of each product the customer has ordered.

Use following SQL scripts to create the three tables.

Create CUSTOMER table

CREATE TABLE `CUSTOMER` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));

Create PRODUCT table

CREATE TABLE `PRODUCT` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `price` DECIMAL(7,2) NOT NULL,
  PRIMARY KEY (`id`));

Create ORDER table

CREATE TABLE `ORDER` (
  `id` INT NOT NULL,
  `date` DATETIME NOT NULL,
  `customer_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `quantity` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `product_id_idx` (`product_id` ASC) VISIBLE,
  INDEX `customer_id_idx` (`customer_id` ASC) VISIBLE,
  CONSTRAINT `customer_id`
    FOREIGN KEY (`customer_id`)
    REFERENCES `CUSTOMER` (`id`),
  CONSTRAINT `product_id`
    FOREIGN KEY (`product_id`)
    REFERENCES `PRODUCT` (`id`));

Insert data in CUSTOMER table

INSERT INTO `CUSTOMER` (`id`, `name`) VALUES ('1', 'Jon Snow');
INSERT INTO `CUSTOMER` (`id`, `name`) VALUES ('2', 'Daenerys Targaryen');
INSERT INTO `CUSTOMER` (`id`, `name`) VALUES ('3', 'Sansa Stark');
INSERT INTO `CUSTOMER` (`id`, `name`) VALUES ('4', 'Arya Stark');
INSERT INTO `CUSTOMER` (`id`, `name`) VALUES ('5', 'Jorah Mormont');
INSERT INTO `CUSTOMER` (`id`, `name`) VALUES ('6', 'Bronn of the Blackwater');

Insert data in PRODUCT table

INSERT INTO `PRODUCT` (`id`, `name`, `price`) VALUES ('1', 'Dragon', '5000');
INSERT INTO `PRODUCT` (`id`, `name`, `price`) VALUES ('2', 'Castle', '1000');
INSERT INTO `PRODUCT` (`id`, `name`, `price`) VALUES ('3', 'Sword', '5');

Insert data in ORDER table

INSERT INTO `ORDER` (`id`, `date`, `customer_id`, `product_id`, `quantity`) VALUES ('1', '2019-01-08 00:00:00', '2', '1', '3');
INSERT INTO `ORDER` (`id`, `date`, `customer_id`, `product_id`, `quantity`) VALUES ('2', '2019-01-22 00:00:00', '6', '3', '1');
INSERT INTO `ORDER` (`id`, `date`, `customer_id`, `product_id`, `quantity`) VALUES ('3', '2019-02-15 00:00:00', '6', '2', '1');
INSERT INTO `ORDER` (`id`, `date`, `customer_id`, `product_id`, `quantity`) VALUES ('4', '2019-02-16 00:00:00', '1', '3', '1');

Database tables

And now that’s what we have in our tables:

Right join tables

Now, let us join these tables using RIGHT JOIN

This query returns customer details and purchase date

SELECT `CUSTOMER`.id, `CUSTOMER`.name, `ORDER`.date
FROM `CUSTOMER`
RIGHT JOIN `ORDER` 
ON `CUSTOMER`.id = `ORDER`.customer_id;

Disclaimer: The example shown above has been tested with MySQL. Depending on your SQL database the CREATE TABLE syntax may vary.