Get started Bring yourself up to speed with our introductory content.

How to create a SQL inner join and outer join: Basics to get started

When you're querying a SQL Server database and want to pull related data from more than one table, you'll need to create a join, and here's how.

SQL Server expert Robert Sheldon explains the two primary types of SQL joins – SQL inner join and SQL outer join...

– and provides examples on how to use them for a customized result set.

When querying a SQL Server database, it is often necessary to pull data from more than one table in order to retrieve related information from those tables. For example, your database might include a table that stores information about books and another table that stores information about authors, with the tables linked together through a foreign key relationship. This might mean, for instance, that the book table includes an author ID column configured as a foreign key that references the author ID column in the authors table. To retrieve book data and its related author data, you can join the tables by taking advantage of the underlying foreign key relationship.

A SQL Server join is essentially a way to logically connect two result sets based on the columns that participate in the foreign key relationship -- either a defined relationship or an implied relationship. Returning to the books and 

authors example, you can join the tables based on the author ID values in those tables so each book title is returned with the book's author. For now, we'll assume that each book is written by only one author, although in reality the issue of multiple authors would have to be taken into account.

In this article, I focus on two primary types of joins: inner and outer. Although there are other types, these two are the ones you'll probably use the most. You can find information about the other types in the "Join Fundamentals" topic in SQL Server Books Online.

To demonstrate inner and outer joins in this article, I created the SalesPeople and SalesTerritories tables in the AdventureWorks database by running the following Transact-SQL script:

IF EXISTS (SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'Sales'
AND table_name = 'SalesPeople')
DROP TABLE Sales.SalesPeople
GO
SELECT TOP 15 SalesPersonID AS SalesID,
TerritoryID, SalesYTD
INTO Sales.SalesPeople
FROM Sales.SalesPerson
GO
ALTER TABLE Sales.SalesPeople
ADD CONSTRAINT FK_SalesPeople_Employee_SalesPersonID FOREIGN KEY (SalesID)
REFERENCES HumanResources.Employee (EmployeeID) ;
GO
IF EXISTS (SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'Sales'
AND table_name = 'SalesTerritories')
DROP TABLE Sales.SalesTerritories GO
SELECT TerritoryID, Name AS Region
INTO Sales.SalesTerritories
FROM Sales.SalesTerritory
ORDER BY TerritoryID
GO
ALTER TABLE Sales.SalesTerritories
ADD CONSTRAINT PK_SalesTerritories_TerritoryID PRIMARY KEY CLUSTERED
(TerritoryID)
GO
ALTER TABLE Sales.SalesPeople
ADD CONSTRAINT FK_SalesPeople_SalesTerritories_TerritoryID FOREIGN KEY
(TerritoryID)
REFERENCES Sales.SalesTerritories (TerritoryID)
GO

The SalesPeople table is based on the SalesPerson table in AdventureWorks, and the SalesTerritories table is based on the SalesTerritory table. I took this approach in order to make it simpler to demonstrate each type of join. If you decide not to create these tables, be sure to change the table names in the examples as appropriate. Also, the article assumes that you have a basic understanding of foreign key relationships. If you're unfamiliar with foreign keys, see the "Foreign Key Constraints" topic in SQL Server Books Online.

Creating SQL inner joins

To understand how a join works (inner or outer), it helps to visualize the tables from which you'll be extracting data and the relationships between the tables. Figure 1 shows the SalesPeople and SalesTerritories tables that I created in the AdventureWorks database.

Example of tables for using SQL inner and outer join
Figure 1: SalesPeople and SalesTerritories tables.

As you can see, these are very simple tables. The TerritoryID column in the SalesPeople table is a foreign key that references the TerritoryID column in the SalesTerritories table. As a result, any salesperson can be associated with a sales territory through the foreign key.

Now, suppose you want to retrieve the sales ID, year-to-date sales and region for each salesperson. The way to achieve this is to join the two tables based on the TerritoryID values in both tables. To join the tables, you must specify an INNER JOIN subclause and an ON subclause within the FROM clause of a SELECT statement, as shown in the following example:

SELECT sp.SalesID, sp.SalesYTD, st.Region
FROM Sales.SalesPeople sp
INNER JOIN Sales.SalesTerritories st
ON sp.TerritoryID = st.TerritoryID

Let's first take a look at the FROM clause before we discuss the SELECT list. Notice that the first table specified in the FROM clause is Sales.SalesPeople. The table name is followed by sp, which is a table alias. Table aliases are handy when joining tables because they make the references to those tables much simpler.

The next part of the FROM clause is the INNER JOIN subclause, followed by the table Sales.SalesTerritories and its st alias. The subclause indicates that the SalesPeople table is being joined to the SalesTerritories table.

After you specify that a join is being created, you must then specify how the tables will be joined. That's where the ON subclause comes in. The clause specifies which column in the first table must match a column in the second table. In this case, the ON subclause indicates that the two tables will be joined on the TerritoryID column in each table (sp.TerritoryID = st.TerritoryID). In other words, the TerritoryID value in SalesPeople must match the TerritoryID value in SalesTerritory for the row to be returned.

Notice how the table aliases are used to represent the name of the table. For example, sp.TerritoryID is used rather than Sales.SalesPeople.TerritoryID. You can see why aliases help to simplify the code. However, you have to specify the table name or alias only if SQL Server cannot distinguish between columns in different tables, such as the TerritoryID column. Without identifying the table, the database engine would not know how to process the query. Yet even in circumstances when the table name or alias is not required, it's generally considered good practice to include it to make reviewing the code easier. If you refer back to the statement's SELECT list, you'll see that each column is prefaced with a table alias. You can also see that you can retrieve data from either table in the join.

When you create an inner join, you are matching rows in one table with rows in another table by matching up the foreign key values. As a result, the returned data includes a row for each salesperson associated with a sales territory, as shown in the following result set:

SalesID SalesYTD Region
275 4557045.0459 Northeast
276 5200475.2313 Southwest
277 3857163.6332 Central
278 1764938.9859 Canada
279 2811012.7151 Southeast
280 0.00 Northwest
281 3018725.4858 Southwest
282 3189356.2465 Canada
283 3587378.4257 Northwest
285 5015682.3752 United Kingdom
286 3827950.238 France
287 1931620.1835 Northwest
     

Notice that for each sales ID and sales amount, there is an associated region. If a salesperson is not associated with a sales region, no row is returned for that person.

You can also include a WHERE clause in your statement as you would any other SELECT statement:

SELECT sp.SalesID, sp.SalesYTD, st.Region
FROM Sales.SalesPeople sp
INNER JOIN Sales.SalesTerritories st
ON sp.TerritoryID = st.TerritoryID
WHERE st.Region = 'Canada'

Now you will receive only the following subset of the data when you run the statement:

SalesID SalesYTD Region
278 1764938.9859 Canada
282 3189356.2465 Canada

In many databases, it's often necessary to query more than two tables to retrieve the necessary related data. For example, in Figure 2, the SalesPeople table also references the Employee table through a foreign key defined on the SalesID column.

Using a SQL inner join, one table can reference another table through a foreign key defined in a column
Figure 2: SalesPeople, SalesTerritories and Employee tables.

As Figure 2 shows, you can retrieve additional information about a salesperson by joining the SalesPeople table to the Employee table. The foreign key on the SalesID column in the SalesPeople table references the EmployeeID column in the Employee table. For example, suppose you want to include the login ID for each employee. To do so, you must add another set of INNER JOIN and ON subclauses to the FROM clause, as shown in the following SELECT statement:

SELECT sp.SalesID, RIGHT(e.LoginID,(LEN(e.LoginID)-16)) AS AcctLogin,
sp.SalesYTD, st.Region
FROM HumanResources.Employee e
INNER JOIN Sales.SalesPeople sp
ON e.EmployeeID = sp.SalesID
INNER JOIN Sales.SalesTerritories st
ON sp.TerritoryID = st.TerritoryID

The first table is now HumanResources.Employee and it is joined to the SalesPeople table based on the EmployeeID and SalesID columns. Your returned data can now include columns from all three tables, as shown in the following results:

