现在的问题是我要获得test1表中在test2表中不存在的name值(也就是lisi)。
除了用not in以外(数据量比较大性能不怎么好)。
还可以怎么写??? ------最佳解决方案-------------------- select name from test1
minus
select name from test2 ------其他解决方案--------------------
minus取差集,会排序,而且不忽略null吧
exists吧 ------其他解决方案-------------------- SELECT * FROM TEST1 T1 WHERE NOT EXISTS(SELECT 'X' FROM TEST2 T2 WHERE T1.NAME = T2.NAME) ------其他解决方案--------------------
with t1 as
(
select 'zhangsan' c1 from dual
union all
select 'lisi' c1 from dual
union all
select 'wangwu' c1 from dual
union all
select 'houliu' c1 from dual
),t2 as
(
select 'zhangsan' c2 from dual
union all
select 'wangwu' c2 from dual
union all
select 'zhaoliu' c2 from dual
)
select c1
from
(
select c1,c2
from t1 left join t2 on c1 = c2
)
where c2 is null