Download the BYJU'S Exam Prep App for free GATE/ESE preparation videos & tests - Download the BYJU'S Exam Prep App for free GATE/ESE preparation videos & tests -

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?

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

Q1

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.

Q2

Why do we use the 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.

Q3

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.

Q4

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,

Comments

Leave a Comment

Your Mobile number and Email id will not be published.

*

*