Inner Join by Example in SQL Server

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.

Syntax

The following is the syntax of inner join.

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

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 / Regular
Customers 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 customer
DiscVoucher The discounts are offered based on the total value of the order

--Sample database--

Sample database structure for Join Query example
Sample database structure for Join Query example

--SQL Query--

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.

The following is the result of the query

Inner join example

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.

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.

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

Inner join 3 or more tables
Result

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

results of Inner join with subquery
results of Inner join with a subquery

Further, you can join the CustomerType table to know the type of customer as shown below

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.

Multiple conditions in the join condition

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

inner join with logical operators in join condition
inner join with logical operators in the join condition

References

  1. Join Syntax (SQL Server)
  2. Sample database

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.

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top