oracle where后怎么弄all,where 条件 = ALL

---特别注意相关子查询---------

DROP TABLE a;

CREATE TABLE a(ID number,NAME VARCHAR2(10));

DROP TABLE b;

CREATE TABLE b(ID number,NAME VARCHAR2(10));

INSERT INTO a VALUES(1,'a');

INSERT INTO a VALUES(2,'b');

INSERT INTO a VALUES(3,'c');

INSERT INTO b VALUES(4,'a');

INSERT INTO b VALUES(5,'b');

INSERT INTO b VALUES(6,'c');

INSERT INTO b VALUES(1,'a');

COMMIT;

--对于每一行,去子查询中匹配,只要没有查询到结果,则返回全部行,匹配的也返回,因此ALL=的关联子查询肯定返回所有行

SELECT * FROM a

WHERE (a.ID,a.NAME)

= ALL (SELECT b.ID,b.NAME FROM b WHERE a.ID=b.ID AND a.NAME=b.NAME);

--返回1行,any,some对于相关子查询,如果匹配不到则不返回,匹配到才返回,用于=类似于in,更复杂的是不等于的关联

SELECT * FROM a

WHERE (a.ID,a.NAME)

= ANY (SELECT b.ID,b.NAME FROM b WHERE a.ID=b.ID AND a.NAME=b.NAME);

--子查询返回结果没有匹配

SELECT * FROM a

WHERE a.ID

= ALL (SELECT b.ID FROM b);

--ALL,非关联子查询若不返回结果,返回主查询所有行

WITH t1 AS

(SELECT LEVEL ID FROM dual CONNECT BY LEVEL <3),

t2 AS

(SELECT 3 ID FROM dual)

SELECT ID

FROM t1

WHERE ID =ALL

(SELECT ID FROM t2 WHERE ID=1);

--id=2的匹配返回,id=2的不匹配,因为没有找到结果也返回

WITH t1 AS

(SELECT LEVEL ID FROM dual CONNECT BY LEVEL <3),

t2 AS

(SELECT 3 ID FROM dual UNION ALL SELECT 2 FROM dual)

SELECT ID

FROM t1

WHERE ID =ALL

(SELECT ID FROM t2 WHERE t1.ID=t2.ID);

--返回行,但是不匹配,不返回主查询结果

WITH t1 AS

(SELECT LEVEL ID FROM dual CONNECT BY LEVEL <3),

t2 AS

(SELECT 3 ID FROM dual)

SELECT ID

FROM t1

WHERE ID =ALL

(SELECT ID FROM t2);

--exists非相关的,如果子查询不返回行,也返回false,只要有一行那么就为真返回全部

WITH t1 AS

(SELECT LEVEL ID FROM dual CONNECT BY LEVEL <3),

t2 AS

(SELECT 3 ID FROM dual)

SELECT ID

FROM t1

WHERE EXISTS

(SELECT ID FROM t2 WHERE ID=2);

all的文档解释是:主表的每行与子表的所有行比较,如果匹配到则返回,如果子表无数据,则返回全部行,这里涉及到一个数学实现的问题

--例子:

DINGJUN123>select * from a;

ID NAME

---------- --------------------

1 a

2 b

3 c

10 e

已选择4行。

DINGJUN123>select * from b;

ID NAME

---------- --------------------

1 b

2 c

3 d

10 e

已选择4行。

DINGJUN123>select * from a where (id,name)= all(select id,name from b where b.id=10);

ID NAME

---------- --------------------

10 e

已选择 1 行。

--这个内部怎么实现的呢?采用NOT EXISTS相关子查询实现,拿主表的每行与从表比较,

--如果全部匹配到,子查询内部条件为FALSE,这样NOT EXISTS取反,则返回TRUE,选中结果,相反,

--如果子查询内部条件有一个为TRUE,应该返回FALSE,不选出。为了保障这个,ORACLE采用一个未公开的函数LNVVL,当然,11g已经公开了,请看

