| # some.iq - Queries with quantifiers SOME (aka ANY) and ALL |
| # |
| # Licensed to the Apache Software Foundation (ASF) under one or more |
| # contributor license agreements. See the NOTICE file distributed with |
| # this work for additional information regarding copyright ownership. |
| # The ASF licenses this file to you under the Apache License, Version 2.0 |
| # (the "License"); you may not use this file except in compliance with |
| # the License. You may obtain a copy of the License at |
| # |
| # http://www.apache.org/licenses/LICENSE-2.0 |
| # |
| # Unless required by applicable law or agreed to in writing, software |
| # distributed under the License is distributed on an "AS IS" BASIS, |
| # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| # See the License for the specific language governing permissions and |
| # limitations under the License. |
| # |
| !use scott |
| !set expand false |
| !set outputformat mysql |
| |
| # =ANY |
| select * from "scott".emp |
| where empno = any (select empno from "scott".emp); |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| (14 rows) |
| |
| !ok |
| |
| # Both sides NOT NULL |
| select * from "scott".emp |
| where empno > any (select deptno from "scott".dept); |
| |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| (14 rows) |
| |
| !ok |
| |
| # ANY; left side NOT NULL, right side nullable. |
| select * from "scott".emp |
| where sal > any (select comm from "scott".emp); |
| |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| (14 rows) |
| |
| !ok |
| |
| # ALL; left side NOT NULL, right side nullable. |
| select * from "scott".emp |
| where sal > all (select comm from "scott".emp); |
| |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| (0 rows) |
| |
| !ok |
| |
| # Previous, as scalar sub-query |
| select *, sal > all (select comm from "scott".emp) as x |
| from "scott".emp; |
| |
| +-------+--------+-----------+------+------------+---------+---------+--------+-------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X | |
| +-------+--------+-----------+------+------------+---------+---------+--------+-------+ |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | false | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | false | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | false | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | false | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | false | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | false | |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | | |
| +-------+--------+-----------+------+------------+---------+---------+--------+-------+ |
| (14 rows) |
| |
| !ok |
| EnumerableCalc(expr#0..10=[{inputs}], expr#11=[0], expr#12=[=($t9, $t11)], expr#13=[>($t9, $t10)], expr#14=[null:BOOLEAN], expr#15=[<=($t5, $t8)], expr#16=[IS NOT TRUE($t15)], expr#17=[AND($t13, $t14, $t16)], expr#18=[>($t5, $t8)], expr#19=[<=($t9, $t10)], expr#20=[AND($t18, $t16, $t19)], expr#21=[OR($t12, $t17, $t20)], proj#0..7=[{exprs}], X=[$t21]) |
| EnumerableNestedLoopJoin(condition=[true], joinType=[inner]) |
| EnumerableTableScan(table=[[scott, EMP]]) |
| EnumerableAggregate(group=[{}], m=[MAX($6)], c=[COUNT()], d=[COUNT($6)]) |
| EnumerableTableScan(table=[[scott, EMP]]) |
| !plan |
| |
| # NOT SOME; left side NOT NULL, right side nullable; converse of previous query. |
| select * from "scott".emp |
| where not sal <= some (select comm from "scott".emp); |
| |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| (0 rows) |
| |
| !ok |
| |
| # Similar, as scalar sub-query. |
| select *, sal <= some (select comm from "scott".emp) as x |
| from "scott".emp; |
| |
| +-------+--------+-----------+------+------------+---------+---------+--------+------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X | |
| +-------+--------+-----------+------+------------+---------+---------+--------+------+ |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | true | |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | true | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | true | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | true | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | true | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | true | |
| +-------+--------+-----------+------+------------+---------+---------+--------+------+ |
| (14 rows) |
| |
| !ok |
| |
| # Sub-query is empty, so "< all" is trivially true. Even for null comm. |
| select * from "scott".emp |
| where comm < all (select comm from "scott".emp where 1 = 0) |
| order by empno; |
| |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| (14 rows) |
| |
| !ok |
| |
| # If sub-query is empty, "< some" is trivially false. Even for null comm. |
| select * from "scott".emp |
| where comm < some (select comm from "scott".emp where 1 = 0); |
| |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| (0 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal > all (select comm from "scott".emp where comm <> null); |
| |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| (14 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal > all(500, 2000); |
| |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| (6 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal > all (4000, 2000); |
| |
| +-------+-------+-----------+-----+------------+---------+------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+-------+-----------+-----+------------+---------+------+--------+ |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| +-------+-------+-----------+-----+------------+---------+------+--------+ |
| (1 row) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal > some (4000, 2000); |
| |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| (6 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal > any (4000, 2000); |
| |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| (6 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal > (select sal * 2 from "scott".emp); |
| more than one value in agg SINGLE_VALUE |
| !error |
| |
| select * from "scott".emp |
| where sal > any (select sal * 2 from "scott".emp) |
| order by sal desc; |
| |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| (6 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal < all (select sal * 2 from "scott".emp) |
| order by sal desc; |
| |
| +-------+--------+----------+------+------------+---------+---------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+--------+----------+------+------------+---------+---------+--------+ |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
| +-------+--------+----------+------+------------+---------+---------+--------+ |
| (7 rows) |
| |
| !ok |
| |
| # Equivalent to previous |
| select r.*, |
| not case when m is not null then r.sal >= m |
| when c > 0 then null |
| else false end as c |
| from (select min(sal * 2) as m, count(*) as c from "scott".emp) |
| cross join "scott".emp as r |
| order by sal desc; |
| +-------+--------+-----------+------+------------+---------+---------+--------+-------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | C | |
| +-------+--------+-----------+------+------------+---------+---------+--------+-------+ |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | false | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | false | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | false | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | false | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | false | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | false | |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | false | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | true | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | true | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | true | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | true | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | true | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | true | |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | true | |
| +-------+--------+-----------+------+------------+---------+---------+--------+-------+ |
| (14 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal < all (select comm * 2 from "scott".emp); |
| |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| (0 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal < any (select comm * 2 from "scott".emp) |
| order by empno; |
| |
| +-------+--------+----------+------+------------+---------+---------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+--------+----------+------+------------+---------+---------+--------+ |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
| +-------+--------+----------+------+------------+---------+---------+--------+ |
| (9 rows) |
| |
| !ok |
| |
| # Oracle gives error, but I believe the statement is valid. |
| # It should return all rows. |
| select * from "scott".emp |
| where sal < any (select comm * 2 from "scott".emp) is unknown; |
| |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| (5 rows) |
| |
| !ok |
| |
| # Oracle gives error, but I believe the statement is valid and result is correct. |
| select *, sal > all(select comm from "scott".emp) as x from "scott".emp; |
| |
| +-------+--------+-----------+------+------------+---------+---------+--------+-------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | X | |
| +-------+--------+-----------+------+------------+---------+---------+--------+-------+ |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | false | |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | false | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | false | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | false | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | false | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | false | |
| +-------+--------+-----------+------+------------+---------+---------+--------+-------+ |
| (14 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal > all (select comm from "scott".emp); |
| |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| +-------+-------+-----+-----+----------+-----+------+--------+ |
| (0 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal > any (select comm from "scott".emp); |
| |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| (14 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal > any (select comm from "scott".emp where comm < 1000); |
| |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| (14 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal > any (select comm from "scott".emp where comm < 2000); |
| |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| (14 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal > any (select comm * 2 from "scott".emp where comm < 2000); |
| |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | |
| | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
| | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | |
| | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | |
| +-------+--------+-----------+------+------------+---------+---------+--------+ |
| (14 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal > all (select comm * 2 from "scott".emp where comm < 2000); |
| |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| +-------+-------+-----------+------+------------+---------+------+--------+ |
| (5 rows) |
| |
| !ok |
| |
| select * from "scott".emp |
| where sal > all (select comm from "scott".emp where comm is not null); |
| |
| +-------+--------+-----------+------+------------+---------+--------+--------+ |
| | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | |
| +-------+--------+-----------+------+------------+---------+--------+--------+ |
| | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | |
| | 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 | |
| | 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | |
| | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 | |
| | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 | |
| | 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 | |
| | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | |
| | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 | |
| +-------+--------+-----------+------+------------+---------+--------+--------+ |
| (8 rows) |
| |
| !ok |
| |
| # End some.iq |
| |
