What is the SQL INNER JOIN?
A SQL INNER JOIN is a technique that allows you to retrieve data from multiple tables by connecting them with a joining key. An INNER JOIN can be thought of as the intersection of two tables based on some joining key. Typically, ID columns like SalesPersonID or BusinessEntityID in the AdventureWorks database, act as the joining keys. Joining keys are the columns that connect multiple tables together.
As mentioned earlier, INNER JOINs are like an intersection of tables. Rows are returned as long as there a match on the joining key between both tables. A Venn diagram is often used to visualize the INNER JOIN:
A few examples will help clarify the concept of the SQL INNER JOIN. In the AdventureWorks database, the Production.Product table contains details about each product the company sells. There is a table called Production.ProductSubCategory that contains details about the SubCategory for each product. In both tables there exits an ID column called ProductSubCategoryID. This can be used as the joining key between the two tables so that we can return the product’s name and the product subcategory’s name in the same query. This is important and new because the columns appear in different tables.
SELECT Production.Product.Name, Production.ProductSubCategory.Name
INNER JOIN Production.ProductSubCategory
ON Production.Product.ProductSubCategoryID = Production.ProductSubCategory.ProductSubCategoryID
We are telling SQL to link these two tables based on the ProductSubCategoryID column from each table. Do you remember column aliases? Well, we can use something called table aliases to reduce the amount of typing. A table alias is just like a column alias except that it assigns an alias to a table instead of a column. Unlike column aliases, a table alias can be referenced in the same query in all clauses.
To reduce the amount of code used in the prior SQL INNER JOIN example with table aliases and assign proper column aliases in the SELECT clause:
SELECT P.Name AS [Product Name], PS.Name AS [SubCategory Name]
FROM Production.Product P
INNER JOIN Production.ProductSubCategory PS
ON P.ProductSubCategoryID = PS.ProductSubCategoryID
We do not need to use the AS when assigning a table alias.
Let’s look at another example of the SQL INNER JOIN. Let’s join the HumanResources.Employee table with the Person.Person table using the BusinessEntityID joining key. We will then return the FirstName and LastName column from Person.Person and the JobTitle column from HumanResources.Employee. Since we are using an INNER JOIN, the only rows that will be returned are those rows where the BusinessEntityID value is in BOTH tables.
SELECT P.FirstName, P.LastName, E.JobTitle
FROM HumanResources.Employee E
INNER JOIN Person.Person P
ON P.BusinessEntityID = E.BusinessEntityID
And just like that, we are able to retrieve columns from two different tables in the same query!
Using the examples above as a resource, try to complete the following practice problems.
- Join the Person.Person table to the HumanResources.Employee table. Return the FirstName and LastName columns from Person.Person and the JobTitle and NationalIDNUmber columns from HumanResources.Employee.
- Join the Production.ProductSubCategory table with the Production.ProductCategory using the ProductCategoryID column. Return the Name column from each table and assign proper aliases.
- Join the Production.Product, Production.ProductSubCategory, and Production.ProductCategory tables together. Return the Name column from all three tables and assign proper column aliases. (Hint: INNER JOIN from Production.Product to Production.ProductSubCategory and then INNER JOIN from Production.ProductSubCategory to Production.ProductCategory.)