SalesID AcctLogin SalesYTD Region
275 michael9 4557045.0459 Northeast
276 linda3 5200475.2313 Southwest
277 jillian0 3857163.6332 Central
278 garrett1 1764938.9859 Canada
279 tsvi0 2811012.7151 Southeast
280 pamela0 0.00 Northwest
281 shu0 3018725.4858 Southwest
282 josé1 3189356.2465 Canada
283 david8 3587378.4257 Northwest
285 jae0 5015682.3752 United Kingdom
286 ranjit0 3827950.238 France
287 tete0 1931620.1835 Northwest

Each row now includes the salesperson ID from the SalesPeople table, the account login from the Employee table, the sales amount from the SalesPeople table and the region from the SalesTerritories table.

You can also join tables together without pulling data from a participating table. For example, in Figure 3, the Employee table references the contact table. A foreign key is defined on the ContactID column of the Employee table. The foreign key references the ContactID column in the Contact table.

Use SQL join to connect tables without pulling data from a participating table
Figure 3: SalesPeople, SalesTerritories, Employee, and Contact tables.

Now your query can reference any of the columns in the four tables by joining the tables together, as shown in the following SELECT statement:

SELECT sp.SalesID, c.FirstName, c.LastName,
sp.SalesYTD, st.Region
FROM Person.Contact c
INNER JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID
INNER JOIN Sales.SalesPeople sp
ON e.EmployeeID = sp.SalesID
INNER JOIN Sales.SalesTerritories st
ON sp.TerritoryID = st.TerritoryID

The FROM clause includes another set of INNER JOIN and ON subclauses that provide the link to the Contact table. As a result, the SELECT list includes columns from the Contact, SalesPeople and SalesTerritories tables. Notice, however, the list does not include columns from the Employee table, even though that is one of the joined tables. In fact, the only way to join and reference the Contact table (from the SalesPeople table) is by including the Employee table. The statement now returns the following results:

SalesID FirstName LastName SalesYTD Region
275 Michael Blythe 4557045.0459 Northeast
276 Linda Mitchell 5200475.2313 Southwest
277 Jillian Carson 3857163.6332 Central
278 Garrett Vargas 1764938.9859 Canada
279 Tsvi Reiter 2811012.7151 Southeast
280 Pamela Ansman-Wolfe 0.00 Northwest
281 Shu Ito 3018725.4858 Southwest
282 José Saraiva 3189356.2465 Canada
283 David Campbell 3587378.4257 Northwest
285 Jae Pak 5015682.3752 United Kingdom
286 Ranjit Varkey Chudukatil 3827950.238 France
287 Tete Mensa-Annan 1931620.1835 Northwest

By joining together the four tables shown in Figure 3, your statement can include exactly the type of data you want in your result set. That way, you can retrieve such information as the salesperson's first and last names, rather than just a number.

Creating SQL outer joins

As noted above, an inner join returns only those rows that are related to each other. That means, the result set will not include salespeople who are not associated with a sales region or sales regions not associated with a salesperson. To include that type of data, you must use an outer join.

SQL Server supports three types of outer joins: left, right and full. Let's first take a look at the left outer join.

A left outer join returns all specified rows in the left table, that is, the table referenced to the left of the LEFT OUTER JOIN keywords. For example, if you want to include all salespeople in your results, whether or not they're associated with a sales territory, you would use the following SELECT statement:

SELECT sp.SalesID, sp.SalesYTD, st.Region
FROM Sales.SalesPeople sp
LEFT OUTER JOIN Sales.SalesTerritories st
ON sp.TerritoryID = st.TerritoryID

The FROM clause includes a LEFT OUTER JOIN subclause, along with the ON subclause. The SalesPeople table is joined to the SalesTerritories table, as you saw in earlier examples. However, all salespeople are now included in the result set:

SalesID SalesYTD Region
268 677558.4653 NULL
275 4557045.0459 Northeast
276 5200475.2313 Southwest
277 3857163.6332 Central
278 1764938.9859 Canada
279 2811012.7151 Southeast
280 0.00 Northwest
281 3018725.4858 Southwest
282 3189356.2465 Canada
283 3587378.4257 Northwest
284 636440.251 NULL
285 5015682.3752 United Kingdom
286 3827950.238 France
287 1931620.1835 Northwest
288 219088.8836 NULL

When you create an outer join, unmatched values are returned as nulls. For example, the first salesperson in the result set (SalesID = 268) is not associated with a sales territory, so a null value is returned in the Region column.

A right outer join is exactly the opposite of a left outer join. For example, you can recast the last example into the following SELECT statement:

SELECT sp.SalesID, sp.SalesYTD, st.Region
FROM Sales.SalesPeople sp
RIGHT OUTER JOIN Sales.SalesTerritories st
ON sp.TerritoryID = st.TerritoryID

Now the RIGHT OUTER JOIN keywords are used instead of LEFT INNER JOIN. As a result, all the rows will be returned from the table on the right of the keywords, the SalesTerritories table, even if there is no match to a salesperson, as shown in the following results:

SalesID SalesYTD Region
280 0.00 Northwest
283 3587378.4257 Northwest
287 1931620.1835 Northwest
275 4557045.0459 Northwest
277 3857163.6332 Central
276 5200475.2313 Southwest
281 3018725.4858 Southwest
279 2811012.7151 Southeast
278 1764938.9859 Canada
282 3189356.2465 Canada
286 3827950.238 France
NULL NULL Germany
NULL NULL Australia
285 5015682.3752 United Kingdom

As you can see, both Germany and Australia do not have salespeople associated with them, so null values are returned for the SalesID and SalesYTD columns.

In some cases, you might want to return all the data in both columns, regardless of whether there are any associated rows. In such cases, you would create a full outer join, as shown in the following SELECT statement:

SELECT sp.SalesID, sp.SalesYTD, st.Region
FROM Sales.SalesPeople sp
FULL OUTER JOIN Sales.SalesTerritories st
ON sp.TerritoryID = st.TerritoryID

Now the FULL OUTER JOIN keywords are used. As a result, all salespeople will be included whether or not they're associated with a sales territory, and all sales territories will be included, whether or not they're associated with salespeople, as shown in the following results:

SalesID SalesYTD Region
268 677558.4653 NULL
275 4557045.0459 Northeast
276 5200475.2313 Southwest
277 3857163.6332 Central
278 1764938.9859 Canada
279 2811012.7151 Southeast
280 0.00 Northwest
281 3018725.4858 Southwest
282 3189356.2465 Canada
283 3587378.4257 Northwest
284 636440.251 NULL
285 5015682.3752 United Kingdom
286 3827950.238 France
287 1931620.1835 Northwest
288 219088.8836 NULL
NULL NULL Germany
NULL NULL Australia

Once again, null values are used whenever a row is returned from one table that is unrelated to the data in the other table. That means salespeople 268 and 284 are included, along with the regions Germany and Australia.

Creating both inner and outer joins can be fairly straightforward; however, the joins shown here are relatively simple compared to how complex some join conditions can become. In addition, there are other ways to construct joins and ways you can combine inner and outer joins in a single statement. There are also other types of joins, such as cross joins and self joins. This article at least gives you a foundation for understanding the basics of creating joins.

ABOUT THE AUTHOR

Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at  http://www.rhsheldon.com.

MEMBER FEEDBACK TO THIS TIP

Do you have a comment on this tip? Let us know.

Thank you for doing this. This is the clearest explanation I have found.
—Ann H.

******************************************

This is a good explanation for the Joins for a beginner. I started looking into SQL syntax for SQL server. Though I consider my self as an expert with ORACLE SQL, I am some what lost with these Joins of SQL Server. I am now very clear of these joins.
—Jeevi G.

******************************************

I would have also noted that putting a filter on the "joined" table essentially will make a left- or right-outer join into an inner join.
—Steven K.

******************************************

I see many testing setup scripts that begin with "if exists....drop table...." For the record, these should be used very cautiously.
—Dave R.


 

This was last published in December 2008

Dig Deeper on SQL-Transact SQL (T-SQL)

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close