Author: Ben Richardson

This article explains how you can retrieve data from two or more tables using the FULL OUTER JOIN operator in SQL Server.
JOIN operators allow you to retrieve data from multiple tables in a relational database. Following are the four major types of SQL Server JOIN operators:

INNER JOIN - retrieves matching rows from all the tables involved in a JOIN operation.

1. FULL OUTER JOIN - returns all rows from both tables. NULL values are added for the non-matching rows.
2. LEFT OUTER JOIN - selects all rows from the left table and only matching rows from the right table. NULL values are returned for the table on the right side if a match with the left table is not found.
3. RIGHT OUTER JOIN - is the inverse of the LEFT OUTER JOIN and fetches all rows from the table on the right side of the JOIN operation while selecting only matching rows from the left table. RIGHT OUTER JOIN is also referred to as RIGHT JOIN.

In this article, you will see the following four methods of applying the FULL OUTER JOIN operator in SQL Server:

● FULL OUTER JOIN using SQLCmd utility
● FULL OUTER JOIN using SSMS
● FULL OUTER JOIN using dbForge Studio SQL Text Query Window
● FULL OUTER JOIN using dbForge Studio Query Editor

Creating the Dummy Database

The following script creates the sample database for this article. The database contains three tables: “Products,” “Orders,” and “Customers.” The “Products” and “Customers” tables have a one-to-many relation with the “Orders” table.

Create Database Store

USE Store

CREATE TABLE Orders

CREATE TABLE Products
(
Id INT PRIMARY KEY ,
Name VARCHAR (50) NOT NULL,
Price FLOAT,
)

CREATE TABLE Customers
(
Id INT PRIMARY KEY,
Name VARCHAR (50) NOT NULL,
)

INSERT INTO Products
VALUES (1, 'Tea', 5.0),
(2, 'Milk', 2.5),
(3, 'Chocolate', 0.80),
(7, 'Cookies', 1.20),
(8, 'Banana', 1.40 ),
(4, 'Apple', 3.50),
(10,'Croissant', 1.00),
(12,'Bread', 0.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 FULL OUTER JOIN

To SELECT data using a FULL OUTER JOIN, write the SELECT statement followed by a FROM statement and the FULL OUTER JOIN operator. Write the names of the tables to join before and after the FULL OUTER JOIN operator. Finally, the ON operator specifies the columns for matching records between the two 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
FULL OUTER JOIN right_table_name
ON left_table_name.joining_column = right_table_name.joining_column

FULL OUTER JOIN Using SQLCMD Utility

SQLCmd is a free utility that allows you to access SQL Server databases via a command-line interface.

You can implement all JOIN operators, including the FULL OUTER JOIN using the SQLCmd utility. Let’s see an example.
Download the SQLCmd tool, open the “Run” shell and execute the following command. You need to replace “server_name” with your SQL Server instance name. Also, you need to replace “-E” with “-U user_name -P password”, for the SQL Server authentication instead of Windows authentication.

“sqlcmd -S server_name -E”

The default interface of the SQLCmd utility looks like this:

The following script applies a FULL OUTER JOIN operator between the “Products” and the “Orders” table. The tables are joined on the “Id” column of the “Products” table and the “ProductID” column of the “Orders” table.

USE Store

SELECT Products.Name as ProductName,
Products.Price as ProductPrice,
Orders.ID as OrderID,
Orders.Quantity
FROM Products
FULL OUTER JOIN Orders
ON Products.Id = Orders.ProductID

In the output below, you can see all the rows from the “Products” and the “Orders” tables. NULL values are added for the rows where no match is found between the two tables.

Let’s see another example of a FULL OUTER JOIN. The following script applies a FULL JOIN operator between the “Customers” and “Orders” tables.

SELECT Customers.Name as CustomerName,
Orders.Id as OrderID,
Orders.Quantity
FROM Customers
FULL OUTER JOIN Orders
ON Customers.Id = Orders.CustomerID

You can also use the FULL JOIN keyword to apply a FULL OUTER JOIN operator between two tables. Here is an example:

USE Store
SELECT Customers.Name as CustomerName,
Orders.Id as OrderID,
Orders.Quantity
FROM Customers
FULL JOIN Orders
ON Customers.Id = Orders.CustomerID

FULL OUTER JOIN using SSMS Query Editor

If you prefer GUIs over command line interfaces, you may need to check SQL Server Management Studio (SSMS), a free utility that lets you run SQL scripts in an IDE.

To run a query in SSMS, click the “New Query” option from the top menu. You will see a new text query window, as shown in the following screenshot.

To run a query, enter your text query in the above SQL query window and click the “Execute” button (just below the “New Query” and next to the green triangle) from the top menu.
As an example, I will run a FULL OUTER JOIN query to fetch records from the “Products” and “Orders” tables, as shown in the following screenshot.

FULL OUTER JOIN Using dbForge Studio for SQL Server

SQLCmd and SSMS are useful utilities for running SQL scripts. However, if you are looking for advanced features such as IntelliSense, auto code completion, and GUI-based query designers, dbForge SQL Studio for SQL Server is an excellent choice.

Luckily enough, they have a trial version which you can download for free. You can evaluate the product for a month and see if it meets your requirements before purchasing a paid version.

Connecting dbForge Studio with SQL Server

Running the dbForge Studio application displays the following window. To create a connection with an SQL Server instance, click the “New Connection” button.

In the following window, select the SQL Server instance you want to connect to, along with the authentication mode. You can also select the database that you want to access. If you do not specify a database name, all the databases on the SQL Server will be accessible.

Finally, click the “Connect” button to create a connection.

You have two options to run a SQL query using dbForge Studio:

Using the Text query window
Using the GUI-based query editor

RIGHT OUTER JOIN Using SQL Text Query Window in dbForge Studio

Click the “New SQL” option from the top menu of the dbForge Studio dashboard. A new text query window will open. As you write your queries, you will see IntelliSense and code completion features, as shown in the following screenshot. On average, the IntelliSense and code completion features of dbForge Studio help you write your SQL script four times faster than any other IDE.

The following screenshot shows how to write a FULL OUTER JOIN query with the dbForge Studio text query editor. Click the “Execute” button from the top menu to run the following query.

RIGHT OUTER JOIN Using dbForge Studio Query Editor

dbForge Studio’s query editor is a convenient feature that lets you design complex SQL queries using a GUI.

Let’s see how to implement a FULL OUTER JOIN using the dbForge Studio Query Editor.

Click the “New Query” option from the top menu.

Drag the tables you want to join from the database explorer and drop them in the query editor window. For example, I will apply a FULL OUTER JOIN between the “Products” and “Orders” tables.

You can see various query types at the bottom of the query editor. Select “Joins” and click the green plus (+) button to create a new JOIN query.

Select the type of JOIN operation you want to perform (Full Outer Join in our case). Select the tables on the left and right-hand sides of the “Full Outer Join”. Look at the following screenshot for reference.

Select the matching columns from both tables.

Once you select matching columns from both tables, you will see a link between the two tables. You can select columns you want to display in the output by ticking the corresponding checkboxes.

Finally, click the “Execute” button from the top menu to run your newly designed query.

Conclusion

FULL OUTER JOIN operation is handy for retrieving matching and non-matching rows from two or more tables in a relational database. Several utilities, e.g., SQLCmd and SSMS, allow you to implement FULL OUTER JOIN queries on SQL Server. However, I recommend using dbForge Studio for SQL Server owing to its excellent features such as IntelliSense, code completion, and GUI-based query editor.

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 the UK and offers a full range of SQL training from introductory training to advanced administration courses.