Right Join by Example in SQL Server

The Right Join (Also known as Right Outer join) includes all the results from the Right table and includes only the matching rows from the Left table. It is a mirror image of the Left Join. This article explores the Right join in more detail with examples

Syntax

The syntax is as follows

The join_condition defines the condition on which the tables are joined.

  1. All the rows from the Right table. i.e tableB.
  2. Each row of the tableA is compared with the tableB. If the condition is evaluated to true, then the rows from the tableA are included in the result.
  3. A NULL value is assigned to every column of tableA that does not have a matching row based on the join condition.

The RIGHT OUTER JOIN & RIGHT JOIN are the same. The Keyword OUTER is optional

Right Join

The Right join is best explained with the following Venn diagram.

Right Join Venn Diagram
Right Join Venn Diagram

The circles in the above diagram represent the two tables. Table A and Table B, which we would like to join using the right join. The intersection part in the above picture shows the data rows which satisfy the join condition.

The right join of the above tables returns

  • All the rows from the right table (i.e Table B )
  • The matching rows from the left table (Table A )

Right Join Example

Now, let us see the join using a real-life example

Sample database

Consider the following tables from the table reservation system of a restaurant. You can use the following script to create the database.

CustomerType : Type of Customer like VIP / Regular etc
Customers List of customers with Customer Type.
Tables The list of tables available in the restaurant. CustomerID field indicates that the customer has reserved the table.
Orders : The orders placed by the customer
DiscVoucher The discounts slab based on the total value of the order

--Sample database--

Sample database structure for Join Query example
Sample database structure for Join Query example

--SQL Query--

Query

We would like to know the list of all customers, along with their reserved table No.

Since it is Right Join, the query returns all the Tables. But it returns the only the matching rows from the Customers.

Change the order of the tables appearing in the query i.e. exchanges customers & tables as shown below

The results are different from the above. Now all the rows from tables are included in the result and only matching rows from the customers are included.

WHERE Condition

The WHERE clause will filter the rows after the JOIN has occurred

For Example in the following query, the join happens first. The Where filter is applied to the join result to get the final output. The result of the following query is similar to the inner join query.

Right Join Example Query Result is similar to inner join
Right Join with where clause query Result is similar to inner join

Right Join Examples

Here are some of the examples of Right Join

Subquery in Right Join

Right Join Example Using Subquery
Right Join Example Using Subquery

Multiple Conditions

Join Operator other than equality

References

  1. Join Syntax (SQL Server)
  2. Sample database

Summary

In this tutorial, we learned about how Right join works in SQL Server. And also looked at several examples of right join querries.

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top