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:

  • What is INNER JOIN in SQL Server?
  • How to use the NOT EQUAL operator on the SQL Server INNER JOIN clause for tables?
  • How to use SQL Server INNER JOIN with the WHERE condition?
  • How to use the INNER JOIN clause with the condition?
  • How do you fetch the first record of tables using the SQL Server INNER JOIN clause?
  • How to use the INNER JOIN clause on multiple tables in SQL Server?
  • How to use the UPDATE statement with the INNER JOIN clause in SQL Server?
  • What is the difference between the SQL Server INNER JOIN and LEFT JOIN?

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.

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.

  • What is INNER JOIN in SQL Server?
  • How to use the NOT EQUAL operator on the SQL Server INNER JOIN clause for tables?
  • How to use SQL Server INNER JOIN with the WHERE condition?
  • How to use the INNER JOIN clause with the condition?
  • How do you fetch the first record of tables using the SQL Server INNER JOIN clause?
  • How to use the INNER JOIN clause on multiple tables in SQL Server?
  • How to use the UPDATE statement with the INNER JOIN clause in SQL Server?
  • What is the difference between the SQL Server INNER JOIN and LEFT JOIN?