SQL Server Essentials: Using SQL Joins

In relational databases like SQL Server, the SQL JOIN statement is used to query, connect and retrieve data from multiple tables based on data relationships between those tables. You can use the SQL JOIN statement with two or more tables, and it essentially returns records that have matching values in the different tables. In this tutorial, I will explain how to use the most common SQL JOIN types including the SQL INNER JOIN, the SQL LEFT JOIN, the SQL RIGHT JOIN, and the SQL OUTER JOIN.

SQL Joins are an essential feature to use when working with relational databases. They are mainly executed using the SQL SELECT statement. You can learn more about getting started with the T-SQL SELECT statement in my previous SQL Servers article: Using SQL SELECT and the WHERE and HAVING Clauses to Retrieve Data.

SQL INNER JOIN

The SQL INNER JOIN operation creates a result set by combining rows that have matching values in two or more tables. This is probably the most commonly used join operation in T-SQL. SQL INNER JOIN only returns rows that have matching values, and it’s used to retrieve data that appears in all tables.

The following diagram illustrates how an SQL INNER JOIN operation works with two tables.

The syntax for an INNER JOIN is pretty straightforward. As part of your SELECT statement, you specify the two tables to join and the columns to use to match rows.

Let’s take a closer look at two tables from the AdventureWorksLT2019 sample database. If we use the SalesLT.Customer and SalesLT.SalesOrderHeader tables, we can use an SQL INNER JOIN to retrieve the orders for the customers in the SalesLT.Customer table by joining the two tables on the CustomerID column, which is a common column in both tables.

Here’s the T-SQL query you can use to accomplish that:

USE AdventureWorksLT2019

SELECT c.CustomerID, c.FirstName, c.LastName, soh.SalesOrderID, soh.OrderDate 
FROM SalesLT.Customer c
INNER JOIN SalesLT.SalesOrderHeader soh ON c.CustomerID = soh.CustomerID

Here’s how the syntax for the SQL INNER JOIN works:

• Here, you can see we’re selecting the ‘CustomerID’, ‘FirstName’, and ‘LastName’ columns from the SalesLT.Customer table, and the ‘SalesOrderID’ and ‘OrderDate’ columns from the SalesLT.SalesOrderHeader table.
• The ‘c’ and ‘soh’ are shorthand table aliases that eliminate the need to always use the full table name when referring to columns.
• We’re using the INNER JOIN keyword to join the two tables. The ON keyword specifies the column to use for the join. Here it’s the ‘CustomerID’ column.

This query will return a result set that includes the ‘CustomerID’, ‘FirstName’, ‘LastName’, ‘SalesOrderID’, and ‘OrderDate’ columns for each customer who has placed an order, as you can see in the following figure.

SQL LEFT JOIN

The SQL LEFT OUTER JOIN operation will also create a result set by matching rows between our two previous tables. With the SQL LEFT JOIN, however, if no records match from the left table, it will show those records with null values.

This is useful when you want to include all rows from the first table and only the matching rows from the second table, even if there’s no match in the right table. The SQL LEFT OUTER JOIN will return null values in columns from the right table if there’s no match.

The following diagram illustrates how an SQL LEFT OUTER JOIN operation works with two tables.

The syntax for the LEFT JOIN is also fairly simple. As part of the SELECT statement, you specify the two tables to join, the columns to use to match rows, and the LEFT JOIN clause.

Let’s take a closer look at two tables from the AdventureWorksLT2019 database. For example, to retrieve all customers and any orders they have, you can use the SalesLT.Customer and SalesLT.SalesOrderHeader tables and perform a LEFT JOIN as you can see in the following listing:

USE AdventureWorksLT2019

SELECT c.CustomerID, c.FirstName, c.LastName, soh.SalesOrderID, soh.OrderDate
FROM SalesLT.Customer c 
LEFT JOIN SalesLT.SalesOrderHeader soh ON c.CustomerID = soh.CustomerID

• In this example, we’re selecting the ‘CustomerID’, ‘FirstName’, ‘LastName’, ‘SalesOrderID’, and ‘OrderDate’ columns from the SalesLT.Customer and SalesLT.SalesOrderHeader tables.
• We’re using the LEFT JOIN keyword to join the two tables and the ON keyword that we will be using in the ‘CustomerID’ column to join the two tables.

You can see the results of this SQL LEFT JOIN query in the figure below.

This query will return a result set that includes all customers in the SalesLT.Customer table, along with any corresponding order information from the SalesLT.SalesOrderHeader table. If a customer does not have any orders in the SalesLT.SalesOrderHeader table, then the columns from that table will be null. You can see several null values in the previous figure.

SQL RIGHT JOIN

