-- 1
-- CTE Scan
WITH RECURSIVE a(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM a WHERE i < 10) SELECT * FROM a;

-- 2
-- Nested Loop Semi Join
SELECT * FROM (values(1)) AS a(i) WHERE i IN (values(1));

-- 3
-- Hash Semi Join
SET enable_nestloop = off;
SELECT * FROM (values(1)) AS a(i) WHERE i IN (values(1));

-- 4
-- Merge Semi Join
SET enable_nestloop = off;
SET enable_hAShjoin = off;
SELECT * FROM (values(1)) AS a(i) WHERE i IN (values(1));

RESET all;

-- 5
-- HashSetOp Intersect
SELECT * FROM (values(1)) AS a(i) INTERSECT (values(1));

-- 6
-- HashSetOp Intersect All
SELECT * FROM (values(1)) AS a(i)  INTERSECT ALL (values(1));

-- 7
-- HashSetOp Except
SELECT * FROM (values(1)) AS a(i) EXCEPT (values(1));

-- 8
-- HashSetOp Except All
SELECT * FROM (values(1)) AS a(i) EXCEPT ALL (values(1));

-- 9
-- Hash Anti Join
CREATE TABLE NewPlanTestTbl1 AS (SELECT * FROM generate_series(1,1000)  AS a(i));
ANALYZE NewPlanTestTbl1;

SELECT * FROM (SELECT * FROM generate_series(1,100)) AS a(i) WHERE NOT EXISTS (SELECT 1 FROM NewPlanTestTbl1 WHERE i = a.i);

-- 10
-- Merge Anti Join
SET enable_hAShjoin = off;
SELECT * FROM (SELECT * FROM generate_series(1,100)) AS a(i) WHERE NOT EXISTS (SELECT 1 FROM NewPlanTestTbl1 WHERE i = a.i);

-- 11
-- Nested Loop Anti Join
SET enable_hasHjoin = off;
SET enable_mergejoin = off;
SELECT * FROM (SELECT * FROM generate_series(1,100)) AS a(i) WHERE NOT EXISTS (SELECT 1 FROM NewPlanTestTbl1 WHERE i = a.i);


DROP TABLE NewPlanTestTbl1;

CREATE TABLE NewPlanTestTbl2 (
  id INT PRIMARY KEY,                                    -- department ID
  parent_NewPlanTestTbl2 INT REFERENCES NewPlanTestTbl2, -- upper department ID
  name TEXT                                              -- department name
);

INSERT INTO NewPlanTestTbl2 VALUES (0, NULL, 'ROOT');
INSERT INTO NewPlanTestTbl2 VALUES (1, 0, 'A');
INSERT INTO NewPlanTestTbl2 VALUES (2, 1, 'B');
INSERT INTO NewPlanTestTbl2 VALUES (3, 2, 'C');
INSERT INTO NewPlanTestTbl2 VALUES (4, 2, 'D');
INSERT INTO NewPlanTestTbl2 VALUES (5, 0, 'E');
INSERT INTO NewPlanTestTbl2 VALUES (6, 4, 'F');
INSERT INTO NewPlanTestTbl2 VALUES (7, 5, 'G');

-- NewPlanTestTbl2 structure represented here is AS follows:
--
-- ROOT-+->A-+->B-+->C
--      |         |
--      |         +->D-+->F
--      +->E-+->G


-- 12
-- Recursive Union
-- WorkTable Scan
WITH RECURSIVE subdepartment AS
(
	non recursive term
--	SELECT * FROM NewPlanTestTbl2 WHERE name = 'A'
--	UNION ALL
	recursive term
--	SELECT d.* FROM NewPlanTestTbl2 AS d, subdepartment AS sd
		WHERE d.parent_department = sd.id
)
SELECT * FROM subdepartment ORDER BY name;

DROP TABLE NewPlanTestTbl2;