执行计划

----------------------------------------------------------

Plan hash value: 1049914119

-----------------------------------

| Id  | Operation          | Name |

-----------------------------------

|   0 | SELECT STATEMENT   |      |

|*  1 |  FILTER            |      |

|   2 |   TABLE ACCESS FULL| A    |

|*  3 |   TABLE ACCESS FULL| B    |

-----------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter( NOT EXISTS (SELECT 0 FROM "B" "B" WHERE "B"."ID"=10 AND

(LNNVL("B"."ID"=:B1) OR LNNVL("B"."NAME"=:B2))))

3 - filter("B"."ID"=10 AND (LNNVL("B"."ID"=:B1) OR

LNNVL("B"."NAME"=:B2)))

3  rows processed

DINGJUN123>select * from a where (id,name)= all(select id,name from b where b.id=10);

已选择 1 行。

执行计划

----------------------------------------------------------

Plan hash value: 1049914119

-----------------------------------

| Id  | Operation          | Name |

-----------------------------------

|   0 | SELECT STATEMENT   |      |

|*  1 |  FILTER            |      |

|   2 |   TABLE ACCESS FULL| A    |

|*  3 |   TABLE ACCESS FULL| B    |

-----------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter( NOT EXISTS (SELECT 0 FROM "B" "B" WHERE (LNNVL("ID"=:B1)

OR LNNVL("NAME"=:B2)) AND "B"."ID"=10))

3 - filter((LNNVL("ID"=:B1) OR LNNVL("NAME"=:B2)) AND "B"."ID"=10)

Note

-----

- rule based optimizer used (consider using cbo)

--改写

DINGJUN123>select * from a where not exists (select 0 from b where (lnnvl(a.id=b.id) or lnnvl(a

2  and b.id=10

3  );

已选择 1 行。

执行计划

----------------------------------------------------------

Plan hash value: 1049914119

-----------------------------------

| Id  | Operation          | Name |

-----------------------------------

|   0 | SELECT STATEMENT   |      |

|*  1 |  FILTER            |      |

|   2 |   TABLE ACCESS FULL| A    |

|*  3 |   TABLE ACCESS FULL| B    |

-----------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter( NOT EXISTS (SELECT 0 FROM "B" "B" WHERE "B"."ID"=10 AND

(LNNVL("B"."ID"=:B1) OR LNNVL("B"."NAME"=:B2))))

3 - filter("B"."ID"=10 AND (LNNVL("B"."ID"=:B1) OR

LNNVL("B"."NAME"=:B2)))

Note

-----

- rule based optimizer used (consider using cbo)

结果一样,这里是两个条件,主表的一行选两列与从表的两列比较,如果全部相等,那么LNNVL函数都是FALSE,那么NOT EXISTS的结果就为TRUE了,相反,有一个是TRUE,也就是不相等,那么LNNVL的结果就是TRUE,这样NOT EXISTS返回的是FALSE,符合了ALL的数学定义规则。这是ORACLE的内部实现机制。类似于:

NOT (condition1 OR condition2) =====>

NOT condition1 AND NOT condition2

NOT (condition1 AND condition2) =====>

NOT condition1 OR NOT condition2

-----------附录 LNNVL函数,LNNVL(Boolean),如果是TRUE则结果为FALSE,如果是FALSE或UNKNOWN,则结果为TRUE--------------

DINGJUN123>select * from dual where lnnvl(1=2);

DU

--

X

已选择 1 行。

DINGJUN123>select * from dual where lnnvl(2=2);

未选定行

DINGJUN123>select * from dual where lnnvl(1=null);

DU

--

X

已选择 1 行。

------------有了上面的知识,这个就好理解了

DINGJUN123>select * from a where (id,name)= all(select 1,'adsadsadsa' from dual where 1=0);

ID NAME

---------- --------------------

1 a

2 b

3 c

10 e

已选择4行。

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值