select * from tablename where exists(select * from tablename where ……)select * from tablename where cardid in (select cardid from tablname)
并运算 select c1,c2 from t1 union all select c1,c2 from t2差: c1-c2:select * from t1 where not exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)c2-c1:select * from t2 where not exists(select 1 from t1 where t1.c1=t2.c1 and t1.c2=t2.c2)交: select * from t1 where exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2) )
select c1,c2 from t1
union all
select c1,c2 from t2差:
c1-c2:select * from t1 where not exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)c2-c1:select * from t2 where not exists(select 1 from t1 where t1.c1=t2.c1 and t1.c2=t2.c2)交:
select * from t1 where exists(select 1 from t2 where t1.c1=t2.c1 and t1.c2=t2.c2)
)
放在WHERE后面,就是说只要你的exists后面的语句有值,就表示为逻辑真,否则为假!
和IN有很大的区别,IN后面的参数通常是具体的值,但不是逻辑值,通常IN理解为包含。
EXISTS只是作为简单的逻辑判断,不对其后的SELECT语句作穷举罗列,通常只要找到第一个符合条件的值便不再往下继续搜索,条件的值这时便已经为真了!
EXISTS 和 IN 都返回相同的结果信息