test1
all_no
1234566
1234567
1234568
1234569
1234570
1234571test2
all_no flag
1234566 0
1234567 1
1234568 0要查询出test2 中flag没有all_no的记录
结果应该是
1234567
1234569
1234570
1234571
哪种效率高
all_no
1234566
1234567
1234568
1234569
1234570
1234571test2
all_no flag
1234566 0
1234567 1
1234568 0要查询出test2 中flag没有all_no的记录
结果应该是
1234567
1234569
1234570
1234571
哪种效率高
all_no
1234566
1234567
1234568
1234569
1234570
1234571 test2
all_no flag
1234566 0
1234567 1
1234568 0 要查询出test2 中flag没有等于0的all_no的记录
结果应该是
1234567
1234569
1234570
1234571
哪种效率高
select test1.*
from test1
join test2 on test1.all_no=test2.all_no and test2.flag=1
where test2.all_no is null
from test1
join test2 on test1.all_no=test2.all_no and test2.flag=0
where test2.all_no is null
INSERT @TEST1
SELECT 1234566 UNION ALL
SELECT 1234567 UNION ALL
SELECT 1234568 UNION ALL
SELECT 1234569 UNION ALL
SELECT 1234570 UNION ALL
SELECT 1234571 DECLARE @TEST2 TABLE ( all_no INT,flag INT)
INSERT @TEST2 SELECT 1234566 , 0 UNION ALL
SELECT 1234567 , 1 UNION ALL
SELECT 1234568 , 0 SELECT * FROM @TEST1 T WHERE NOT EXISTS(SELECT 1 FROM @TEST2 WHERE all_no=T.all_no AND flag=0)
(所影响的行数为 6 行)
(所影响的行数为 3 行)all_no
-----------
1234567
1234569
1234570
1234571(所影响的行数为 4 行)