RIGHT JOIN in SQL Server

In this SQL Server tutorial, we will learn and understand how to use the SQL Server RIGHT JOIN clause on both tables by the query. Here are the subtopics that we are going to discuss are given below:

What is the RIGHT JOIN in SQL Server?
What is the difference between RIGHT JOIN and LEFT JOIN in SQL Server?
How to use RIGHT JOIN on multiple tables in SQL Server?
How can we use SQL Server’s RIGHT JOIN with the WHERE clause?
What will happen if RIGHT JOIN without ON condition is used in SQL Server?

Table of Contents

All records from the right table (table_2) and the corresponding records from the left table (table_1) are returned via the SQL Server RIGHT JOIN clause. If there is no match, there are no records from the left side. Here is the image of RIGHT JOIN in SQL Server:

Sql server right join tutorial — Example of SQL Server RIGHT JOIN clause

This means that a right join will return NULL if there are no matching join criteria and all rows from the right table with values matching those in the left table.

SYNTAX:

SELECT SELECTED_COLUMN}
FROM TABLE_1
RIGHT JOIN TABLE_2
ON TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN;

Here are the two tables i.e; STUDENT(LEFT_TABLE) and ADMISSION_DPT (RIGHT_TABLE) which are used for the RIGHT JOIN clause and images are given below:

Sql server right join query — STUDENT table

EXAMPLE:


SELECT STUDENT.FIRST_NAME,
ADMISSION_DPT.COURSE_STARTDATE,
ADMISSION_DPT.COURSE_ENDDATE
FROM STUDENT
RIGHT JOIN ADMISSION_DPT
ON STUDENT.ADMISSION_NUMBER=ADMISSION_DPT.ADMISSION_NUMBER;

With the help of the RIGHT JOIN clause, the SELECT statement retrieves all records from the ADMISSION_DPT (right_table) and with matching records from the STUDENT (left_table).

Sql server right join query example — Example of SQL Server RIGHT JOIN clause

We hope that you have understood how to use RIGHT JOIN in SQL Server.

Read: SQL Server Outer Join With Count

SQL Server Right Join Vs Left Join

We have explained the RIGHT JOIN in SQL Server in the above subtopic “SQL Server RIGHT JOIN”. The SQL Server LEFT JOIN is used to return all records from the LEFT TABLE with matching records from the RIGHT TABLE.

EXAMPLE OF LEFT JOIN:


SELECT STUDENT.FIRST_NAME,
ADMISSION_DPT.COURSE_STARTDATE,
ADMISSION_DPT.COURSE_ENDDATE
FROM STUDENT
LEFT JOIN ADMISSION_DPT
ON STUDENT.ADMISSION_NUMBER=ADMISSION_DPT.ADMISSION_NUMBER;

With the help of the LEFT JOIN clause, the SELECT statement will retrieve all records of the selected_column from the ADMISSION_DPT (left_table ) with matching records of the selected column from the STUDENT ( right_table ).

Sql server right join vs left join — Example of SQL Server LEFT JOIN clause

We hope that you have understood the difference between RIGHT JOIN and LEFT JOIN in SQL Server.

Read: SQL Server Row_Number Join

SQL Server Right Join Multiple Tables

Using RIGHT JOIN or any JOIN types doesn’t mean that we can join only two tables, We can join 3 or 4 tables by JOIN types. The possibilities are limitless.

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT TOP 10 CUSTOMER.CUSTOMER_FIRSTNAME,CUSTOMER.CUSTOMER_LASTNAME,
ORDERS.ORDER_NUMBER,ADMINISTRATOR.FULL_NAME
FROM CUSTOMER
RIGHT JOIN ORDERS
ON CUSTOMER.CUSTOMER__ID=ORDERS.ORDER__ID
RIGHT JOIN ADMINISTRATOR
ON ORDERS.ORDER__ID=ADMINISTRATOR.ADMIN_ID;

