In this article i will explain what is Non Equi Join in SQL Server.
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.
Create Emp table and insert data into Emp table as follows:
Create table Emp
ManagerID int null
insert into Emp
select 101, 'AAA', 1000, 'Manager', null
select 102, 'BBB', 2000, 'Worker', 101
select 103, 'CCC', 3000, 'Worker', 101
select 104, 'DDD', 4000, 'Manager', null
select 105, 'EEE', 5000, 'Worker', 104
Create SalaryGrade table and insert data into SalaryGrade table as follows:
Create table SalaryGrade
insert into SalaryGrade
select 'A', 1000, 2999
select 'B', 3000, 4999
select 'C', 5000, 6999
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