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.
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:
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.
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
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.
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
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 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
The results of this FULL OUTER JOIN operation are shown in the following figure.
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!