Right (Outer) Join in SQL

We use the RIGHT OUTER JOIN when we want to join the records from tables, return all rows from a table, and then show the other columns of the tables if there’s a match. In any other case, it will return NULL values.

In this article, we will take a look at the Right (Outer) Join in SQL according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.

Table of Contents

What is Right (Outer) Join in SQL?
- - Syntax
- Example
Practice Questions on Right (Outer) Join in SQL
FAQs

What is Right (Outer) Join in SQL?

The right join in SQL helps us in returning all the rows available in the right table, even when no matches are present there in the left table. It means that in case the ON clause would match zero records present in the left table, the join would return a row in its result. The only thing is that there will be NULL present in every column from the left table.

Thus, it would mean that the right or outer join would return the values present in the right table along with the matched values present in the left table. In case of zero matching join predicate, the returned value would be NULL.

Syntax

Here is the basic syntax of a typical RIGHT JOIN:

SELECT table_a.column_a, table2.column_b

FROM table_a

RIGHT JOIN table_b

ON table_a.common_field = table_b.common_field;

Example

Let us now consider these two tables given below:

Table 1 − CLIENTS Table is as follows:

ID	NAME_TAG	AGE	CITY	WAGE
1	Rex	32	Ahmedabad	2000.00
2	Komal	25	Delhi	1500.00
3	Kurt	23	Kota	2000.00
4	Cassie	25	Mumbai	6500.00
5	Herald	27	Bhopal	8500.00
6	Kim	22	MP	4500.00
7	Maddy	24	Indore	1000.00

Table 2 − ORDERS Table is as follows:

OID	DATE	CLIENT_ID	BILL
102	2019-10-18 00:00:00	3	3000
100	2019-10-18 00:00:00	3	1500
101	2019-11-21 00:00:00	2	1560
103	2018-05-21 00:00:00	4	2060

Now, we will join these two tables here using the RIGHT JOIN like this:

SQL> SELECT ID, NAMETAG, BILL, DATE

FROM CLIENTS

RIGHT JOIN ORDERS

ON CLIENTS.ID = ORDERS.CLIENT_ID;

Thus, it would ultimately produce the result as follows:

ID	NAME	BILL	DATE
3	Kurt	3000	2019-10-18 00:00:00
3	Kurt	1500	2019-10-18 00:00:00
2	Komal	1560	101 \| 2019-11-21 00:00:00 \| 2
4	Cassie	2060	2018-05-21 00:00:00

Practice Questions on Right (Outer) Join in SQL

1. Which of these joins refers to joining records from the writing table that consists of no matching key present in the left table and is included in the result set?

A. Right outer join

B. Left outer join

C. Full outer join

D. None

Answer: A. Right outer join

2. Which of these statements is False?

A. CROSS JOIN is the same as FULL OUTER JOIN

B. SELF JOIN is the OUTER JOIN’s special type

C. LEFT OUTER JOIN is equivalent to RIGHT OUTER JOIN if the order of tables are reversed

D. Both A and B

Answer: D. Both A and B

FAQs

What is a right outer join in SQL?

A right outer join refers to a method that is used to combine tables. The result here includes unmatched rows from the table specified after the clause of RIGHT OUTER JOIN. In case you are trying to join two tables, and you want the result set inclusive of rows unmatched from only one table, then you can use either a clause of a LEFT OUTER JOIN or a RIGHT OUTER JOIN.

Why do we use the right outer join in SQL?

What is the difference between left and right outer join?

The primary difference between the Left and Right Join exists in the inclusion of the non-matched rows. On the one hand, the left join would include the unmatched rows from that table that is present on the join clause’s left. The Right outer join, on the other hand, would do the same on the join clause’s right.

Is the left join faster than the inner join?

A LEFT JOIN is not at all faster than an INNER JOIN. It’s actually slower. By definition, the outer join (LEFT or RIGHT) has to do everything that the INNER JOIN does, plus the extra task of null, thus extending the results.

Keep learning and stay tuned to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2023, GATE Admit Card, GATE Syllabus, GATE Previous Year Question Paper, and more.

Also Explore,