现在有A、B、C三张表A(id,.....) A表的数据量:700W
B(id,.....) B表的数据量:200W
C(id,.....) C表的数据量:200Wid都是这三张表的主键1、查出A表的所有信息:条件id同时存在于B、C表select * from A where id in (select id from B where id in(select id from C));2、查出A表的所有信息:条件id存在于B但是不存在于C表select * from A where id in(select id from B where id not in(select id from c));这两句SQL语句应该如何优化?
B(id,.....) B表的数据量:200W
C(id,.....) C表的数据量:200Wid都是这三张表的主键1、查出A表的所有信息:条件id同时存在于B、C表select * from A where id in (select id from B where id in(select id from C));2、查出A表的所有信息:条件id存在于B但是不存在于C表select * from A where id in(select id from B where id not in(select id from c));这两句SQL语句应该如何优化?
select * from A where id in (select id from B where id in(select id from C));
---->
select * from a where exists (select id from (select id form b intersect select id from c) b where b.id =a.id)select * from A where id in(select id from B where id not in(select id from c));
----->
select * from a where exists (select id from (select id form b minus select id from c) b where b.id =a.id)
-----这样试下呢上面看错了。。以为是别的写法。。呵呵见笑了。。
1>:
select * from A where exists (select 1 from B where exists (select 1 from C where c.id = b.id) and a.id=b.id)
2>:
select * from A where exists (select 1 from B where not exists (select 1 from C where c.id = b.id) and a.id=b.id)
抛开上面我说的那个方法,有没有比用exsit、not exsit更好的方法?
只是,从语句的逻辑角度,是否可以考虑一下,将这两条语句合并为一个可在直接查出A、B关联(内联)、再与C的关联(左联),这样的话,一个SQL语句就可以了
--1、查出A表的所有信息:条件id同时存在于B、C表select * from A
where exists(select null from
(select id from B
where exists(select null from C
where C.id =B.id)) t
where t.id=A.id)
--2、查出A表的所有信息:条件id存在于B但是不存在于C表
select * from A
where exists(select null from
(select id from B
where not exists(select null from C
where C.id =B.id)) t
where t.id=A.id)
select A.* from A,B,C where a.id = b.id and b.id = c.id; select * from A
where exists(select null from
(select id from B
where exists(select null from C
where C.id =B.id)) t
where t.id=A.id)
--上面的两种,那种效率高?
用exists还是内连接??