As you might guess, the SQL RIGHT OUTER JOIN operation is essentially the opposite of the LEFT OUTER JOIN. The RIGHT OUTER JOIN selects data from the right table (Table 2) and matches this data with the rows from the left table (Table 1).

The RIGHT JOIN returns a result set that includes all rows in the right table, even if they do not have matching rows from the left table. If a row in the right table does not have a matching row in the left table, then the result set for the columns from the left table will be null.

The following diagram illustrates how a right JOIN works with two tables:

We can show this with the SalesLT.Customer and SalesLT.SalesOrderHeader from the AdventuresLT2019 database. To retrieve all orders and their corresponding customer information, you can use a RIGHT JOIN query as you can see in the following example:

USE AdventureWorksLT2019

SELECT c.CustomerID, c.FirstName, c.LastName, soh.SalesOrderID, soh.OrderDate
FROM SalesLT.Customer c 
RIGHT JOIN SalesLT.SalesOrderHeader soh ON c.CustomerID = soh.CustomerID

• In this example, we’re selecting the ‘CustomerID’, ‘FirstName’, ‘LastName’, ‘SalesOrderID’, and ‘OrderDate’ columns from the SalesLT.Customer and SalesLT.SalesOrderHeader tables.
• The RIGHT JOIN keyword is used to join the two tables, and the ON keyword specifies that we’re joining the two tables on the ‘CustomerID’ column.

This query will return a result set that includes all orders in the SalesLT.SalesOrderHeader table, along with their corresponding customer information from the SalesLT.Customer table if it’s available. If an order does not have a corresponding customer in the SalesLT.Customer table, the customer columns in the result set will be null.

You might notice this query is very much like the preceding query. However, the LEFT JOIN resulted in many rows from the SaleOrderHeader table that had null values. However, our last query has no null values in the rows from the SaleOrderHeader table.

The results of our SQL RIGHT OUTER JOIN operation are shown in the following figure.

SQL OUTER JOIN

SQL OUTER JOIN is not the most common type of join operation. Sometimes called a FULL JOIN, the OUTER JOIN query will not only retrieve the matching rows but the unmatched rows as well. In other words, it returns data from the joined table when there is a match in either the left or right tables.

As you might guess, this tends to produce larger result sets than the other join types. The following diagram illustrates how an SQL OUTER JOIN works with two tables.

I’ll give you an example of an SQL OUTER JOIN operation using the SalesLT.SalesOrderHeader and SalesLT.SalesOrderDetail tables. Here, we want to retrieve all sales orders and their associated details, including orders that don’t have any details records.

To do this, we can use a FULL OUTER JOIN. This will also return any SalesOrderDetail rows that didn’t have a corresponding SalesOrderHeader row. In most normal cases all SalesOrderDetails rows should have a corresponding SalesOrderHearder row but this might not be the case if there has been an application or system error which this can help detect.

Here’s a T-SQL query showing an example FULL OUTER JOIN:

USE AdventureWorksLT2019

SELECT soh.SalesOrderID, soh.OrderDate, sod.ProductID, sod.OrderQty, sod.UnitPrice
FROM SalesLT.SalesOrderHeader soh
FULL OUTER JOIN SalesLT.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID

• In this example, we’re selecting the ‘SalesOrderID’, ‘OrderDate’, ‘ProductID’, ‘Quantity’, and ‘UnitPrice’ columns from both the SalesLT.SalesOrderHeader and SalesLT.SalesOrderDetail tables.
• We’re using the FULL OUTER JOIN keyword to join the two tables, and the ON keyword to specify that the SalesOrderID column will be used to join the two tables.
• This query will return a result set that includes all sales orders in the SalesLT.SalesOrderHeader table, along with their corresponding detail information from the SalesLT.SalesOrderDetail table. It will also include all details in the SalesLT.SalesOrderDetail table, along with their corresponding order header information if it’s available.
• If a sales order does not have any details in the SalesLT.SalesOrderDetail table, then the columns will be NULL. If a detail does not have a corresponding sales order in the SalesLT.SalesOrderHeader table, then the sales order columns will be NULL.

The results of this FULL OUTER JOIN operation are shown in the following figure.

Learning SQL Join fundamentals

In this tutorial, I covered the most common different types of SQL JOINS. I showed how to use the INNER JOIN, the LEFT JOIN, the RIGHT JOIN, and the OUTER JOIN, and I also explained how they are different and where they can be used. In an upcoming post, I’ll be covering some of the less common joins like the CROSS JOIN and the SELF JOIN, so stay tuned to Petri!

Related Article:

• SQL Server Essentials: SQL Self Joins
• SQL Server Essentials: Using SQL Cross Joins