What is Non Equi Join in SQL Server?

 Updated on October 28, 2014     VIKRAM     734

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 EmpSalaryGrade 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:

If you enjoyed this post, please like (or) share this article.


Comments