DBA Hub

📋Steps in this guide1/3

ALL, ANY and SOME Comparison Conditions in SQL

A quick look at some comparison conditions you may not be used to seeing in SQL against an Oracle database.

oracle miscconfigurationintermediate
by OracleDba
13 views
1

ALL

The comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery. When the condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with operators, as shown below. When the condition is followed by a subquery, the optimizer performs a two-step transformation as shown below. Assuming subqueries don't return zero rows, the following statements can be made for both list and subquery versions: - "x = ALL (...)": The value must match all the values in the list to evaluate to TRUE. - "x != ALL (...)": The value must not match any values in the list to evaluate to TRUE. - "x > ALL (...)": The value must be greater than the biggest value in the list to evaluate to TRUE. - "x < ALL (...)": The value must be smaller than the smallest value in the list to evaluate to TRUE. - "x >= ALL (...)": The value must be greater than or equal to the biggest value in the list to evaluate to TRUE. - "x <= ALL (...)": The value must be smaller than or equal to the smallest value in the list to evaluate to TRUE. If a subquery returns zero rows, the condition evaluates to TRUE. In the following example, the subquery returns zero rows, which means the whole expression "sal > ALL (zero rows)" evaluates to TRUE, so all rows are displayed.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
SELECT empno, sal
FROM   emp
WHERE  sal > ALL (2000, 3000, 4000);

     EMPNO        SAL
---------- ----------
      7839       5000

SQL>

-- Transformed to equivalent statement without ALL.

SELECT empno, sal
FROM   emp
WHERE  sal > 2000 AND sal > 3000 AND sal > 4000;

     EMPNO        SAL
---------- ----------
      7839       5000

SQL>

SELECT e1.empno, e1.sal
FROM   emp e1
WHERE  e1.sal > ALL (SELECT e2.sal
                     FROM   emp e2
                     WHERE  e2.deptno = 20);

     EMPNO        SAL
---------- ----------
      7839       5000

SQL>

-- Transformed to equivalent statement using ANY.

SELECT e1.empno, e1.sal
FROM   emp e1
WHERE  NOT (e1.sal <= ANY (SELECT e2.sal
                           FROM emp e2
                           WHERE e2.deptno = 20));

     EMPNO        SAL
---------- ----------
      7839       5000

SQL>

-- Transformed to equivalent statement without ANY.

SELECT e1.empno, e1.sal
FROM   emp e1
WHERE  NOT EXISTS (SELECT e2.sal
                   FROM emp e2
                   WHERE e2.deptno = 20
                   AND   e1.sal <= e2.sal);

     EMPNO        SAL
---------- ----------
      7839       5000

SQL>

-- The following query returns zero rows.
SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100;

no rows selected

SQL>

-- Place it in the subquery of an ALL and the 
-- condition "sal > ALL (zero rows)" evaluates to TRUE
-- so all rows are displayed.
SELECT e1.empno, e1.sal
FROM   emp e1
WHERE  e1.sal > ALL (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100);

     EMPNO        SAL
---------- ----------
      7369        800
      7900        950
      7876       1100
      7521       1250
      7654       1250
      7934       1300
      7844       1500
      7499       1600
      7782       2450
      7698       2850
      7566       2975
      7788       3000
      7902       3000
      7839       5000

SQL>
2

ANY

The comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a list or subquery. When the condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together with operators, as shown below. When the condition is followed by a subquery, the optimizer performs a single transformation as shown below. Assuming subqueries don't return zero rows, the following statements can be made for both list and subquery versions: - "x = ANY (...)": The value must match one or more values in the list to evaluate to TRUE. - "x != ANY (...)": The value must not match one or more values in the list to evaluate to TRUE. - "x > ANY (...)": The value must be greater than the smallest value in the list to evaluate to TRUE. - "x < ANY (...)": The value must be smaller than the biggest value in the list to evaluate to TRUE. - "x >= ANY (...)": The value must be greater than or equal to the smallest value in the list to evaluate to TRUE. - "x <= ANY (...)": The value must be smaller than or equal to the biggest value in the list to evaluate to TRUE. If a subquery returns zero rows, the condition evaluates to FALSE. In the following example, the subquery returns zero rows, which means the whole expression "sal > ANY (zero rows)" evaluates to FALSE, so no rows are displayed.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
SELECT empno, sal
FROM   emp
WHERE  sal > ANY (2000, 3000, 4000);

     EMPNO        SAL
---------- ----------
      7566       2975
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7902       3000

SQL>

-- Transformed to equivalent statement without ANY.

SELECT empno, sal
FROM   emp
WHERE  sal > 2000 OR sal > 3000 OR sal > 4000;

     EMPNO        SAL
---------- ----------
      7566       2975
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7902       3000

SQL>

SELECT e1.empno, e1.sal
FROM   emp e1
WHERE  e1.sal > ANY (SELECT e2.sal
                     FROM   emp e2
                     WHERE  e2.deptno = 10);

     EMPNO        SAL
---------- ----------
      7839       5000
      7902       3000
      7788       3000
      7566       2975
      7698       2850
      7782       2450
      7499       1600
      7844       1500

SQL>

-- Transformed to equivalent statement without ANY.

SELECT e1.empno, e1.sal
FROM   emp e1
WHERE  EXISTS (SELECT e2.sal
               FROM emp e2
               WHERE e2.deptno = 10
               AND   e1.sal > e2.sal);

     EMPNO        SAL
---------- ----------
      7839       5000
      7902       3000
      7788       3000
      7566       2975
      7698       2850
      7782       2450
      7499       1600
      7844       1500

SQL>

-- The following query returns zero rows.
SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100;

no rows selected

SQL>

-- Place it in the subquery of an ANY and the 
-- condition "sal > ANY (zero rows)" evaluates to FALSE
-- so no rows are displayed.
SELECT e1.empno, e1.sal
FROM   emp e1
WHERE  e1.sal > ANY (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100);

no rows selected

SQL>
3

SOME

The and comparison conditions do exactly the same thing and are completely interchangeable. For more information see: - ALL, ANY and SOME Comparison Conditions in SQL - Comparison Conditions - How the CBO Evaluates the ANY or SOME Operator - How the CBO Evaluates the ALL Operator Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!