§ 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
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
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
扫码关注微信公众号
