Organizations are generating and analyzing unmatched volumes of data with each passing minute. In this article, we will demonstrate how we can employ SQL Inner Join to query and access data from multiple tables that store this incessantly growing data in the SQL databases.
SQL Joins
Before we get started with SQL Inner Join, I would like to call out SQL Join here. Join is the widely-used clause in the SQL Server essentially to combine and retrieve data from two or more tables. In a real-world relational database, data is structured in a large number of tables and which is why, there is a constant need to join these multiple tables based on logical relationships between them. There are four basic types of Joins in SQL Server – Inner, Outer (left, right, full), Self and Cross join. To get a quick overview of all these joins, I would recommend going through this link, SQL Join types overview and tutorial.
This article targets all about the Inner Join in SQL Server, so let’s head over to it.
Definition of SQL Inner Join
Inner Join clause in SQL Server creates a new table (not physical) by combining rows that have matching values in two or more tables. This join is based on a logical relationship (or a common field) between the tables and is used to retrieve data that appears in both tables.
Assume, we have two tables, Table A and Table B, that we would like to join using SQL Inner Join. The result of this join will be a new result set that returns matching rows in both these tables. The intersection part in black below shows the data retrieved using Inner Join in SQL Server.
SQL Server Inner Join Syntax
Below is the basic syntax of Inner Join.
SELECT Column_list
FROM TABLE1
INNER JOIN TABLE2
ON Table1.ColName = Table2.ColName
Inner Join syntax basically compares rows of Table1 with Table2 to check if anything matches based on the condition provided in the ON clause. When the Join condition is met, it returns matched rows in both tables with the selected columns in the SELECT clause.
SQL Inner Join clause is the same as Join clause and works the same way if we don’t specify the type (INNER) while using the Join clause. In short, Inner Join is the default keyword for Join and both can be used interchangeably.
Note – We will use the keyword ‘Inner’ Join in this article for the sake of more clarity. You can omit it while writing your queries and can use only ‘Join’ as well.
SQL Inner Join in action
Let’s try to understand the concept of Inner Join through an interesting data sample that deals with a Pizza Company and its food distribution. I am going to create two tables first – table ‘PizzaCompany’ that manages different branches of Pizza outlets in a few cities and table ‘Foods’ that stores food distribution details across these companies. You can execute the code below to create and populate data into these two tables. All this data is hypothetical and you can create in any of your existing databases.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE [dbo].[PizzaCompany] ( [CompanyId] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED, [CompanyName] [varchar](50) , [CompanyCity] [varchar](30) ) SET IDENTITY_INSERT [dbo].[PizzaCompany] ON; INSERT INTO [dbo].[PizzaCompany] ([CompanyId], [CompanyName], [CompanyCity]) VALUES(1,'Dominos','Los Angeles') ; INSERT INTO [dbo].[PizzaCompany] ([CompanyId], [CompanyName], [CompanyCity]) VALUES(2,'Pizza Hut','San Francisco') ; INSERT INTO [dbo].[PizzaCompany] ([CompanyId], [CompanyName], [CompanyCity]) VALUES(3,'Papa johns','San Diego') ; INSERT INTO [dbo].[PizzaCompany] ([CompanyId], [CompanyName], [CompanyCity]) VALUES(4,'Ah Pizz','Fremont') ; INSERT INTO [dbo].[PizzaCompany] ([CompanyId], [CompanyName], [CompanyCity]) VALUES(5,'Nino Pizza','Las Vegas') ; INSERT INTO [dbo].[PizzaCompany] ([CompanyId], [CompanyName], [CompanyCity]) VALUES(6,'Pizzeria','Boston') ; INSERT INTO [dbo].[PizzaCompany] ([CompanyId], [CompanyName], [CompanyCity]) VALUES(7,'chuck e cheese','Chicago') ; SELECT * FROM PizzaCompany: |
This is how data in the PizzaCompany table looks like:
Let’s create and populate Foods table now. CompanyID in this table is the foreign key that is referencing to the Primary key of the PizzaCompany table created above.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE TABLE [dbo].[Foods] ( [ItemId] INT PRIMARY KEY CLUSTERED , [ItemName] Varchar(50), [UnitsSold] int, CompanyID int, FOREIGN KEY(CompanyID) REFERENCES PizzaCompany(CompanyID) ) INSERT INTO [dbo].[Foods] ([ItemId], [ItemName], [UnitsSold], [CompanyId]) VALUES(1,'Large Pizza',5,2) INSERT INTO [dbo].[Foods] ([ItemId], [ItemName], [UnitsSold], [CompanyId]) VALUES(2,'Garlic Knots',6,3) INSERT INTO [dbo].[Foods] ([ItemId], [ItemName], [UnitsSold], [CompanyId]) VALUES(3,'Large Pizza',3,3) INSERT INTO [dbo].[Foods] ([ItemId], [ItemName], [UnitsSold], [CompanyId]) VALUES(4,'Medium Pizza',8,4) INSERT INTO [dbo].[Foods] ([ItemId], [ItemName], [UnitsSold], [CompanyId]) VALUES(5,'Breadsticks',7,1) INSERT INTO [dbo].[Foods] ([ItemId], [ItemName], [UnitsSold], [CompanyId]) VALUES(6,'Medium Pizza',11,1) INSERT INTO [dbo].[Foods] ([ItemId], [ItemName], [UnitsSold], [CompanyId]) VALUES(7,'Small Pizza',9,6) INSERT INTO [dbo].[Foods] ([ItemId], [ItemName], [UnitsSold], [CompanyId]) VALUES(8,'Small Pizza',6,7) SELECT * FROM Foods |
The following table shows data in the Foods table. This table stores information like units sold per food item and also the pizza outlet (CompanyId) that delivers it.
Now, if we would like to see the items and also the units sold by each pizza company, we can combine these two tables with the help of an inner join clause being used on the field CompanyId (in our case this shares a foreign key relationship).
|
1 2 3 4 |
SELECT pz.CompanyCity, pz.CompanyName, pz.CompanyId AS PizzaCompanyId, f.CompanyID AS FoodsCompanyId, f.ItemName, f.UnitsSold FROM PizzaCompany pz INNER JOIN Foods f ON pz.CompanyId = f.CompanyId |
Below is the result set of the above SQL Inner Join query. For each row in the table PizzaCompany, Inner Join compares and finds the matching rows in the table Foods and returns all the matching rows as shown below. And if you notice, CompanyId = 5 is excluded from the query result, as it does not make a match in the Foods table.
With the help of the above result set, we can make out the items and also the count of items delivered by pizza outlets in various cities. For instance, Dominos made a delivery of 7 Breadsticks and 11 Medium Pizza in Los Angeles.
SQL Inner Join on three tables
Let’s explore more into this join and suppose three waterparks (looks like summer) get opened in the state and these waterparks outsource food from the pizza outlets mentioned in the table PizzaCompany.
I am going to quickly create a table WaterPark and load some arbitrary data into it as shown below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE [dbo].[WaterPark] ( [WaterParkLocation] VARCHAR(50), [CompanyId] int, FOREIGN KEY(CompanyID) REFERENCES PizzaCompany(CompanyID) ) INSERT INTO [dbo].[WaterPark] ([WaterParkLocation], [CompanyId]) VALUES('Street 14',1) INSERT INTO [dbo].[WaterPark] ([WaterParkLocation], [CompanyId]) VALUES('Boulevard 2',2) INSERT INTO [dbo].[WaterPark] ([WaterParkLocation], [CompanyId]) VALUES('Rogers 54',4) INSERT INTO [dbo].[WaterPark] ([WaterParkLocation], [CompanyId]) VALUES('Street 14',3) INSERT INTO [dbo].[WaterPark] ([WaterParkLocation], [CompanyId]) VALUES('Rogers 54',5) INSERT INTO [dbo].[WaterPark] ([WaterParkLocation], [CompanyId]) VALUES('Boulevard 2',5) SELECT * FROM WaterPark |
And below is the output of this table.
As the saying goes, the picture is worth a thousand words. Let’s quickly see the database diagram of these three tables with their relationships to understand them better.
Now we are going to include this third table in the SQL Inner Join clause to see how it is going to impact the result set. Per data in the WaterPark table, the three waterparks have been outsourcing food from all the Pizza Companies but Pizzeria (Id=6) and chuck e cheese (Id = 7). Execute the code below to see all the food distribution across the waterparks by the Pizza outlets.
|
1 2 3 4 5 6 |
SELECT pz.CompanyId, pz.CompanyCity, pz.CompanyName,f.ItemName, f.UnitsSold, w.WaterParkLocation FROM PizzaCompany pz INNER JOIN Foods f ON pz.CompanyId = f.CompanyId INNER JOIN WaterPark w ON w.CompanyId = pz.CompanyId ORDER BY pz.CompanyId |
Based on CompanyId, SQL Inner Join matches rows in both tables, PizzaCompany (Table 1) and Foods (Table 2) and subsequently looks for a match in the WaterPark (Table 3) to return rows. As shown below, with the addition of inner join on WaterPark, CompanyId (6,7 (apart from 5)) are also excluded from the final result set as the condition w.CompanyId = pz.CompanyId is not satisfied for Ids (6,7). This is how SQL Inner join helps to return specific rows of data from multiple tables.
Let’s dig into SQL Inner Join more with a few more T-SQL clauses.
Using WHERE with Inner Join
We can filter records based on a specified condition when SQL Inner Join is used with a WHERE clause. Assume that we would like to get the rows where units sold were more than 6.
In the following query, the WHERE clause is added to extract results with value more than 6 for units sold.
|
1 2 3 4 5 |
SELECT pz.CompanyId, pz.CompanyCity, pz.CompanyName,f.ItemName, f.UnitsSold FROM PizzaCompany pz INNER JOIN Foods f ON pz.CompanyId = f.CompanyId WHERE f.UnitsSold > 6 ORDER BY pz.CompanyCity |
Execute above code in SSMS to see the below result. Four such records are returned by this query.
Using Group By with Inner Join
SQL Inner Join permits us to use Group by clause along with aggregate functions to group the result set by one or more columns. Group by works conventionally with Inner Join on the final result returned after joining two or more tables. If you are not familiar with Group by clause in SQL, I would suggest going through this to have a quick understanding of this concept. Below is the code that makes use of Group By clause with the Inner Join.
|
1 2 3 4 5 |
SELECT pz.CompanyCity, pz.CompanyName, SUM(f.UnitsSold) AS TotalQuantitySold FROM PizzaCompany pz INNER JOIN Foods f ON pz.CompanyId = f.CompanyId GROUP BY pz.CompanyCity, pz.CompanyName ORDER BY pz.CompanyCity |
Here, we intend to obtain total items sold by each Pizza company present in the City. As you can see below, aggregated result in ‘totalquantitysold’ column as 18 (7+11) and 9 (6+3) for Los Angeles and San Diego respectively is computed.
A brief note on Equi and Theta Join
Before we conclude this article, let’s quickly go over terms, a SQL developer may hear sporadically – Equi and Theta Join.
Equi Join
As the name suggests, equi join contains an equality operator ‘=’ either in the Join clause or in the WHERE condition. SQL Inner, Left, Right are all equi joins when ‘=’ operator is being used as a comparison operator. Usually, when there is a mention of SQL Inner Join, it is considered as an Inner equi Join, in an unusual situation only, equality operator is not used.
To make things easier, I am going to refer to AdventureWorksDW2017 sample database and fire a query against existing tables to demonstrate how equi join looks like.
|
1 2 3 4 |
SELECT e.EmployeeKey, e.FirstName, e.Title, e.HireDate, fs.SalesAmountQuota FROM DimEmployee e INNER JOIN FactSalesQuota fs ON e.EmployeeKey = fs.EmployeeKey |
Theta Join (Non-equi join)
Non-equi join is basically opposite of equi-join and is used when we join on a condition other than ‘=’ operator. This type is rarely used in practice. Below is an example that makes use of theta join with an inequality operator (<) to evaluate profit by estimating cost and selling prices in two tables.
|
1 |
SELECT * FROM Table1 T1, Table2 T2 WHERE T1.ProductCost < T2.SalesPrice |
Conclusion
I hope this article on ‘SQL Inner Join’ provides a comprehensible approach to one of the important and frequently used clauses – ‘Inner join’ in the SQL Server to combine multiple tables. In case you have any questions, please feel free to ask in the comments section below.
To continue your learning on SQL Joins, you can refer to below posts:
She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server.
View all posts by Gauri Mahajan
- Oracle Substring function overview with examples - June 19, 2024
- Introduction to the SQL Standard Deviation function - April 21, 2023
- A quick overview of MySQL foreign key with examples - February 7, 2023