In this article, you will see how to JOIN two or more tables using the RIGHT OUTER JOIN operator in SQL Server.
The RIGHT OUTER JOIN operator returns all rows from the table on the right-hand side of the operator, while only matching rows are returned from the left table. RIGHT OUTER JOIN is also referred to as RIGHT JOIN.
This article explains the following four approaches for applying RIGHT OUTER JOIN:
- RIGHT OUTER JOIN using SQLCmd utility
- RIGHT OUTER JOIN using SSMS
- RIGHT OUTER JOIN using dBForge Studio SQL Text Query Window
- RIGHT OUTER JOIN using dBForge Studio Query Editor
Creating the dummy database
The following script creates a dummy database that I will use to demonstrate RIGHT OUTER JOIN examples in this article.
The database consists of three tables: “Products,” “Customers,” and “Orders.” The “Orders” table contains “ProductID” and “CustomerID” columns which act as foreign keys for the “Products” and “Customers” tables, respectively. Thereby establishing two one-to-many relationships: between “Products” and “Orders” tables and “Customers” and “Orders” tables.
| Create Database store USE store |
| CREATE TABLE [Orders]
( id INT PRIMARY KEY IDENTITY(1,1), Quantity INT, ProductID INT, CustomerID INT ) CREATE TABLE Products ( id INT PRIMARY KEY , Surname VARCHAR (50) NOT ZERO, Price FLOAT, ) CREATE TABLE customers ( id INT PRIMARY KEY, Surname VARCHAR (50) NOT ZERO, ) |
The following script inserts some dummy data in the “Products”, “Customers”, and “Orders” tables.
| INSERT INTO Products
VALUES (1, ‘tea’5.0), (2, ‘milk’2.5), (3, ‘Chocolate’0.80), (7, ‘cookies’1.20), (8th, ‘banana’1.40 ), (4, ‘apple’3.50), (10,‘Croissant’1.00), (12,bread0.95), (6, ‘coffee’4.00) INSERT INTO customers VALUES (1, ‘Sara’), (2, ‘nick’), (3, ‘Jones’), (10, ‘elis’), (12, ‘Mike’), (15, ‘Andy’) INSERT INTO orders VALUES (5, 1, 2), (3, 2, 2), (8, 3, 2), (10, 3,1), (7, 3,1), (5, 4,1), (8, 5,5), (6, 5,6), (10, 7,8) |
Syntax of RIGHT OUTER JOIN
The syntax of the RIGHT OUTER JOIN involves a SELECT statement that selects records from the tables involved in a JOIN operator, followed by a FROM statement and RIGHT JOIN (or RIGHT OUTER JOIN) statements.
The table on the left-hand side or before the RIGHT OUTER JOIN operator is referred to as the left table, while the table on the right-hand side or after the RIGHT JOIN OPERATOR is called the right table.
The ON operator specifies matching columns between the left and right tables.
| SELECT left_table_name.column_name1,
left_table_name.column_name…N, right_table_name.column_name1, right_table_name.column_name…N, FROM left_table_name RIGHT OUTER JOIN right_table_name ON left_table_name.joining_column = right_table_name.joining_column |
RIGHT OUTER JOIN Using SQLCMD Utility
If you prefer command-line tools for writing SQL scripts, you can use the SQLCmd tool, which is a command-line utility for running SQL Server scripts.
Once you install the SQLCmd tool on Windows, open the “Run” shell and run the following command to connect to your SQL Server instance via the SQLCmd utility. Replace “server_name” in the following script with the name of the SQL Server instance you want to connect to. Also, if you do not use the Windows authentication, you will need to replace “-E” with “-U user_name -P password”.
| “sqlcmd -S server_name -E” |
Running the above command will open the SQLCmd utility that looks like this:
Let’s see a concrete example of the RIGHT OUTER JOIN operator. The script below joins the “Products” and “Orders” tables on the “Id” column of the “Products” table and the “ProductID” column of the “Orders” table.
The script fetches values from the “Name” column of the “Products” table and the “Id” and “Quantity” columns of the “Orders” table.
| SELECT Products.Name like ProductName, Orders.Id like OrderID, Orders.Quantity
FROM Products RIGHT OUTER JOIN orders ON Products.Id = Orders.ProductID |
The “Products” table acts as the left table, and the “Orders” table acts as the right table in the RIGHT OUTER JOIN operation in the above script. You can see all rows from the “Orders” table while only matching rows from the “Products” table. NULL values are added for the non-matching rows in the “Products” table.
If you swap the positions of the “Products” and “Orders” tables, you will see that all rows are returned for the “Products” table while only matching rows will be returned for the “Orders” table.
| SELECT Products.Name like ProductName, Orders.Id like OrderID, Orders.Quantity
FROM orders RIGHT OUTER JOIN Products ON Products.Id = Orders.ProductID |
RIGHT OUTER JOIN using SSMS Query Editor
SQL Server Management Studio (SSMS) is a good option if you are looking for a free IDE for running SQL scripts. Let’s see how to run a RIGHT OUTER JOIN operation in SSMS.
Open SSMS and click the “New Query” option from the top menu of the SSMS dashboard.
A query window will appear where you can enter SQL queries. For example, the following script applies a RIGHT INNER JOIN operation on the “Customers” and “Orders” tables.
| USE store
SELECT Customers.Name like CustomerName, Orders.Id like OrderID, Orders.Quantity FROM customers RIGHT JOIN orders ON Customers.Id = Orders.CustomerID |
In the output, you can see all records from the “Orders” table while only matching records from the “Customers” table.
RIGHT OUTER JOIN Using dbForge Studio for SQL Server
SQLCmd and SSMS are good options for running SQL Server queries. However, if you are looking for a feature-rich, all-purpose SQL Server IDE with IntelliSense and code completion features, I highly recommend dbForge SQL Studio for SQL Server. You can evaluate the dbForge Studio for SQL Server by free downloading a 30-day trial version.
dbForge Studio provides an easy-to-use GUI query editor and a text query window to run your SQL queries. For example, I will explain how to apply the RIGHT OUTER JOIN operation using the text query window and the GUI-based query editor.
Connecting dbForge Studio with SQL Server
Open dbForge Studio and click the “New Connection” button, as shown in the following screenshot.
You will see the following window. Enter the SQL Server instance name you want to connect to and select the authentication method.
Select the database that you want to access. If you do not select a database name, you will see all the databases in the dbForge Studio database explorer.
Click the “Connect” button.
RIGHT OUTER JOIN Using SQL Text Query Window in dbForge Studio
Click the “New SQL” option from the top menu. A text query window will appear where you can write your SQL script.
You will see code suggestions and auto-code completion features while writing your SQL scripts, as shown in the following screenshot. dbForge Studio’s IntelliSense and code completion features help you write your SQL script at least four times faster than any other editor with no IntelliSense and code completion features.
The following script demonstrates a RIGHT OUTER JOIN operation between the “Customers” and “Orders” tables in the dbForge Studio SQL text query window. Click the “Execute” button from the top menu to run the following query.
RIGHT OUTER JOIN Using dbForge Studio Query Editor
dbForge Studio query editor lets you design SQL queries via a GUI, which can be very handy for designing complex SQL queries.
I will demonstrate how to design a RIGHT OUTER JOIN query via the dbForge Studio query editor.
Click the “New Query” option from the top menu.
I will apply a RIGHT OUTER JOIN operation between the “Customers” and “Orders” tables. To do so, drag and drop the tables on the query editor.
At the bottom of the query editor, you have options to design various types of queries. Click the “Joins” option and then click the plus (+) sign to design a new JOIN query, as shown in the following screenshot.
Select the JOIN operation you want to perform (Right Outer Join in our case). Select the tables on the left and right-hand sides of the “Right Outer Join” operator.
Select the columns you want to use for joining your tables from the drop-down list, as shown in the following screenshot.
Once you select matching columns for the “Customers” and “Orders” tables, you will see a link between the two tables, as shown below:
Select the names of the columns you want in the output and click the “Execute” button from the top menu to run the RIGHT OUTER JON query you designed. You will see the following results.
Conclusion
SQL JOIN operations are useful for retrieving data from multiple tables. You can use command line tools like SQLCmd or IDEs such as SSMS to run JOIN queries on SQL Server. However, if you are looking for an advanced IDE with IntelliSense and code completion features, I highly suggest you try the dbForge Studio for SQL Server. Furthermore, the dbForge Studio’s query editor features make it exceptionally easier to design complex SQL queries.
Author Bio:
Ben Richardson runs Acuity Training. Acuity is an IT training business offering classroom courses in London and Guildford. It is a leading provider of SQL training in the UK and offers a full range of SQL training from introductory training to advanced administration courses.
