SQL Server INNER JOIN Tutorial

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

Recently I was working on the Employee Attendance Management System Project where I got to retrieve all records based on the INNER JOIN clause. So, here we will focus on how to use the INNER JOIN clause on the tables.

Here is the subtopic that we are going to cover:

• Introduction to SQL Server Inner Join
• SQL Server Inner Join On Not Equal
• Using SQL Server Inner Join With Where Clause
• How to use SQL Server Inner Join Top 1
• SQL Server Inner Join Multiple Tables
• Using SQL Server Inner Join Update
• SQL Server Inner Join Vs Left Join
• Using SQL Server Inner Join Alias
• How to use SQL Server Inner Join Between
• How to use SQL Server Inner Join Distinct
• SQL Server Inner Join Group By Clause
• SQL Server Inner Join Group By Multiple Columns
• SQL Server Inner Join Row_Number
• Working of SQL Server Inner Join Year

SQL Server Inner Join

In SQL Server, the INNER JOIN clause retrieves selected records with matching values in both tables. Here is the image of the INNER JOIN clause:

Here is ER diagram of the Employee Attendance Management System which will be used for the INNER JOIN clause in this tutorial:

SYNTAX:

SELECT SELECTED_COLUMN
FROM TABLE_1
INNER JOIN TABLE_2
ON TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN;

EXAMPLE:

SELECT EMPLOYEES.FIRST_NAME,EMPLOYEES.LAST_NAME,
EMPLOYEES.STATE,LEAVE.LEAVE_DATE
FROM EMPLOYEES
INNER JOIN LEAVE 
ON EMPLOYEES.EMP_ID=LEAVE.EMP_ID;

With the help of the INNER JOIN clause, the SELECT statement retrieves records of the employees with USA state_name and leave date from both tables i.e; EMPLOYEES and LEAVE table. And both tables have joined based on the standard column as the EMP_ID column for the result set.

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

Read: SQL Server SELF JOIN 

SQL Server Inner Join On Not Equal

In SQL Server, the NON-EQUI join clause is also the type of INNER JOIN clause. In this, we also need to retrieve data from multiple tables. So it matches the column values of tables based on the inequality which uses operators like <,>,<=,>=,!=, BETWEEN, etc.

EXAMPLE:

SELECT JOB_TITLE.PROFILE,LEAVE.LEAVE_DATE
FROM JOB_TITLE
INNER JOIN LEAVE
ON JOB_TITLE.JOB_ID!=LEAVE.JOB_ID;

So with the help of the INNER JOIN clause, the SELECT statement retrieves all records of the PROFILE and LEAVE_DATE column from both tables i.e JOB_TITLE and LEAVE tables. But the records will be retrieved based on the non-equality operator of a common column in both tables for the resultset.

We hope that you have understood how to use the INNER JOIN clause with the NON–EQUAL operator on tables by the query.

Read: SQL Server OUTER JOIN

SQL Server Inner Join With Where Clause

In SQL Server, the WHERE condition is used to extract the record which fulfils its condition.

EXAMPLE:

SELECT EMPLOYEES.FIRST_NAME,
EMPLOYEES.LAST_NAME,
EMPLOYEES.STATE
FROM EMPLOYEES
INNER JOIN LEAVE 
ON EMPLOYEES.EMP_ID=LEAVE.EMP_ID
WHERE EMPLOYEES.STATE like '%a%';

With the help of the SELECT statement, we retrieve all records of the FIRST_NAME and LAST_NAME columns from both tables i.e; EMPLOYEES and LEAVE. And to get the result set, we have used the WHERE condition to find all employee state names starting within alphabet a in the EMPLOYEE table.

We hope that you have understood how to use SQL Server INNER JOIN clause with the WHERE condition on tables by the query.

Read: SQL Server LEFT JOIN Tutorial

SQL Server Inner Join Top 1

In SQL Server, the TOP clause is used to specify the number of records to be returned. And it’s normally used with a large amount of data in a table. If we produce a large record then it will affect the performance. Not every database system supports the TOP clause.

EXAMPLE:

SELECT TOP 1 EMPLOYEES.FIRST_NAME,EMPLOYEES.LAST_NAME,
LEAVE.LEAVE_DATE
FROM EMPLOYEES
INNER JOIN LEAVE 
ON EMPLOYEES.EMP_ID=LEAVE.EMP_ID;

According to the INNER JOIN clause in the query above, we have used the TOP clause as TOP 1 in order to extract the first record from both tables, i.e., the EMPLOYEES and LEAVE tables.   

We hope that you have understood how to use SQL Server TOP clause with the INNER JOIN clause on tables by the query.

Read: RIGHT JOIN in SQL Server

SQL Server Inner Join Multiple Tables

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

EXAMPLE:

SELECT EMPLOYEES.FIRST_NAME,EMPLOYEES.LAST_NAME,
JOB_TITLE.PROFILE,LEAVE.LEAVE_DATE
FROM EMPLOYEES
INNER JOIN LEAVE
ON EMPLOYEES.EMP_ID=LEAVE.EMP_ID
INNER JOIN JOB_TITLE
ON LEAVE.JOB_ID=JOB_TITLE.JOB_ID;

By using the SELECT statement with the INNER JOIN clause, we have joined multiple tables i.e; EMPLOYEES, LEAVE and JOB_TITLE tables for the result set.

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

Read: What is SQL Server Cross Join?

SQL Server Inner Join Update

The SQL Server UPDATE statement is used to update existing records of the table by using the WHERE condition by the query. If we don’t use the WHERE condition, the UPDATE statement will update all records of a particular column in the table.

EXAMPLE:

UPDATE LEAVE 
SET LEAVE.LEAVE_DATE='2022-12-31'
FROM LEAVE
INNER JOIN EMPLOYEES
ON LEAVE.EMP_ID=EMPLOYEES.EMP_ID
WHERE LEAVE.LEAVE_ID=1;

SELECT * FROM LEAVE;

With the help of the UPDATE statement, we have updated the leave date of the employee from NULL to 2022-12-31 from joining both tables i.e; LEAVE and EMPLOYEES which is done based on the WHERE condition.

Once a new value is updated by the UPDATE statement, we have used the SELECT statement to retrieve the latest records from the LEAVE table.

We hope that you have understood how to use the SQL Server UPDATE statement with the INNER JOIN clause on tables by the query.

Read: SQL Server Outer Join With Count

SQL Server Inner Join Vs Left Join

We have already written about the difference between the SQL Server INNER JOIN and LEFT JOIN clauses in the SQL Server LEFT JOIN post. Kindly look into the post, for more clear information.

SQL Server Inner Join Alias

Let’s see an example of SQL Server INNER JOIN on tables with the ALIAS keyword by the query.

EXAMPLE:

SELECT BC.CATEGORY_NAME,
BK.BOOK_NAME
FROM BOOKS AS BK
INNER JOIN BOOK_CATEGORY AS BC
ON BK.BOOK_ID=BC.BOOK_ID;

With the help of the INNER JOIN in the SELECT statement, we retrieved all records of the book category based on the book’s name from both tables i.e.; BOOKS and BOOK_CATEGORY by the ALIAS keyword. To shorter the BOOKS and BOOK_CATEGORY tables, we used the ALIAS keyword and gave the name ‘BK‘ and ‘BC’ while in the query.

So the ALIAS keyword is normally used when we want to shorter the TABLE_NAME based on the USER’S condition for the query.

We hope that you have understood a sample example of SQL Server INNER JOIN with the ALIAS keyword on tables by the query.

Read: How To Update Table Using JOIN in SQL Server

SQL Server Inner Join Between

Here we will learn and understand how to use the SQL Server INNER JOIN with the BETWEEN operator on tables by the following query.

EXAMPLE:

SELECT AUTHORS.AUTHOR_FULLNAME,
BOOKS.BOOK_NAME
FROM AUTHORS
INNER JOIN BOOKS
ON AUTHORS.AUTHOR_ID=BOOKS.AUTHOR_ID
WHERE BOOKS.BOOK_PRICE BETWEEN 15 AND 50;

In the INNER JOIN query example, we retrieved all records of the author’s name with their book’s name from both tables i.e.; AUTHORS and BOOKS by the WHERE condition.

In the WHERE condition, we filtered the book’s price between $15 and $50 so that we can get the author’s name with the book’s name for the resultset.

You should now be able to use the SQL Server INNER JOIN using the BETWEEN operator on the query’s tables.

Read: SQL Server Left Join With Count

SQL Server Inner Join Distinct

Here is a sample example of SQL Server DISTINCT with the INNER JOIN clause on tables by the following query.

EXAMPLE:

SELECT DISTINCT AUTHORS.AUTHOR_FULLNAME,
BOOKS.BOOK_NAME
FROM AUTHORS
INNER JOIN BOOKS
ON AUTHORS.AUTHOR_ID=BOOKS.AUTHOR_ID;

With the help of the INNER JOIN in the SELECT statement, we retrieve the unique author’s name with their book’s name from both tables i.e.; AUTHORS and BOOKS.

We hope that you have understood how to use the SQL Server DISTINCT with the INNER JOIN clause on tables by the query.

Read: SQL Server Outer Join With Count

SQL Server Inner Join Group By Clause

Here we will see an example of SQL Server INNER JOIN with the GROUP BY clause on tables by the following query.

EXAMPLE:

SELECT COUNT(BOOKS.BOOK_NAME) AS BOOKS_PERAUTHOR,
AUTHORS.AUTHOR_FULLNAME
FROM BOOKS
INNER JOIN AUTHORS
ON BOOKS.AUTHOR_ID=AUTHORS.AUTHOR_ID
GROUP BY AUTHORS.AUTHOR_FULLNAME;

With the help of the INNER JOIN in the SELECT statement, we retrieved and counted all book’s names based on the author’s name from both tables i.e.; BOOKS and AUTHORS.

We hope that you understood how to use an example of SQL Server INNER JOIN with the GROUP BY clause on tables by the query.

Read: RIGHT JOIN in SQL Server

SQL Server Inner Join Group By Multiple Columns

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

EXAMPLE:

SELECT COUNT(BOOKS.BOOK_NAME) AS ORDER_PERCUSTOMER,
CUSTOMERS.CUSTOMER_FULLNAME,
ORDERS.CUSTOMER_ID
FROM BOOKS 
INNER JOIN ORDERS
ON BOOKS.BOOK_ID=ORDERS.BOOK_ID
INNER JOIN CUSTOMERS
ON ORDERS.CUSTOMER_ID=CUSTOMERS.CUSTOMER_ID
GROUP BY CUSTOMERS.CUSTOMER_FULLNAME,
ORDERS.CUSTOMER_ID;

With the help of INNER JOIN, we counted the book’s name based on the customer id & names from tables i.e.; BOOKS, CUSTOMERS, and ORDERS.

In the GROUP BY clause, we grouped the records of the customer’s id and full name so that it can counts books ordered based on the customer‘s detail for the result set.

We hope that you have understood how to use the SQL Server INNER JOIN with the GROUP BY clause on multiple column from tables by the query.

Read: SQL Server Count Join Group By

SQL Server Inner Join Row_Number

In this SQL Server subtopic section, we will see an example of SQL Server ROW_NUMBER function with the INNER JOIN on tables by the query.

EXAMPLE:

SELECT ROW_NUMBER() 
OVER(PARTITION BY BOOKS.BOOK_NAME
ORDER BY BOOKS.BOOK_NAME) AS BOOKS_ORDERNUMBER,
BOOKS.BOOK_NAME,
AUTHORS.AUTHOR_FULLNAME
FROM BOOKS
INNER JOIN AUTHORS
ON BOOKS.AUTHOR_ID=AUTHORS.AUTHOR_ID;

In the INNER JOIN query example, we partitioned number of book’s name as per edition and retrieved the records of author’s name from both tables i.e.; AUTHORS and BOOKS.

We hope that you have understood the subtopic “SQL Server Inner Join Row_Number” by using the SQL Server ROW_NUMBER function with the INNER JOIN clause on tables by the query.

SQL Server Inner Join Year

Here we’ll see an example of the SQL Server YEAR function with the INNER JOIN on tables by the query.

EXAMPLE:

SELECT YEAR(EMPLOYEES.DATE_OF_BIRTH) AS EMPLOYEE_DOB,
EMPLOYEES.FIRST_NAME,
EMPLOYEE_CREDENTIALS.LOGIN_ID
FROM EMPLOYEES
INNER JOIN EMPLOYEE_CREDENTIALS
ON EMPLOYEES.EMP_ID=EMPLOYEE_CREDENTIALS.EMP_ID;

With the help of the YEAR function in the INNER JOIN, we extracted year portion value of the employee’s DOB and retrieved employee’s first name and login ID from both tables i.e.; EMPLOYEES and EMPLOYEE_CREDENTIALS.

We hope that you have understood the subtopic “SQL Server Inner Join Year” by using the SQL Server YEAR function with the INNER JOIN on tables by the query.

Also, take a look at some more SQL Server tutorials.

• Introduction to SQL Server Trigger
• SQL Server Inner Join Top 1
• What is a View in SQL Server
• Stored procedure in SQL Server
• Identity Column in SQL Server

After reading this tutorial, we now know about the topic “SQL Server RIGHT JOIN.” To assist you in understanding the concept, here is a list of every subtopic that we have discussed.

• Introduction to SQL Server Inner Join
• SQL Server Inner Join On Not Equal
• Using SQL Server Inner Join With Where Clause
• How to use SQL Server Inner Join Top 1
• SQL Server Inner Join Multiple Tables
• Using SQL Server Inner Join Update
• SQL Server Inner Join Vs Left Join
• Using SQL Server Inner Join Alias
• How to use SQL Server Inner Join Between
• How to use SQL Server Inner Join Distinct
• SQL Server Inner Join Group By Clause
• SQL Server Inner Join Group By Multiple Columns
• SQL Server Inner Join Row_Number
• Working of SQL Server Inner Join Year