The TOP 10 is initially used to deliver the first 10 records from these tables to the result that the SELECT statement has specified. Although we have added a third table (ADMINISTRATOR) and used the ON condition of the RIGHT JOIN clause for the result set, the mechanism of the RIGHT JOIN is the same as for the CUSTOMER and ORDERS tables.

Sql server right join multiple tables example — Example of SQL Server RIGHT JOIN clause on multiple tables

We trust that after seeing an example, you now fully understand how to use SQL Server’s RIGHT JOIN clause on several tables.

Read: If Else In Trigger SQL Server

SQL Server Right Join With Where Clause

As we are aware, SQL Server uses the WHERE clause. The SELECT statement filters the table’s records according to the definition of the WHERE clause. Additionally, it is utilized with the DELETE and UPDATE statements. It indicates that only records that meet its precise criteria will be filtered.

EXAMPLE:


SELECT STUDENT.FIRST_NAME,
ADMISSION_DPT.*
FROM STUDENT
RIGHT JOIN ADMISSION_DPT
ON STUDENT.ADMISSION_NUMBER=ADMISSION_DPT.ADMISSION_NUMBER
WHERE STUDENT.CITY != 'California';

With the help of the RIGHT JOIN clause,

Sql server right join with where clause example — Example of RIGHT JOIN with WHERE clause in SQL Server

We hope that you have understood how to use RIGHT JOIN with WHERE condition in SQL Server.

Read: How to Debug an SQL Server Trigger

SQL Server Right Outer Join Without On

As the subtopic says that if we try to use the RIGHT OUTER JOIN without the ON condition then it will throw an error. Because by using the ON condition only the common column of both tables can be connected by the EQUAL TO operator for the result set.

Here is the example of RIGHT OUTER JOIN in SQL Server without ON condition:

EXAMPLE:

USE SQLSERVERGUIDES;

SELECT HARVARD_UNIVERSITY.STUDENT_FIRSTNAME,
TEACHER_OF_HARVARDUNIVERSITY.TEACHER_NAME
FROM HARVARD_UNIVERSITY
RIGHT OUTER JOIN TEACHER_OF_HARVARDUNIVERSITY;

Due to the ON condition not being applied while joining the matching result set for both tables in the result set, this RIGHT OUTER JOIN query generates a syntax error.

Sql server right outer join without on example — Example of SQL Server RIGHT OUTER JOIN without ON condition

Example of RIGHT OUTER JOIN with ON condition:

USE SQLSERVERGUIDES;

SELECT HARVARD_UNIVERSITY.STUDENT_FIRSTNAME,
TEACHER_OF_HARVARDUNIVERSITY.TEACHER_NAME
FROM HARVARD_UNIVERSITY
RIGHT OUTER JOIN TEACHER_OF_HARVARDUNIVERSITY 
ON HARVARD_UNIVERSITY.STUDENT_ID=TEACHER_OF_HARVARDUNIVERSITY.TEACHER_ID;

This query provides the result set once the ON condition is used for RIGHT OUTER JOIN in both tables.

Sql server right outer join without on tutorial — Example of SQL Server RIGHT OUTER JOIN with ON condition

We hope that you have understood RIGHT OUTER JOIN without ON condition can’t be used in SQL Server. You have to always use RIGHT JOIN with ON condition and for more understanding, we have used two different examples.

Also, take a look at some more SQL Server tutorials.

We now know about the post “RIGHT JOIN in SQL Server” after reading this tutorial. Here is a list of each subtopic we covered to help you understand the concept.

What is RIGHT JOIN in SQL Server?
What is the difference between RIGHT JOIN and LEFT JOIN in SQL Server?
How to use RIGHT JOIN on multiple tables in SQL Server?
How can we use SQL Server’s RIGHT JOIN with the WHERE clause?
What will happen if RIGHT JOIN without ON condition is used in SQL Server?

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.