GreatSQL用户手册

§ Oracle兼容-语法-ANY/ALL

§ 1. 语法

expr  comp_op ANY/ALL ( expr,expression_list ) 

comp_op:
    =
   | !=
   | <>
   | <
   | >
   | <=
   | >=
1
2
3
4
5
6
7
8
9
10

§ 2. 定义和用法

GreatSQL兼容支持 ANY/ALL 语法对多个表达式进行比较。

ANY/ALL 是采用等价转换方式来实现的。

operator 转换
x = ANY(a,b,c) x IN(a,b,c)
x != ANY(a,b,c) x != a OR x != b OR x != c
x < ANY(a,b,c) x <= GREATEST(a,b,c)
x <= ANY(a,b,c) x <= GREATEST(a,b,c)
x > ANY(a,b,c) x > LEAST(a,b,c)
x >= ANY(a,b,c) x >= LEAST(a,b,c)
x = ALL(a,b,c) x = a AND x = b AND x= c
x != ALL(a,b,c) x NOT IN(a,b,c)
x < ALL(a,b,c) x < LEAST(a,b,c)
x <= ALL(a,b,c) x <= LEAST(a,b,c)
x > ALL(a,b,c) x > GREATEST(a,b,c)
x >= ALL(a,b,c) x >= GREATEST(a,b,c)

§ 3. Oracle兼容说明

GreatSQL原生支持ROW类型的比较，因为 IN 与 NOT IN 支持表达式查找，所以= ANY、!= ANY 也支持。

其他表达式和Oracle一致，都不支持。

§ 4. 示例

greatsql> CREATE TABLE t1 (
uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(50) NOT NULL,
salary INT NOT NULL,
deptno INT NOT NULL);

greatsql> INSERT INTO t1(name, salary, deptno) VALUES ('John',5000,50),
('Jane',6000,50),
('Bob',7000,60),
('Sue',8000,70);

greatsql> SELECT * FROM t1;
+-----+------+--------+--------+
| uid | name | salary | deptno |
+-----+------+--------+--------+
|   1 | John |   5000 |     50 |
|   2 | Jane |   6000 |     50 |
|   3 | Bob  |   7000 |     60 |
|   4 | Sue  |   8000 |     70 |
+-----+------+--------+--------+

greatsql> SELECT * FROM t1 WHERE salary = ANY (5000, 6000, 7000);
+-----+------+--------+--------+
| uid | name | salary | deptno |
+-----+------+--------+--------+
|   1 | John |   5000 |     50 |
|   2 | Jane |   6000 |     50 |
|   3 | Bob  |   7000 |     60 |
+-----+------+--------+--------+

greatsql> SELECT * FROM t1 WHERE salary = ALL (5000, 6000, 7000);
Empty set (0.00 sec)

greatsql> SELECT * FROM t1 WHERE salary != ALL (5000, 6000, 7000);
+-----+------+--------+--------+
| uid | name | salary | deptno |
+-----+------+--------+--------+
|   4 | Sue  |   8000 |     70 |
+-----+------+--------+--------+

greatsql> SELECT * FROM t1 WHERE salary != ANY (5000, 6000, 7000);
+-----+------+--------+--------+
| uid | name | salary | deptno |
+-----+------+--------+--------+
|   1 | John |   5000 |     50 |
|   2 | Jane |   6000 |     50 |
|   3 | Bob  |   7000 |     60 |
|   4 | Sue  |   8000 |     70 |
+-----+------+--------+--------+

greatsql> SELECT * FROM t1 WHERE salary < ALL (5000, 6000, 7000);
Empty set (0.00 sec)

greatsql> SELECT * FROM t1 WHERE salary < ANY (5000, 6000, 7000);
+-----+------+--------+--------+
| uid | name | salary | deptno |
+-----+------+--------+--------+
|   1 | John |   5000 |     50 |
|   2 | Jane |   6000 |     50 |
+-----+------+--------+--------+

greatsql> SELECT * FROM t1 WHERE salary > ALL (5000, 6000, 7000);
+-----+------+--------+--------+
| uid | name | salary | deptno |
+-----+------+--------+--------+
|   4 | Sue  |   8000 |     70 |
+-----+------+--------+--------+

greatsql> SELECT * FROM t1 WHERE salary > ANY (5000, 6000, 7000);
+-----+------+--------+--------+
| uid | name | salary | deptno |
+-----+------+--------+--------+
|   2 | Jane |   6000 |     50 |
|   3 | Bob  |   7000 |     60 |
|   4 | Sue  |   8000 |     70 |
+-----+------+--------+--------+

greatsql> SELECT * FROM t1 WHERE salary <= ALL (5000, 6000, 7000);
+-----+------+--------+--------+
| uid | name | salary | deptno |
+-----+------+--------+--------+
|   1 | John |   5000 |     50 |
+-----+------+--------+--------+

greatsql> SELECT * FROM t1 WHERE salary <= ANY (5000, 6000, 7000);
+-----+------+--------+--------+
| uid | name | salary | deptno |
+-----+------+--------+--------+
|   1 | John |   5000 |     50 |
|   2 | Jane |   6000 |     50 |
|   3 | Bob  |   7000 |     60 |
+-----+------+--------+--------+

greatsql> SELECT * FROM t1 WHERE salary >= ALL (5000, 6000, 7000);
+-----+------+--------+--------+
| uid | name | salary | deptno |
+-----+------+--------+--------+
|   3 | Bob  |   7000 |     60 |
|   4 | Sue  |   8000 |     70 |
+-----+------+--------+--------+

greatsql> SELECT * FROM t1 WHERE salary >= ANY (5000, 6000, 7000);
+-----+------+--------+--------+
| uid | name | salary | deptno |
+-----+------+--------+--------+
|   1 | John |   5000 |     50 |
|   2 | Jane |   6000 |     50 |
|   3 | Bob  |   7000 |     60 |
|   4 | Sue  |   8000 |     70 |
+-----+------+--------+--------+
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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110

greatsql-wx