subquery/exists-subquery/exists-basic.sql

0001 -- Tests EXISTS subquery support. Tests basic form
0002 -- of EXISTS subquery (both EXISTS and NOT EXISTS)
0003
0004 CREATE TEMPORARY VIEW EMP AS SELECT * FROM VALUES
0005   (100, "emp 1", date "2005-01-01", 100.00D, 10),
0006   (100, "emp 1", date "2005-01-01", 100.00D, 10),
0007   (200, "emp 2", date "2003-01-01", 200.00D, 10),
0008   (300, "emp 3", date "2002-01-01", 300.00D, 20),
0009   (400, "emp 4", date "2005-01-01", 400.00D, 30),
0010   (500, "emp 5", date "2001-01-01", 400.00D, NULL),
0011   (600, "emp 6 - no dept", date "2001-01-01", 400.00D, 100),
0012   (700, "emp 7", date "2010-01-01", 400.00D, 100),
0013   (800, "emp 8", date "2016-01-01", 150.00D, 70)
0014 AS EMP(id, emp_name, hiredate, salary, dept_id);
0015
0016 CREATE TEMPORARY VIEW DEPT AS SELECT * FROM VALUES
0017   (10, "dept 1", "CA"),
0018   (20, "dept 2", "NY"),
0019   (30, "dept 3", "TX"),
0020   (40, "dept 4 - unassigned", "OR"),
0021   (50, "dept 5 - unassigned", "NJ"),
0022   (70, "dept 7", "FL")
0023 AS DEPT(dept_id, dept_name, state);
0024
0025 CREATE TEMPORARY VIEW BONUS AS SELECT * FROM VALUES
0026   ("emp 1", 10.00D),
0027   ("emp 1", 20.00D),
0028   ("emp 2", 300.00D),
0029   ("emp 2", 100.00D),
0030   ("emp 3", 300.00D),
0031   ("emp 4", 100.00D),
0032   ("emp 5", 1000.00D),
0033   ("emp 6 - no dept", 500.00D)
0034 AS BONUS(emp_name, bonus_amt);
0035
0036 -- uncorrelated exist query
0037 -- TC.01.01
0038 SELECT *
0039 FROM   emp
0040 WHERE  EXISTS (SELECT 1
0041                FROM   dept
0042                WHERE  dept.dept_id > 10
0043                       AND dept.dept_id < 30);
0044
0045 -- simple correlated predicate in exist subquery
0046 -- TC.01.02
0047 SELECT *
0048 FROM   emp
0049 WHERE  EXISTS (SELECT dept.dept_name
0050                FROM   dept
0051                WHERE  emp.dept_id = dept.dept_id);
0052
0053 -- correlated outer isnull predicate
0054 -- TC.01.03
0055 SELECT *
0056 FROM   emp
0057 WHERE  EXISTS (SELECT dept.dept_name
0058                FROM   dept
0059                WHERE  emp.dept_id = dept.dept_id
0060                        OR emp.dept_id IS NULL);
0061
0062 -- Simple correlation with a local predicate in outer query
0063 -- TC.01.04
0064 SELECT *
0065 FROM   emp
0066 WHERE  EXISTS (SELECT dept.dept_name
0067                FROM   dept
0068                WHERE  emp.dept_id = dept.dept_id)
0069        AND emp.id > 200;
0070
0071 -- Outer references (emp.id) should not be pruned from outer plan
0072 -- TC.01.05
0073 SELECT emp.emp_name
0074 FROM   emp
0075 WHERE  EXISTS (SELECT dept.state
0076                FROM   dept
0077                WHERE  emp.dept_id = dept.dept_id)
0078        AND emp.id > 200;
0079
0080 -- not exists with correlated predicate
0081 -- TC.01.06
0082 SELECT *
0083 FROM   dept
0084 WHERE  NOT EXISTS (SELECT emp_name
0085                    FROM   emp
0086                    WHERE  emp.dept_id = dept.dept_id);
0087
0088 -- not exists with correlated predicate + local predicate
0089 -- TC.01.07
0090 SELECT *
0091 FROM   dept
0092 WHERE  NOT EXISTS (SELECT emp_name
0093                    FROM   emp
0094                    WHERE  emp.dept_id = dept.dept_id
0095                            OR state = 'NJ');
0096
0097 -- not exist both equal and greaterthan predicate
0098 -- TC.01.08
0099 SELECT *
0100 FROM   bonus
0101 WHERE  NOT EXISTS (SELECT *
0102                    FROM   emp
0103                    WHERE  emp.emp_name = emp_name
0104                           AND bonus_amt > emp.salary);
0105
0106 -- select employees who have not received any bonus
0107 -- TC 01.09
0108 SELECT emp.*
0109 FROM   emp
0110 WHERE  NOT EXISTS (SELECT NULL
0111                    FROM   bonus
0112                    WHERE  bonus.emp_name = emp.emp_name);
0113
0114 -- Nested exists
0115 -- TC.01.10
0116 SELECT *
0117 FROM   bonus
0118 WHERE  EXISTS (SELECT emp_name
0119                FROM   emp
0120                WHERE  bonus.emp_name = emp.emp_name
0121                       AND EXISTS (SELECT state
0122                                   FROM   dept
0123                                   WHERE  dept.dept_id = emp.dept_id));