Right join in sql server

Introduction to the SQL Server RIGHT JOIN clause

The RIGHT JOIN combines data from two or more tables. The RIGHT JOIN clause starts selecting data from the right table and matching with the rows from the left table. The RIGHT JOIN returns a result set that includes all rows in the right table, whether or not they have matching rows from the left table. If a row in the right table does not have any matching rows from the left table, the column of the left table in the result set will have nulls.

The following shows the syntax of the RIGHT JOIN clause:

1 2 3 4 5

SELECT     select_list FROM     T1 RIGHT JOIN T2 ON join_predicate;

In this syntax, T1 is the left table and T2 is the right table.

Note that RIGHT JOIN and RIGHT OUTER JOIN is the same. The OUTER keyword is optional.

The following Venn diagram illustrates the RIGHT JOIN operation:

SQL Server RIGHT JOIN example

We will use the sales.order_items and production.products table from the sample database for the demonstration.

The following statement returns all order_id from the sales.order_items and product name from the production.products table:

1 2 3 4 5 6 7 8 9

SELECT     product_name,     order_id FROM     sales.order_items o     RIGHT JOIN production.products p         ON o.product_id = p.product_id ORDER BY     order_id;

Here is the output:

The query returned all rows from the production.products table (right table) and rows from sales.order_items table (left table). If a product does not have any sales, the order_id column will have a null.

To get the products that do not have any sales, you add a WHERE clause to the above query to filter out the products that have sales:

1 2 3 4 5 6 7 8 9 10 11

SELECT     product_name,     order_id FROM     sales.order_items o     RIGHT JOIN production.products p         ON o.product_id = p.product_id WHERE     order_id IS NULL ORDER BY     product_name;

The following picture shows the output:

The following Venn diagram illustrates the above RIGHT JOIN operation:

In this tutorial, you have learned how to use the SQL Server RIGHT JOIN to query data from two tables.