SQL Server: Non Equi Join

 SQL Server   sbo   Updated on November 18, 2013 11:08 AM   VIKRAM   622  


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.


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
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
    Grade char(1),
    Lowest int,
    Highest int

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


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


  Related Articles
SQL Server: Equi Join
SQL Server: Cross Joins
SQL Server: Full Join (or) Full Outer Join
SQL Server: Joins
SQL Server: Right Join (or) Right Outer Join
SQL Server: Left Join (or) Left Outer Join
SQL Server: Inner Join (or) Join
SQL Server: How to get current week dates
SQL Server: Joins - Cartesian Product

  Recent Articles
Tips & Tricks: Drag and Drop code from Visual Studio Toolbox
Tips & Tricks: Remove Parameters and Reorder Parameters in Visual Studio
Tips & Tricks: Task List in Visual Studio
Tips & Tricks: Extract Method in Visual Studio
Tips & Tricks: Rectangular selection of code in Visual Studio
The major features added in C# in each release