SQL Server INNER JOIN

Recently, I was working on the Employee Attendance Management System Project, where I retrieved all records using the INNER JOIN clause. So, here we will focus on how to use the INNER JOIN clause on the tables. In this SQL Server tutorial, we will learn and understand how to use the SQL Server INNER JOIN clause on tables using a query.

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 an 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. Both tables have been joined based on the standard column, specifically the EMP_ID column, for the result set.

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

Read: SQL Server SELF JOIN 

Example 1: Inner Join On Not Equal

In SQL Server, the NON-EQUI join clause is also a 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 result set.

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

Read: SQL Server OUTER JOIN

Example 2: Inner Join With Where Clause

In SQL Server, the WHERE condition is used to extract the record that 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 with the letter a in the EMPLOYEE table.

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

Read: SQL Server LEFT JOIN Tutorial

Example 3: Using 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

Example 4: Joining Multiple Tables

In this SQL Server subtopic section, we will learn and understand how to use the INNER JOIN clause on multiple tables by using the following 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 the SQL Server INNER JOIN clause on multiple tables in the query.

Read: What is SQL Server Cross Join?

Example 6: Using Inner Join Update

The SQL Server UPDATE statement is used to update existing records of the table by using the WHERE condition in 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;

Using the UPDATE statement, we updated the employee’s leave date from NULL to 2022-12-31 by joining the LEAVE and EMPLOYEES tables based on the WHERE condition.

Once the UPDATE statement updates a new value, we use 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 in the query.

Read: SQL Server Outer Join With Count

Example 7: Using 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;

Using the INNER JOIN in the SELECT statement, we retrieved all records of the book category based on the book’s name from both tables, BOOKS and BOOK_CATEGORY, using the ALIAS keyword. To shorten the BOOKS and BOOK_CATEGORY tables, we used the ALIAS keyword and gave the names ‘BK‘ and ‘BC’ while in the query.

The ALIAS keyword is typically used to shorten TABLE_NAME based on the user’s query condition.

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

Read: How To Update Table Using JOIN in SQL Server

Example 8: Using Inner Join Between

Here, we will learn how to use the SQL Server INNER JOIN with the BETWEEN operator on tables using 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 along with the book’s name for the result set.

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

Example 9: Using 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

Example 10: Using 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, 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

Example 11: Using 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 in 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;

Using INNER JOIN, we counted the book names based on customer IDs and names from the BOOKS, CUSTOMERS, and ORDERS tables.

In the GROUP BY clause, we grouped the records by customer ID and full name to count the books ordered based on the customer‘s details in the result set.

Read: SQL Server Count Join Group By

Example 12: Using Inner Join Row_Number

In this SQL Server subtopic section, we will see an example of the SQL Server ROW_NUMBER function with an INNER JOIN on tables in 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 the number of books’ names as per edition and retrieved the records of authors’ names 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.

Example 13: Using 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;

Using the YEAR function in the INNER JOIN, we extracted the year portion of the employee’s DOB and retrieved their first name and login ID from both the EMPLOYEES and EMPLOYEE_CREDENTIALS tables.

Key Takeaways

INNER JOIN serves as the backbone of most data retrieval operations. It helps return only matching records, making it the go-to choice for:

• Data integrity operations where you need guaranteed relationships
• Performance-critical queries that benefit from smaller result sets
• Reporting scenarios where incomplete data relationships should be excluded
• Data validation processes that require strict matching criteria

Remember these essential points:

• Consider INNER JOIN as your default choice when you need precise data relationships
• INNER JOIN returns only rows with matches in both tables
• It typically produces smaller result sets, leading to better performance
• Proper indexing on JOIN columns is crucial for optimal performance
• Always analyze execution plans to verify query efficiency

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

• Is SSMS Free
• SQL Server INNER JOIN vs LEFT JOIN
• 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