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:

Sql server inner join image
Image of SQL Server 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:

Sql server inner join er diagram
ER diagram of Employee Attendance Management System

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.

Sql server inner join example
Example of SQL Server INNER JOIN clause

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.

Sql server inner join on not equal tutorial
Example of SQL Server INNER JOIN on Not Equal

We hope that you have understood how to use the INNER JOIN clause with the NONEQUAL 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.

Sql server inner join with condition example
Example of SQL Server INNER JOIN with the WHERE clause

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.   

Sql server inner join top 1 example
Example of SQL Server Inner Join Top 1

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.

Sql server inner join multiple tables
Example of SQL Server INNER JOIN on multiple tables

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.

Sql server inner join update example
Example of SQL Server UPDATE statement with the INNER JOIN clause

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.

Sql server inner join alias example
Example of SQL Server Inner Join Alias

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.

Sql server inner join between example
Example of SQL Server INNER JOIN with the BETWEEN operator

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.

Sql server inner join distinct
Example of SQL Server DISTINCT with the INNER JOIN

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.

Sql server inner join group by clause example
Example of SQL Server INNER JOIN with GROUP BY clause

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 customers detail for the result set.

Sql server inner join group by multiple columns example
Example of SQL Server Inner Join Group by Multiple Columns

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.

Sql server inner join row_number sample example
Example of SQL Server Inner Join Row_Number

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.

Sql server inner join year example
Example of SQL Server YEAR function with the INNER JOIN clause

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.

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