翻遍了微軟歷年T-SQL 2000/2005/2008/R2/2012/2014,還有最新的SQL Server 2016的20761A 第四章節,隻字不提到Nonequijoins 查詢。
基本上它很簡單,就是將JOIN 過程,搭配BETWEEN 關鍵字
找出區間值對應的代碼。 詳細可以看以下範例
use tempdb
go
--薪資等級表
if object_id('SALARY_GRADES') is not null
drop table SALARY_GRADES
go
--員工薪資
if object_id('EMPLOYEES') is not null
drop table EMPLOYEES
go
--薪資等級表
CREATE TABLE SALARY_GRADES
(GRADE_LEVEL char(1), --薪資等級
LOW_SAL int, --該薪資等級最低數字
HIGHEST_SAL int) --薪資等級表最高數字
go
--員工資料表
CREATE TABLE EMPLOYEES
(LAST_NAME varchar(30),
SALARY int)
go
--薪資等級表
insert into SALARY_GRADES(GRADE_LEVEL,LOW_SAL,HIGHEST_SAL)
values('A',0,20008) ,('B',20009,20100),('C',20101,21000) ,
('D',21001,21900),('E',21901,22800),('F',22801,24000),
('G',24001,25200)
go
--新增員工資料
insert into EMPLOYEES(LAST_NAME,SALARY )
values('ADA' ,20010),('JULIA' ,20500),
('JEFF' ,55800),('ANNA' ,21905),
('AIRES',22800),('ARTHUR',22000),
('HENRY',23000),('THOMAS',24000)
go
select * from SALARY_GRADES
select * from EMPLOYEES
--inner join on between (non equal join)
--inner join on a.c1=b.c1
select a.*,b.GRADE_LEVEL as level
from EMPLOYEES a left outer join SALARY_GRADES b
on a.SALARY between b.LOW_SAL and b.HIGHEST_SAL
go
select a.*,isnull(b.GRADE_LEVEL,a.SALARY) as level
from EMPLOYEES a left outer join SALARY_GRADES b
on a.SALARY >= b.LOW_SAL and a.SALARY <= b.HIGHEST_SAL
go