What is Non Equi Join in SQL Server

Introduction:

In this article i will explain what is Non Equi Join in SQL Server.

Description:

In previous articles i explained what are joins, what is INNER JOIN in SQL Server, what is LEFT JOIN (or) LEFT OUTER JOIN in SQL Server,  what is RIGHT JOIN (or) RIGHT OUTER JOIN, what is FULL JOIN or FULL OUTER JOIN, and what is CROSS JOIN, and what is EQUI JOIN in SQL Server. Now i will explain what is Non Equi Join in SQL Server.

NON EQUI JOIN:

To retrieve data from two tables where no common column between two tables such thing is called Non Equi Join.

Example:

Create Emp table and insert data into Emp table as follows:

Create table Emp
(
    EmpID int,
    EmpName varchar(50),
    EmpSalary int,
    Designation varchar(50),
    ManagerID int null
)

insert into Emp
select 101, 'AAA', 1000, 'Manager', null
union
select 102, 'BBB', 2000, 'Worker', 101
union
select 103, 'CCC', 3000, 'Worker', 101
union
select 104, 'DDD', 4000, 'Manager', null
union
select 105, 'EEE', 5000, 'Worker', 104

Output:

Create SalaryGrade table and insert data into SalaryGrade table as follows:

Create table SalaryGrade (    Grade char(1),    Lowest int,    Highest int ) insert into SalaryGrade select 'A', 1000, 2999 union select 'B', 3000, 4999 union select 'C', 5000, 6999

Output:

How to use:

 Now we are retrieving Employee data with Grade. Here no common column present in both Emp, SalaryGrade tables.

select      E.EmpID, E.EmpName, E.EmpSalary, S.Grade
from        Emp E, SalaryGrade S
where       E.EmpSalary >= S.Lowest
and         E.EmpSalary <= S.Highest

Output: