保持数据质量并通过JOIN中的两个OR语句加快查询速度

这是因为这2个查询左连接到table4。 但是在on子句中有cid 26的标准。

为了说明这一点，请看以下简化示例的输出：

--
-- sample data
--
create table tbl1 
(
    cid int primary key,
    pcode varchar(30) not null
);

create table tbl2
(
    id2 int identity(1,1) primary key,
    srscid int,
    pcode varchar(30) not null
);

insert into tbl1 (cid, pcode) values
(26, 'a'),
(31, 'b'),(32, 'c'),(33, 'd');

insert into tbl2 (pcode, srscid) values
('a', 26),('b', 26), 
('x', 31),('y', 32);

GO
受影响的8行
-- query 1
-- cid != 26, srscid = cid
--
select t1.*, t2.id2, t2.pcode as pcode2
from tbl1 t1
left join tbl2 t2
on (t2.srscid = t1.cid and t1.cid != 26)

GO
cid | pcode | id2 | pcode2
-：| ：---- | ---：：-----
 26 | 一个| 空 | 空值  
 31 | b | 3 | X     
 32 | c | 4 | ÿ     
 33 | d | 空 | 空值  
-- query 2
-- cid = 26, pcode
--
select t1.*, t2.id2, t2.pcode as pcode2
from tbl1 t1
left join tbl2 t2
on (t2.pcode = t1.pcode and t1.cid = 26)
GO
cid | pcode | id2 | pcode2
-：| ：---- | ---：：-----
 26 | 一个| 1 | 一种     
 31 | b | 空 | 空   
 32 | c | 空 | 空   
 33 | d | 空 | 空值  
-- query 3
-- cid != 26 or cid = 26
--
select t1.*, t2.id2, t2.pcode as pcode2
from tbl1 t1
left join tbl2 t2
on ((t2.srscid = t1.cid    and t1.cid != 26) or 
    (t2.pcode = t1.pcode and t1.cid = 26))

GO
cid | pcode | id2 | pcode2
-：| ：---- | ---：：-----
 26 | 一个| 1 | 一种     
 31 | b | 3 | X     
 32 | c | 4 | ÿ     
 33 | d | 空 | 空值  
-- query 4
-- union query 1 & 2
--
select t1.*, t2.id2, t2.pcode as pcode2
from tbl1 t1
left join tbl2 t2
on (t2.srscid = t1.cid and t1.cid != 26)
union
select t1.*, t2.id2, t2.pcode
from tbl1 t1
left join tbl2 t2
on (t2.pcode = t1.pcode and t1.cid = 26)

GO
cid | pcode | id2 | pcode2
-：| ：---- | ---：：-----
 26 | 一个| 空 | 空值  
 26 | 一个| 1 | 一种     
 31 | b | 空 | 空值  
 31 | b | 3 | X     
 32 | c | 空 | 空值  
 32 | c | 4 | ÿ     
 33 | d | 空 | 空值  
-- query 4
-- union with where clauses
--
select t1.*, t2.id2, t2.pcode as pcode2
from tbl1 t1
left join tbl2 t2
on t2.srscid = t1.cid 
where t1.cid != 26
union all
select t1.*, t2.id2, t2.pcode
from tbl1 t1
left join tbl2 t2
on t2.pcode = t1.pcode 
where t1.cid = 26
order by t1.cid

GO
cid | pcode | id2 | pcode2
-：| ：---- | ---：：-----
 26 | 一个| 1 | 一种     
 31 | b | 3 | X     
 32 | c | 4 | ÿ     
 33 | d | 空 | 空值  

db <> 在这里拨弄