Inner Join In Information Design Tool

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.

Venn diagram representation of SQL Inner Join.

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.

SQL Join and Inner join are identical in nature.

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.

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:

Data populated in PizzaCompany table.

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.

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.

Data populated in Foods table.

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).

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.

SQL Inner Join result set on two tables.

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.

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.

Data populated in WaterPark 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.

Physical data model of the tables created.

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.

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.

Quering three tables using SQL Inner Join.

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.

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 WHERE with Inner Join in SQL Server.

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.

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.

Using Group By with Inner Join in SQL Server.

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.

SELECT e . EmployeeKey , e . FirstName , e . Title , e . HireDate ,

fs . SalesAmountQuota FROM DimEmployee e

INNER JOIN FactSalesQuota fs

ON e . EmployeeKey = fs . EmployeeKey

SQL Equi Join results.

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.

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:

SQL OUTER JOIN overview and examples
SQL Join introduction and overview

Author
Recent Posts

Gauri is a SQL Server Professional and has 6+ years experience of working with global multinational consulting and technology organizations. She is very passionate about working on SQL Server topics like Azure SQL Database, SQL Server Reporting Services, R, Python, Power BI, Database engine, etc. She has years of experience in technical documentation and is fond of technology authoring.

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