Sometimes there is a need to compare one value to many others. In Oracle SQL or PL/SQL for this purpose you can use Group Comparisons. Syntax for this is:
expr { = | != | < | > | <> | <= | >= } {ANY | SOME | ALL} (expr, …, expr) , for example
X = ANY (1,2,3) .
You can use subquery instead of expression list. For full syntax go to
Oracle Documentation.
Lets see how it works. First create sample table:
SQL> CREATE TABLE T1 (N1 NUMBER PRIMARY KEY, TEXT VARCHAR2(10));
Table created.
SQL> INSERT INTO T1 SELECT LEV, LEV
2 FROM (SELECT LEVEL LEV FROM DUAL CONNECT BY LEVEL < 10);
9 rows created.
SQL> COMMIT;
Commit complete.
Then do some queries
SQL> SELECT * FROM T1 WHERE N1 < ANY (4,6);
N1 TEXT
---------- ----------
1 1
2 2
3 3
4 4
5 5
SQL> SELECT * FROM T1 WHERE N1 < SOME (4,6);
N1 TEXT
---------- ----------
1 1
2 2
3 3
4 4
5 5
SQL> SELECT * FROM T1 WHERE N1 < ALL (4,6);
N1 TEXT
---------- ----------
1 1
2 2
3 3
And lets see what will happen if there is NULL in list:
SQL> SELECT * FROM T1 WHERE N1 < ANY (4,6, NULL);
N1 TEXT
---------- ----------
1 1
2 2
3 3
4 4
5 5
SQL> SELECT * FROM T1 WHERE N1 < SOME (4,6, NULL);
N1 TEXT
---------- ----------
1 1
2 2
3 3
4 4
5 5
SQL> SELECT * FROM T1 WHERE N1 < ALL (4,6, NULL);
no rows selected
So we can see that ANY is equivalent to SOME. These Grouping conditions are in fact rewritten to simple conditions. ANY is rewritten to many comparisons with logical OR,
a < ANY (b,c,d) is rewritten to
(a<b OR a<c OR a<d) while ALL is rewritten to AND statement
a < ALL(b,c,d) becomes
(a<b AND a<c AND a<d).
We can observe that
= ANY is equivalent to
IN operator.
SQL> SELECT * FROM T1 WHERE N1 = ANY (4,6, NULL);
N1 TEXT
---------- ----------
4 4
6 6
SQL> SELECT * FROM T1 WHERE N1 IN (4,6, NULL);
N1 TEXT
---------- ----------
4 4
6 6
Also observe that
!= ALL is equivalent to
NOT IN operator.
SQL> SELECT * FROM T1 WHERE N1 != ALL (4,6);
N1 TEXT
---------- ----------
1 1
2 2
3 3
5 5
7 7
8 8
9 9
7 rows selected.
SQL> SELECT * FROM T1 WHERE N1 NOT IN (4,6);
N1 TEXT
---------- ----------
1 1
2 2
3 3
5 5
7 7
8 8
9 9
7 rows selected.
SQL> SELECT * FROM T1 WHERE N1 != ALL (4,6, NULL);
no rows selected
SQL> SELECT * FROM T1 WHERE N1 NOT IN (4,6, NULL);
no rows selected
Take a look at trick with joining many tables based on the same key. Lets create more tables
SQL> CREATE TABLE T2 (N2 NUMBER PRIMARY KEY, TEXT VARCHAR2(10));
Table created.
SQL> INSERT INTO T2 SELECT * FROM T1;
9 rows created.
SQL> CREATE TABLE T3 (N3 NUMBER PRIMARY KEY, TEXT VARCHAR2(10));
Table created.
SQL> INSERT INTO T3 SELECT * FROM T1;
9 rows created.
SQL> COMMIT;
Commit complete.
And simple join:
SQL> SELECT N1, N2, N3 FROM T1, T2, T3 WHERE N1 = ALL (N2,N3);
N1 N2 N3
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
4 4 4
5 5 5
6 6 6
7 7 7
8 8 8
9 9 9
9 rows selected.
Is it really evaluated in proper way?
SQL> set autotrace traceonly;
SQL> SELECT N1, N2, N3 FROM T1, T2, T3 WHERE N1 = ALL (N2,N3);
9 rows selected.
Execution Plan
----------------------------------------------
Plan hash value: 3348756600
---------------------------------------------- ...
| Id | Operation | Name | ...
---------------------------------------------- ...
| 0 | SELECT STATEMENT | | ...
| 1 | NESTED LOOPS | | ...
| 2 | NESTED LOOPS | | ...
| 3 | INDEX FAST FULL SCAN| SYS_C004041 | ...
|* 4 | INDEX UNIQUE SCAN | SYS_C004042 | ...
|* 5 | INDEX UNIQUE SCAN | SYS_C004043 | ...
---------------------------------------------- ...
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("N1"="N2")
5 - access("N1"="N3")
So we can observe that it is really rewritten and oracle uses indexes in optimal way.
Hope you find it useful
Paweł
