SQL Server Inner Join Multiple Tables

In this SQL Server tutorial, we will learn and understand how to use the SQL Server INNER JOIN clause on multiple tables.

We have already discussed how to use the SQL Server Inner Join on 2 tables in SQL Server. However, there are many cases when we need to apply Inner join on more than 2 tables. So, this SQL Server tutorial will explain how to use the SQL Server Inner Join on multiple tables.

Here is the list of topics that we are going to cover:

• Working on SQL Server Inner Join Multiple Tables
• Use of SQL Server Inner Join Multiple Tables Alias
• How to use SQL Server Inner Join Multiple Tables By Group
• How use SQL Server Inner Join Multiple Tables Between
• Use of SQL Server Inner Join Multiple Tables Having
• How to use SQL Server Inner Join Multiple Tables Like
• Use of SQL Server Inner Join Multiple Tables Max Date
• How to use SQL Server Inner Join Multiple Tables Row_Number

SQL Server Inner Join Multiple Tables

In SQL Server, the INNER JOIN clause is used to select records that have matching records from both tables. Let’s see an example of SQL Server INNER JOIN clause on multiple tables by the following query.

EXAMPLE:

SELECT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME,
CITY.CITY_NAME
FROM COUNTRIES
INNER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
INNER JOIN CITY
ON STATES.STATE_ID=CITY.STATE_ID;

With the help of the INNER JOIN in the SELECT statement, we retrieve all records of the country’s name (United States of America, United Kingdom, etc) which is based on the state’s name and city’s name from these tables i.e.; COUNTRIES, STATES, and CITY.

We hope you understood how to use the SQL Server INNER JOIN on multiple tables by the query.

Read: SQL Server Inner Join With Where Clause

SQL Server Inner Join Multiple Tables Alias

Here we will see an example of how to use the SQL Server ALIAS keyword with the INNER JOIN on multiple tables by the following query.

EXAMPLE:

SELECT C.COUNTRY_NAME,
S.STATE_NAME,
A.AMBASSADORS_NAME
FROM COUNTRIES AS C
INNER JOIN STATES AS S
ON C.COUNTRY_ID=S.COUNTRY_ID
INNER JOIN AMBASSADORS AS A
ON C.COUNTRY_ID=A.COUNTRY_ID;

With the help of the INNER JOIN query example, we retrieved all records of the country’s name with the state’s name and ambassadors’ name from these tables i.e.; COUNTRY, STATES, and AMBASSADORS.

To become easier to understand for the user, we changed the table name by using the ALIAS keyword and gave the name C, S, and A in the query for the resultset.

We hope you understood the example of SQL Server ALIAS keyword with the INNER JOIN on multiple tables by the query.

Read: SQL Server LEFT JOIN Tutorial

SQL Server Inner Join Multiple Tables By Group

Let’s see an example of SQL Server INNER JOIN with the GROUP BY clause on multiple tables by the following query.

EXAMPLE:

SELECT COUNT(CITY.CITY_NAME) AS CITY_PERCOUNTRY,
STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM CITY
INNER JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
INNER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
GROUP BY STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME;

In the INNER JOIN query example, we counted the city’s name based on the state’s name and country’s name from these tables i.e.; CITY, STATES and COUNTRIES.

We hope you understood how to use the SQL Server INNER JOIN with the GROUP BY clauses on multiple tables by the query.

Read: RIGHT JOIN in SQL Server

SQL Server Inner Join Multiple Tables Between

Here we will see an example of SQL Server INNER JOIN with BETWEEN operator on multiple tables by the query.

EXAMPLE:

SELECT CITY.CITY_NAME,
STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM CITY
INNER JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
INNER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
WHERE COUNTRIES.COUNTRY_ID BETWEEN 2 AND 5;

With the help of the INNER JOIN query example, we retrieve all records of the city’s name based on the state’s name and country’s name from these tables i.e., CITY, STATES and COUNTRY by the WHERE condition.

In the WHERE condition, we filter the country ID between 2 and 5. This will enable us to get all cities’ names based on the state’s name and country’s name in the resultset.

Hopefully, you understand how to use SQL Server INNER JOIN with the BETWEEN operator on multiple tables.

Read: SQL Server Left Join With Count

SQL Server Inner Join Multiple Tables Having

In this SQL Server subtopic section, we will learn and understand how to use the SQL Server INNER JOIN with the HAVING clause on multiple tables by the query.

EXAMPLE:

SELECT COUNT(CITY.CITY_NAME) AS CITY_PERCOUNTRY,
STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM CITY
INNER JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
INNER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
GROUP BY STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
HAVING COUNT(CITY.CITY_NAME) >3;

In the INNER JOIN query example, we counted the city’s name based on the state’s name and country’s name from these tables i.e.; CITY, STATES, and COUNTRIES by the HAVING clause.

In the HAVING clause, we are counting only those city’s names whose counted value is greater than 3 so that we can get the state’s name and country’s name for the resultset.

We hope you understand SQL Server INNER JOIN with the HAVING clause on multiple tables by the query.

Read: SQL Server Outer Join With Count

SQL Server Inner Join Multiple Tables Like

Here, we’ll examine an illustration of a SQL Server INNER JOIN query that employs LIKE clauses to join several tables together.

EXAMPLE:

SELECT CITY.CITY_NAME,
STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM CITY
INNER JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
INNER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
WHERE COUNTRIES.COUNTRY_NAME LIKE 'U%';

By using the WHERE condition, we were able to extract every record from the tables CITY, STATES, and COUNTRIES that contained the city name along with the state name and country name.

To get the state and city names for the USA country in the resultset, we filtered the country names in the WHERE condition that begins with the letter “U.”

We hope you comprehend how the query uses SQL Server’s INNER JOIN in conjunction with LIKE clauses on several tables.

Read: How To Update Table Using JOIN in SQL Server

SQL Server Inner Join Multiple Tables Max Date

Here is an example of SQL Server MAX function with the INNER JOIN on multiple tables by the query.

EXAMPLE:

SELECT MAX(TIMESHEET.CHECK_IN) AS MAX_DATE,
EMPLOYEES.FIRST_NAME,
EMPLOYEE_CREDENTIALS.LOGIN_ID
FROM TIMESHEET
INNER JOIN EMPLOYEES
ON TIMESHEET.EMP_ID=EMPLOYEES.EMP_ID
INNER JOIN EMPLOYEE_CREDENTIALS
ON EMPLOYEES.EMP_ID=EMPLOYEE_CREDENTIALS.EMP_ID
GROUP BY EMPLOYEES.FIRST_NAME,
EMPLOYEE_CREDENTIALS.LOGIN_ID;

By using the INNER JOIN in the SELECT statement, we retrieve the maximum date value of the employee’s check-in time. Additionally, we retrieve the employee’s first name and login ID from these tables i.e., TIMESHEET, EMPLOYEES, and EMPLOYEE_CREDENTIALS.

We hope you understand how to use the SQL Server MAX function with the INNER JOIN on multiple tables by the query.

Read: SQL Server SELF JOIN 

SQL Server Inner Join Multiple Tables Row_Number

Let’s see an example of SQL Server Row_Number function with the INNER JOIN on multiple tables by the query.

EXAMPLE:

SELECT ROW_NUMBER()
OVER (PARTITION BY CITY.COUNTRY_ID
ORDER BY CITY.CITY_NAME) AS CITIES_NUMBER,
STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM CITY
INNER JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
INNER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID;

With the help of the INNER JOIN in the SELECT statement, we partitioned the city’s name based on the country ID and also arranged the records of the city’s name in ascending order for the resultset.

Then we retrieve all records of the state’s name and country’s name from these tables i.e.; CITY, STATES, and COUNTRIES.

We hope you comprehend the use of SQL Server ROW_NUMBER function with the INNER JOIN on multiple tables by the query.

You may also like to read the following SQL Server tutorials

• Introduction to SQL Server Trigger
• Instead of Trigger In SQL Server
• SQL Server Drop Trigger If Exists
• SQL Server Trigger Update with Examples
• SQL Server Right Outer Join vs Left Outer Join
• SQL Server EQUI JOIN
• SQL Server Left Outer Join Multiple Tables

We now know about the post “SQL Server Inner Join Multiple Tables” after reading this tutorial. Here is a list of each subtopic we covered to help you understand the concept.

• Working on SQL Server Inner Join Multiple Tables
• Use of SQL Server Inner Join Multiple Tables Alias
• How to use SQL Server Inner Join Multiple Tables By Group
• How do we use SQL Server Inner Join Multiple Tables Between
• Use of SQL Server Inner Join Multiple Tables Having
• How to use SQL Server Inner Join Multiple Tables Like
• Use of SQL Server Inner Join Multiple Tables Max Date
• How to use SQL Server Inner Join Multiple Tables Row_Number