How to Use EQUI JOIN in SQL Server

While working with relational databases, I was required to combine rows with multiple tables based on related columns. I will use the commonly used EQUI JOIN in SQL Server for that.

An EQUI JOIN is a specific type of INNER JOIN that links tables based on an equality condition between two columns. In this post, I will explain what an EQUI JOIN is and how it works in SQL Server, as well as provide practical examples to help you understand.

What is EQUI JOIN in SQL Server

Generally, joins are used to retrieve data if we want to select data from multiple tables and retrieve data that includes related data from multiple tables. An EQUI JOIN clause is an operation that combines multiple columns based on equality and/or matching column values in the associated tables. Equi Join is an inner join that gives the output by performing a join operation between multiple tables on a standard column between them.

The syntax for EQUI JOIN in SQL Server

Below is the syntax for EQUI JOIN in SQL Server.

SELECT COLUMN_NAME(S)
FROM TABLE_1, TABLE_2,TABLE_N
WHERE TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN;

Example 1

Let’s consider the two tables, EmployeeDetails and Department.

Table-1: EmployeeDetails Table

Table-2: Department

Now, we will execute EQUI JOIN operation in the SQL Server

Select E1.EmployeeName, D1.DepartmentName
From EmployeeDetails as E1, DepartmentName as D1
Where E1.DepartmentId. = D1.DepartmentId;

Now the output is below,

Alternate Way to Use EQUI JOIN

We can also execute the query in another way to get the same results. Now, Let us execute that statement, which gives the same result.

Select E1.EmployeeName, D1.DepartmentName
From EmployeeDetails as E1 join Department as D1
Where E1.DepartmentId. = D1.DepartmentId;

SQL Server Non-EQUI JOIN

It uses a comparison operator other than equal symbol (<,<=,>,>=).

Syntax

The syntax for Non-EQUI JOIN syntax is given below,

SELECT * 
FROM TABLE_1,TABLE_2
WHERE TABLE_1.COMMON_COLUMN [ < | > | <= | >= ] TABLE_2.COMMON_COLUMN;

EQUI JOIN with Conditions

Let’s see an example of EQUI JOIN with conditions for the two tables OrderList and CustomerList.

Table-1: OrderList

Table-2: CustomerList

This query filters the results to include only those Customers who bought Laptop.

SELECT CustomerList.CustomerID, CustomerList.FirstName, CustomerList.LastName, OrderList.OrderName
FROM CustomerList
INNER JOIN OrderList
ON CustomerList.CustomerID = OrderList.OrderID
WHERE OrderList.OrderName = 'Laptop';

EQUI JOIN with Aggregation

Here, we will find the number of Customers who bought and how many orders.

SELECT OrderList.OrderName, COUNT(CustomerList.CustomerID) AS CustomerNumber
FROM CustomerList
INNER JOIN OrderList
ON CustomerList.CustomerID = OrderList.OrderID
GROUP BY Orderlist.OrderName;

SQL Server EQUI JOIN Count

Now, we will see how many names concerning CustomerList in SQL Table.

SELECT COUNT(CustomerList.FirstName) AS Count,
CustomerList.FirstName
FROM CustomerList,OrderList
WHERE CustomerList.CustomerId = OrderList.OrderId
GROUP BY CustomerList.FirstName;

SQL Server Equi Join Order By

Our SELECT statement uses the EQUI JOIN clause to retrieve all records of the OrderName  and FirstName from both tables: OrderList and CustomerList

SELECT OrderList.OrderName,
CustomerList.FirstName
FROM CustomerList,OrderList
ORDER BY CustomerList.CustomerId DESC;

Conclusion

Hopefully, you now understand how to use the SQL Server EQUI JOIN on tables in the query.

You may also like to read the following SQL Server tutorials.

• What is a View in SQL Server
• How to delete a view in SQL Server
• What is SELF JOIN in SQL Server?