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行。