Learn about SQL RIGHT JOIN with the help of examples. Learn how to use the SQL RIGHT JOIN to combine data from two tables, returning all rows from the right table, even if there are no matching rows in the left table.
The SQL RIGHT JOIN
statement joins two tables based on a common column. It selects records that have matching values in these columns and the remaining rows from the right table.
-- join Customers and Orders tables
-- based on their shared customer_id columns
-- Customers is the left table
-- Orders is the right table
SELECT Customers.customer_id, Customers.first_name, Orders.item
FROM Customers
RIGHT JOIN Orders
ON Customers.customer_id = Orders.customer_id;
Here, the code right joins the Customers
and Orders
tables based on customer_id, which is common to both tables. The result set contains
Orders
table (including those whose customer_id value is not present in the Customers
table)The syntax of the SQL RIGHT JOIN
statement is:
SELECT columns_from_both_tables
FROM table1
RIGHT JOIN table2
ON table1.column1 = table2.column2
Here,
column2
are the related columns in the two tables-- join Customers and Orders tables
-- based on customer_id of Customers and customer of Orders
-- Customers is the left table
-- Orders is the right table
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
RIGHT JOIN Orders
ON Customers.customer_id = Orders.customer;
Here's how this code works:
Example: SQL RIGHT JOIN
Here, the SQL command selects the customer_id
and first_name
columns (from the Customers
table) and the amount
column (from the Orders
table).
And, the result set will contain those rows where there is a match between customer_id
(of the Customers
table) and customer
(of the Orders
table), along with all the remaining rows from the Orders
table.
Note: RIGHT JOIN
is not supported by our online SQL compiler since it's based on SQLite. However, you can get the same results by using a LEFT JOIN and swapping the left and right tables.
-- left join Orders and Customers tables
The SQL RIGHT JOIN
statement can have an optional WHERE clause. For example,
SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
RIGHT JOIN Orders
ON Customers.customer_id = Orders.customer
WHERE Orders.amount >= 500;
Here, the SQL command joins the Customers
and Orders
tables and selects rows where the amount is greater than or equal to 500.
We can use AS aliases inside RIGHT JOIN
to make our SQL code short and clean. For example,
-- use alias C for Categories table
-- use alias P for Products table
SELECT C.category_name, P.product_title
FROM Categories AS C
RIGHT JOIN Products AS P
ON C.cat_id = P.cat_id;
Here, the SQL command performs a right join on the Categories
and Products
tables while assigning the aliases C and P to them, respectively.
#sql