…… category=2 or category=5 改 category in(2,5) 走索引 sql基础要求能不用or就不用or,尽可能用exists代替in表关联 SELECT * FROM inventory a WHERE EXISTS(SELECT 1 FROM organization WHERE xxxx=a.org) AND EXISTS(SELECT 1 FROM category_all WHERE xxxx=a.category);
我1楼已经说了 and比or的优先级高,按你这么改已经和原语句查询结果不一样了
这个单表查询 可以不用 exists的,直接用in ,语句如下 select * from INVENTORY where org in ('132','134','136','137','133','128') and category in (2,5)如果还慢的话,建议针对于 org 字段 和 category 建个 组合索引
上面刚说完还犯同样错误,看看下面的例子想想为啥只出来一条记录 SQL> with T as(select rownum rn from dual connect by rownum<=10) 2 select * from T 3 where rn=1 4 and rn=2 or rn=3; RN ---------- 3SQL>
照楼主那样写,and的优先级比or要高,条件判断的时候会先执行and 。 如果楼主的本意是想分别判断【org in ('132','134','136','137','133','128')】和【category=2 or category=5】,在AND的话, 可以采用表关联,用exists代替in: SELECT * FROM inventory a WHERE EXISTS(SELECT 1 FROM organization WHERE xxxx=a.org) AND EXISTS(SELECT 1 FROM category_all WHERE xxxx=a.category);
category=2 or category=5 改 category in(2,5) 走索引
sql基础要求能不用or就不用or,尽可能用exists代替in表关联
SELECT *
FROM inventory a
WHERE EXISTS(SELECT 1 FROM organization WHERE xxxx=a.org)
AND EXISTS(SELECT 1 FROM category_all WHERE xxxx=a.category);
select * from INVENTORY
where org in ('132','134','136','137','133','128')
and category in (2,5)如果还慢的话,建议针对于 org 字段 和 category 建个 组合索引
SQL> with T as(select rownum rn from dual connect by rownum<=10)
2 select * from T
3 where rn=1
4 and rn=2 or rn=3; RN
----------
3SQL>
如果楼主的本意是想分别判断【org in ('132','134','136','137','133','128')】和【category=2 or category=5】,在AND的话,
可以采用表关联,用exists代替in:
SELECT *
FROM inventory a
WHERE EXISTS(SELECT 1 FROM organization WHERE xxxx=a.org)
AND EXISTS(SELECT 1 FROM category_all WHERE xxxx=a.category);