-
Notifications
You must be signed in to change notification settings - Fork 21
/
Copy pathleft-join.sql
85 lines (76 loc) · 3.98 KB
/
left-join.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
/* setup */
CREATE TABLE t1 (f1 INT, f2 INT);
INSERT INTO t1 (f1, f2) VALUES (123, 1);
INSERT INTO t1 (f1, f2) VALUES (456, 2);
INSERT INTO t1 (f1, f2) VALUES (789, 3);
INSERT INTO t1 (f1, f2) VALUES (234, 4);
CREATE TABLE t2 (f3 INT, f4 INT);
INSERT INTO t2 (f3, f4) VALUES (123, 5);
INSERT INTO t2 (f3, f4) VALUES (789, 6);
INSERT INTO t2 (f3, f4) VALUES (345, 7);
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON TRUE;
-- EXPLAIN: $1:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T1 (F1 INTEGER, F2 INTEGER)
-- EXPLAIN: $2:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T2 (F3 INTEGER, F4 INTEGER)
-- EXPLAIN: LEFT JOIN ON TRUE (":memory:".PUBLIC.T1.F1 INTEGER, ":memory:".PUBLIC.T1.F2 INTEGER, ":memory:".PUBLIC.T2.F3 INTEGER, ":memory:".PUBLIC.T2.F4 INTEGER)
-- EXPLAIN: EXPR (":memory:".PUBLIC.T1.F1 INTEGER, ":memory:".PUBLIC.T1.F2 INTEGER, ":memory:".PUBLIC.T2.F3 INTEGER, ":memory:".PUBLIC.T2.F4 INTEGER)
SELECT * FROM t1 LEFT JOIN t2 ON TRUE;
-- F1: 123 F2: 1 F3: 123 F4: 5
-- F1: 123 F2: 1 F3: 789 F4: 6
-- F1: 123 F2: 1 F3: 345 F4: 7
-- F1: 456 F2: 2 F3: 123 F4: 5
-- F1: 456 F2: 2 F3: 789 F4: 6
-- F1: 456 F2: 2 F3: 345 F4: 7
-- F1: 789 F2: 3 F3: 123 F4: 5
-- F1: 789 F2: 3 F3: 789 F4: 6
-- F1: 789 F2: 3 F3: 345 F4: 7
-- F1: 234 F2: 4 F3: 123 F4: 5
-- F1: 234 F2: 4 F3: 789 F4: 6
-- F1: 234 F2: 4 F3: 345 F4: 7
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON f1 = f3;
-- EXPLAIN: $1:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T1 (F1 INTEGER, F2 INTEGER)
-- EXPLAIN: $2:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T2 (F3 INTEGER, F4 INTEGER)
-- EXPLAIN: LEFT JOIN ON F1 = F3 (":memory:".PUBLIC.T1.F1 INTEGER, ":memory:".PUBLIC.T1.F2 INTEGER, ":memory:".PUBLIC.T2.F3 INTEGER, ":memory:".PUBLIC.T2.F4 INTEGER)
-- EXPLAIN: EXPR (":memory:".PUBLIC.T1.F1 INTEGER, ":memory:".PUBLIC.T1.F2 INTEGER, ":memory:".PUBLIC.T2.F3 INTEGER, ":memory:".PUBLIC.T2.F4 INTEGER)
SELECT * FROM t1 LEFT JOIN t2 ON f1 = f3;
-- F1: 123 F2: 1 F3: 123 F4: 5
-- F1: 456 F2: 2 F3: NULL F4: NULL
-- F1: 789 F2: 3 F3: 789 F4: 6
-- F1: 234 F2: 4 F3: NULL F4: NULL
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON f1 = f3 ORDER BY f3, f1;
-- EXPLAIN: $1:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T1 (F1 INTEGER, F2 INTEGER)
-- EXPLAIN: $2:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T2 (F3 INTEGER, F4 INTEGER)
-- EXPLAIN: LEFT JOIN ON F1 = F3 (":memory:".PUBLIC.T1.F1 INTEGER, ":memory:".PUBLIC.T1.F2 INTEGER, ":memory:".PUBLIC.T2.F3 INTEGER, ":memory:".PUBLIC.T2.F4 INTEGER)
-- EXPLAIN: ORDER BY F3 ASC, F1 ASC
-- EXPLAIN: EXPR (":memory:".PUBLIC.T1.F1 INTEGER, ":memory:".PUBLIC.T1.F2 INTEGER, ":memory:".PUBLIC.T2.F3 INTEGER, ":memory:".PUBLIC.T2.F4 INTEGER)
SELECT * FROM t1 LEFT JOIN t2 ON f1 = f3 ORDER BY f3, f1;
-- F1: 234 F2: 4 F3: NULL F4: NULL
-- F1: 456 F2: 2 F3: NULL F4: NULL
-- F1: 123 F2: 1 F3: 123 F4: 5
-- F1: 789 F2: 3 F3: 789 F4: 6
EXPLAIN SELECT * FROM t1 LEFT OUTER JOIN t2 ON TRUE;
-- EXPLAIN: $1:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T1 (F1 INTEGER, F2 INTEGER)
-- EXPLAIN: $2:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T2 (F3 INTEGER, F4 INTEGER)
-- EXPLAIN: LEFT JOIN ON TRUE (":memory:".PUBLIC.T1.F1 INTEGER, ":memory:".PUBLIC.T1.F2 INTEGER, ":memory:".PUBLIC.T2.F3 INTEGER, ":memory:".PUBLIC.T2.F4 INTEGER)
-- EXPLAIN: EXPR (":memory:".PUBLIC.T1.F1 INTEGER, ":memory:".PUBLIC.T1.F2 INTEGER, ":memory:".PUBLIC.T2.F3 INTEGER, ":memory:".PUBLIC.T2.F4 INTEGER)
EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON f1 > f3;
-- EXPLAIN: $1:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T1 (F1 INTEGER, F2 INTEGER)
-- EXPLAIN: $2:
-- EXPLAIN: TABLE ":memory:".PUBLIC.T2 (F3 INTEGER, F4 INTEGER)
-- EXPLAIN: LEFT JOIN ON F1 > F3 (":memory:".PUBLIC.T1.F1 INTEGER, ":memory:".PUBLIC.T1.F2 INTEGER, ":memory:".PUBLIC.T2.F3 INTEGER, ":memory:".PUBLIC.T2.F4 INTEGER)
-- EXPLAIN: EXPR (":memory:".PUBLIC.T1.F1 INTEGER, ":memory:".PUBLIC.T1.F2 INTEGER, ":memory:".PUBLIC.T2.F3 INTEGER, ":memory:".PUBLIC.T2.F4 INTEGER)
SELECT * FROM t1 LEFT JOIN t2 ON f1 > f3;
-- F1: 123 F2: 1 F3: NULL F4: NULL
-- F1: 456 F2: 2 F3: 123 F4: 5
-- F1: 456 F2: 2 F3: 345 F4: 7
-- F1: 789 F2: 3 F3: 123 F4: 5
-- F1: 789 F2: 3 F3: 345 F4: 7
-- F1: 234 F2: 4 F3: 123 F4: 5