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 ( 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
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
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