Oracle兼容-语法-ANY/ALL

Oracle兼容-语法-ANY/ALL

1. 语法

1. expr  comp_op ANY/ALL ( expr,expression_list )

3. comp_op:
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. 示例

1. greatsql> CREATE TABLE t1 (
2. uid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3. name VARCHAR(50) NOT NULL,
4. salary INT NOT NULL,
5. deptno INT NOT NULL);

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

12. greatsql> SELECT * FROM t1;
13. +-----+------+--------+--------+
14. | uid | name | salary | deptno |
15. +-----+------+--------+--------+
16. |   1 | John |   5000 |     50 |
17. |   2 | Jane |   6000 |     50 |
18. |   3 | Bob  |   7000 |     60 |
19. |   4 | Sue  |   8000 |     70 |
20. +-----+------+--------+--------+

22. greatsql> SELECT * FROM t1 WHERE salary = ANY (5000, 6000, 7000);
23. +-----+------+--------+--------+
24. | uid | name | salary | deptno |
25. +-----+------+--------+--------+
26. |   1 | John |   5000 |     50 |
27. |   2 | Jane |   6000 |     50 |
28. |   3 | Bob  |   7000 |     60 |
29. +-----+------+--------+--------+

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

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

41. greatsql> SELECT * FROM t1 WHERE salary != ANY (5000, 6000, 7000);
42. +-----+------+--------+--------+
43. | uid | name | salary | deptno |
44. +-----+------+--------+--------+
45. |   1 | John |   5000 |     50 |
46. |   2 | Jane |   6000 |     50 |
47. |   3 | Bob  |   7000 |     60 |
48. |   4 | Sue  |   8000 |     70 |
49. +-----+------+--------+--------+

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

54. greatsql> SELECT * FROM t1 WHERE salary < ANY (5000, 6000, 7000);
55. +-----+------+--------+--------+
56. | uid | name | salary | deptno |
57. +-----+------+--------+--------+
58. |   1 | John |   5000 |     50 |
59. |   2 | Jane |   6000 |     50 |
60. +-----+------+--------+--------+

62. greatsql> SELECT * FROM t1 WHERE salary > ALL (5000, 6000, 7000);
63. +-----+------+--------+--------+
64. | uid | name | salary | deptno |
65. +-----+------+--------+--------+
66. |   4 | Sue  |   8000 |     70 |
67. +-----+------+--------+--------+

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

78. greatsql> SELECT * FROM t1 WHERE salary <= ALL (5000, 6000, 7000);
79. +-----+------+--------+--------+
80. | uid | name | salary | deptno |
81. +-----+------+--------+--------+
82. |   1 | John |   5000 |     50 |
83. +-----+------+--------+--------+

85. greatsql> SELECT * FROM t1 WHERE salary <= ANY (5000, 6000, 7000);
86. +-----+------+--------+--------+
87. | uid | name | salary | deptno |
88. +-----+------+--------+--------+
89. |   1 | John |   5000 |     50 |
90. |   2 | Jane |   6000 |     50 |
91. |   3 | Bob  |   7000 |     60 |
92. +-----+------+--------+--------+

94. greatsql> SELECT * FROM t1 WHERE salary >= ALL (5000, 6000, 7000);
95. +-----+------+--------+--------+
96. | uid | name | salary | deptno |
97. +-----+------+--------+--------+
98. |   3 | Bob  |   7000 |     60 |
99. |   4 | Sue  |   8000 |     70 |
100. +-----+------+--------+--------+

102. greatsql> SELECT * FROM t1 WHERE salary >= ANY (5000, 6000, 7000);
103. +-----+------+--------+--------+
104. | uid | name | salary | deptno |
105. +-----+------+--------+--------+
106. |   1 | John |   5000 |     50 |
107. |   2 | Jane |   6000 |     50 |
108. |   3 | Bob  |   7000 |     60 |
109. |   4 | Sue  |   8000 |     70 |
110. +-----+------+--------+--------+