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. +-----+------+--------+--------+

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx