Description

SQL RIGHT JOIN can be used to join two tables, which returns all the rows from the right table along with the associated data from the left table that met the join condition.

If there is a row in the right table but no match found in the left table, then the associate result row contains NULL values against all the columns coming from the left table.

The left table is the one that appears leftmost in the JOIN clause, and the right table is the one that appears rightmost.

  • It is a type of outer join, so it is also referred to as the left outer join.
  • It can be implemented on SELECT, UPDATE, and DELETE statements.

Also, check the other types of outer join - left join and full join.

Syntax

Here is the basic syntax of a SELECT statement with a RIGHT JOIN.

  • The table1 and table2 represents the left and right table names.
  • The column_list represents the column names from either or both the tables.
  • The column1 and column2 are the columns from the left and right tables, which are used for creating the LEFT JOIN.
SELECT column_list
FROM table1
RIGHT JOIN table2 ON table1.column1 = table2.column2;

Table Data

Consider the below data in the orders, customers, and shippers tables, with the below observations.

  • Tables orders and customers are relationally connected via customer_id column.
  • Tables orders and shippers are relationally connected via shipper_id column.

Orders Table Data

order_id order_date order_value customer_id shipper_id
10250 2014-07-05 1807.5 3 1
10251 2014-07-15 1159 2 2
10252 2014-07-18 2822 1 3
10253 2014-07-19 2575.3 3 2
10254 2014-07-24 1256.25 1 3
10255 2014-07-31 9247.5 5 1

Customers Table Data

customer_id customer_name address  city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100

Shipper Table Data

shipper_id shipper_name phone
1 Speedy Express (503) 555-9831
2 United Package (503) 555-3199
3 Federal Shipping (503) 555-9931

Right Join Multiple Columns

The below SQL statement fetches data from both tables by joining them together as mentioned below.

  • Returns all the rows from the right table along with the matching data from the left table, based on the join.
  • For a right table row, if the associated left table row is not found, then the resulted row contains NULL values against all the columns coming from the left table.
  • For a right table row, if there is more than one matching row on the left table, then the resulted row contains includes the values from the first matching row against all the columns coming from the left table.

Run this on IDE

SELECT order_id, order_date, order_value, customer_name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

After successful execution, the output contains all rows from the left table along with the required data from the right table, as shown below.

  • For the last right table row, there is no matching record on the left table, so all left table columns are NULL for that specific record.
order_id order_date order_value customer_name
10252 2014-07-18 2822 Maria Anders
10251 2014-07-15 1159 Fran Wilson
10250 2014-07-05 1807.5 3
NULL NULL NULL Martin Blank

Right Join All Columns

The below SQL statement fetches data from both the tables as mentioned below.

  • The SELECT * returns all the columns from both the tables, without repeating the joined columns.

Run this on IDE

SELECT *
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

After successful execution, the output contains data from both the joined tables as shown below.

  • For the last right table row, there is no matching record on the left table, so all left table columns are NULL for that specific record.
order_id order_date order_value supplier_id customer_id customer_name address city country postal_code
10252 2014-07-18 2822 3 1 Maria Anders Obere Str. 57 Berlin Germany 12209
10251 2014-07-15 1159 2 2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
10250 2014-07-05 1807.5 1 3 Dominique Perrier 25, rue Lauriston Paris France 75016
NULL NULL NULL NULL 4 Martin Blank Via Monte Bianco 34 Turin Italy 10100

Tips & Recommendations

An outer join is a join that includes rows in the result set even though there may not be a match between the rows of the joined tables.

In a join query, the left table is the one that appears leftmost in the JOIN clause, and the right table is the one that appears rightmost.

In an outer join, wherever the database system can't find a match record, it places NULL values to indicate that the data do not exist in columns of such table.

Overall

We now know how can we use a SELECT Statement with RIGHT JOIN to combine tables and fetch data from the joined tables.

Related Links