The Right Join is used to joins two or more tables and returns all rows from the right-hand table, and only those results from the other table that fulfilled the join condition. If it finds unmatched records from the left side table, it returns Null value. It is similar to the Left Join, except it gives the reverse result of the join tables. It is also known as Right Outer Join. So, Outer is the optional clause used with the Right Join.
We can understand it with the following visual representation where Right Outer Join returns all records from the left-hand table and only the matching records from the other table:
The following are the syntax of Right Join that joins tables Table1 and Table2:
SELECT column_list FROM Table1 RIGHT [OUTER] JOIN Table2 ON join_condition;
Let us see how Right Join works.
This Join starts selecting the columns from the right-hand table and matches each record of this table form the left table. If both records fulfill the given join condition, it combines all columns in a new row set that will be returned as output. If the rows of the right-side table do not find any matching rows form the left table, it combines those rows from the right-side table with Null values. It means, the Right Join returns all data from the right-side table weather it matches the rows form the left table or not.
Let us take some examples to understand the working of Right Join clause:
Here, we are going to create two tables "customers" and "orders" that contains the following data:
To select records from both tables using RIGHT JOIN, execute the following query:
SELECT customers.customer_id, cust_name, price, date FROM customers RIGHT JOIN orders ON customers.customer_id = orders.customer_id ORDER BY customer_id;
SELECT customers.customer_id, cust_name, price, date FROM customers RIGHT JOIN orders USING(customer_id) ORDER BY customer_id;
After successful execution of the above queries, it will give the equivalent output:
SELECT * FROM customers RIGHT JOIN orders USING(customer_id) WHERE price>2500 AND price<5000;
This statement gives the below result:
We have already created two tables, named "customers" and "orders". Let us create one more table and name it as a "contacts" that contains the following data:
Execute the following statement to join the three table customers, orders, and contacts:
SELECT customers.customer_id, cust_name, order_id, price, cellphone FROM customers RIGHT JOIN contacts ON customer_id = contact_id RIGHT JOIN orders ON customers.customer_id = orders.customer_id ORDER BY order_id;
After successful execution of the above query, it will give the following output:
The Right Join clause is also useful in such a case when we want to get records in the table that does not contain any matching rows of data from another table.
We can understand it with the following example that uses the RIGHT JOIN clause to find a customer who has no cellphone number:
SELECT customer_id, cust_name, cellphone, homephone FROM customers RIGHT JOIN contacts ON customer_id = contact_id WHERE cellphone IS NULL ORDER BY cellphone;
The above statement returns the following output: