exists in
select * from table t where exists (select 'x' from table where col=t.col);
select * from table t where col in (select col from table);
col值为null的记录不会被选出,返回的记录相同;
exists不排序,in排序;所以exists比in快;
-----------------------------------------------------------------------------------------------
.update
update table t set col=(select col from table where id=t.id) where exists (select 'x' from table wehre id=t.id);
1.子查询返回的记录数大于1时会报错,可以max(col)或min(col)解决;
2.子查询中含有主查询表字段时,按照主句表字段顺序更新;
比如id从1到100,执行的update语句的效果就是:
update table t set col=(select col from table where id=1) where id=1;
......
update table t set col=(select col from table wehre id=100) where id=100;
3.主句查询的条件是为了保证只更新主查询和子查询中id相等的记录;
比如子查询中没有id=2的记录,返回null,那么主查询中id=2的记录被更新为null;
采用条件后,如果子查询中没有id=2的记录,那么条件就不会让主查询中id=2的记录更新;
-----------------------------------------------------------------------------------------------
.delete
delete from table t where col
1.子查询返回的记录数大于1时会报错,可以max(col)或min(col)解决;
2.子查询中含有主查询表字段时,按照主句表字段顺序更新;
比如var从a到z,执行的delete语句的效果就是:
delete from table t where col...
delete from table t where col
3.特别注意:
运算符不会将主句col字段is null的记录来用比较;
var is null时记录会被忽略,因为null 不等于 null;null只能用 is 或 is not 判断
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8231934/viewspace-592677/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8231934/viewspace-592677/