The inner join is one of the most commonly used join statement in SQL Server. A join lets us combine results from two or more tables into a single result set. It includes only those results which are common to both the tables. This article explores the inner join in more detail with examples.
Table of Contents
Syntax
The following is the syntax of inner join.
1 2 3 4 5 6 | SELECT <Columns> FROM tableA INNER JOIN tableB ON (join_condition) |
The join_condition defines the condition on which the tables are joined. This condition is evaluated for each row of the result. If the condition is evaluated to true, then the rows are joined and added to the result set.
If the conditions do not match, then the rows are ignored and not added to the result set
The word INNER is not necessary as it is the default. Hence the following statement is the same as the above
1 2 3 4 5 6 | SELECT <Columns> FROM tableA JOIN tableB ON (join_condition) |
Inner Join
The inner join is best explained with the following Venn diagram.
In the above example, the circles represent the two tables. Table A and Table B, which we would like to join using the inner join.
The intersection part in blue above shows the data rows which satisfy the join condition. The inner join of the above tables returns the matching rows in both these tables.
Inner Join Example
Now, let us see the inner join using a real-life example
Sample database
Consider the following tables from the table reservation system of a restaurant
CustomerType : Customers are defined as VIP / RegularCustomers List of customers with Customer Type.Tables The list of tables available in the restaurant. CustomerID field indicates that the customer has reserved the table.Orders : The orders placed by the customerDiscVoucher The discounts are offered based on the total value of the order
[tabby title=”–Sample database–“]
[tabby title=”–SQL Query–“]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 | create table customerType ( CustomerTypeID int primary key, Name varchar(10) ) insert into customerType values (1,'VIP') insert into customerType values (2,'Regular') create table Customers ( CustomerID int primary key, Name varchar(100), CustomerTypeID int null, CONSTRAINT FK_Customers_CustomerTypeID FOREIGN KEY (CustomerTypeID) REFERENCES customerType (CustomerTypeID) ); insert into Customers values(1, 'Kevin Costner',1); insert into Customers values(2, 'Akshay Kumar',2); insert into Customers values(3, 'Sean Connery',1); insert into Customers values(4, 'Sanjay Dutt',2); insert into Customers values(5, 'Sharukh Khan',null); create table Tables ( TableNo int primary key, CustomerID int null, CONSTRAINT FK_Tables_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID) ); insert into Tables values(1, null); insert into Tables values(2, 1); insert into Tables values(3, 2); insert into Tables values(4, 5); insert into Tables values(5, null); create table Orders ( OrderNo int primary key, OrderDate datetime, CustomerID int null, Amount decimal(10,2), CONSTRAINT FK_Orders_CustomerID FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID) ); insert into Orders Values(1,'2019-12-10',1,5000) insert into Orders Values(2,'2019-12-09',1,3000) insert into Orders Values(3,'2019-12-10',2,7000) insert into Orders Values(4,'2019-12-01',2,7000) insert into Orders Values(5,'2019-12-10',3,1000) insert into Orders Values(6,'2019-12-03',3,1000) insert into Orders Values(7,'2019-12-10',4,3000) insert into Orders Values(8,'2019-12-10',2,4000) create table DiscVoucher ( FromAmount decimal(10,0) , UptoAmount decimal(10,0) , Discount decimal(10,0) ) insert into DiscVoucher Values(0,3000,0) insert into DiscVoucher Values(3001,8000,10) insert into DiscVoucher Values(8001,99999,25) |
[tabbyending]
Query
As said earlier, the inner join returns the rows which have related rows in the joined table. Rows that do not have any relation to the other table are left out.
The following query returns the list of customers who have reserved a table.
1 2 3 4 5 6 | Select C.CustomerID,C.Name, T.TableNo From Customers C join Tables T on (C.CustomerID=T.CustomerID) |
The following is the result of the query
The tables 2,3,4 are reserved by the customer with id 1,2 & 5. The inner join returns these rows.
The tables 1 & 5 are unreserved, while Customer with id 3 & 4 has not booked any tables. The inner join does not include them in the result.
Using Where
You can also make use of where clause instead of join clause as shown below. SQL Server is smart enough to do an inner join.
1 2 3 4 5 | select C.CustomerID,C.Name,T.TableNo From Customers C ,Tables T Where (C.CustomerID=T.CustomerID) |
Inner join 3 or more tables
You can join more than 3 tables in an join. The syntax is as shown below. You can also mix other types of joins The SQL Server allows joining data from up to 256 tables.
1 2 3 4 5 6 7 8 9 10 | SELECT <Columns> FROM first_table INNER JOIN second_table ON (join_condition) INNER JOIN third_table ON (join_condition) INNER JOIN fourth_table ON (join_condition) |
To get reserved tables with customer name & customer type, we need to join all the three tables together as shown below Note that the customer Sharukh khan ( CustomerID 5 ) has Customer Type as NULL.
Query
1 2 3 4 5 6 7 8 | Select Cu.CustomerID,Cu.Name, Tb.TableNo, ct.Name as CustomerType From Customers Cu join Tables Tb on (Cu.CustomerID=Tb.CustomerID) join customerType ct on (Cu.CustomerTypeID=ct.CustomerTypeID) |
The interesting thing about the result is that CustomerID 5 (Sharukh Khan) does not appear in the result although he as reserved a table.
The Customers & tables have three matching rows. Hence the first inner join returns three rows. The result is joined with the CustomerType table. Customer with ID 5 does not have Customer Type, hence it is discarded and only two rows are returned.
Subquery in a Join
Instead of a table, you can make use of a subquery.
We have the Orders table in our sample database. it contains the date of order, customerID and amount of Order. We would like to find out the total order placed by each customer.
Query
We sum the amount field customer wise to arrive at the total order. We then join it with the customer table from where we can get the name as shown below
1 2 3 4 5 6 7 8 | Select Cu.CustomerID,Cu.Name, Ord.Amount From Customers Cu join ( Select CustomerID, Sum(Amount) As Amount From Orders Group by CustomerID) Ord On (Cu.CustomerID= Ord.CustomerID) |
Further, you can join the CustomerType table to know the type of customer as shown below
1 2 3 4 5 6 7 8 9 10 | Select Cu.CustomerID,Cu.Name, Ord.Amount, ct.Name as CustomerType From Customers Cu join ( Select CustomerID, Sum(Amount) As Amount From Orders Group by CustomerID) Ord On (Cu.CustomerID= Ord.CustomerID) join customerType ct on ( cu.CustomerTypeID=ct.CustomerTypeID) |
Multiple Conditions in Join
You can use more than one condition in the join condition. The following query joins the customer table with the order table using both the CustomerId and OrderDate column.
1 2 3 4 5 6 | select Cu.CustomerID,Cu.Name, Ord.OrderDate, Ord.Amount From Customers Cu join Orders Ord On (Cu.CustomerID= Ord.CustomerID and OrderDate='2019-12-10') |
An interesting point to note here is that the customer with id 2 appears twice in the result. Because the orders table has two matching records. Hence the final result includes both the records.
Comparison in join condition
We only looked at the equality operator in the join condition. Now let us see an example of other logical operators.
The customers are given a discount voucher based on their spend. We need to join the DiscVoucher table to find out the discount.
Query
1 2 3 4 5 6 7 8 9 10 | select Cu.CustomerID,Cu.Name, Ord.Amount, disc.Discount From Customers Cu join ( Select CustomerID, Sum(Amount) As Amount From Orders Group by CustomerID) Ord On (Cu.CustomerID= Ord.CustomerID) join DiscVoucher disc on ( ord.amount >= disc.FromAmount and ord.amount <= disc.UptoAmount) |
References
Summary
We learned how to make use of inner join with a few examples. In the next article, we will explore the left join in more detail.
