studentBoxOffice.in

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

Related Articles
Comments
Recent Articles

© 2012-13 by student Box Office. All Rights Reserved | Copyright | Terms of Use & Privacy Policy | Feedback