sql in exists any all

查找ecp_wbs_workorder_info中work_order_id在

ecp_wbs_workorder_deal_info表中的数据

 

in 写法

 select * from ecp_wbs_workorder_info info where work_order_id  in

 

 

 

(
select  work_order_id from ecp_wbs_workorder_deal_info
)

exists写法

 

select * from ecp_wbs_workorder_info where   exists
(
select  * from ecp_wbs_workorder_deal_info d where  ecp_wbs_workorder_info.work_order_id=d.work_order_id
)

any 写法

 

 

select * from ecp_wbs_workorder_info info where work_order_id=any
(
select  work_order_id from ecp_wbs_workorder_deal_info
)

 

 

查找ecp_wbs_workorder_info中work_order_id不在

ecp_wbs_workorder_deal_info表中的数据

not in写法

 

select * from ecp_wbs_workorder_info info where work_order_id not in
(
select  work_order_id from ecp_wbs_workorder_deal_info
)

not  exists

写法

select * from ecp_wbs_workorder_info where not  exists
(
select  * from ecp_wbs_workorder_deal_info d where  ecp_wbs_workorder_info.work_order_id=d.work_order_id
)

 

<>all写法

 

select * from ecp_wbs_workorder_info info where work_order_id<>all
(
select  work_order_id from ecp_wbs_workorder_deal_info
)

 

 

>any(),大于()中最小的

>all(),大于()中最大的

 in exists效率

IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。