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
86
87
|
--
-- join.sql
--
-- Test join clauses
--
CREATE TABLE JOIN_TBL (
i integer,
j integer,
x text
);
CREATE TABLE JOIN2_TBL (
i integer,
k integer
);
INSERT INTO JOIN_TBL VALUES (1, 3, 'one');
INSERT INTO JOIN_TBL VALUES (2, 2, 'two');
INSERT INTO JOIN_TBL VALUES (3, 1, 'three');
INSERT INTO JOIN_TBL VALUES (4, 0, 'four');
INSERT INTO JOIN2_TBL VALUES (1, -1);
INSERT INTO JOIN2_TBL VALUES (2, 2);
INSERT INTO JOIN2_TBL VALUES (3, -3);
INSERT INTO JOIN2_TBL VALUES (2, 4);
--
-- Inner joins (equi-joins)
--
SELECT '' AS "xxx", *
FROM JOIN_TBL CROSS JOIN JOIN2_TBL;
SELECT '' AS "xxx", *
FROM JOIN_TBL NATURAL JOIN JOIN2_TBL;
SELECT '' AS "xxx", *
FROM JOIN_TBL INNER JOIN JOIN2_TBL USING (i);
SELECT '' AS "xxx", *
FROM JOIN_TBL JOIN JOIN2_TBL ON (JOIN_TBL.i = JOIN2_TBL.i);
SELECT '' AS "xxx", *
FROM JOIN_TBL JOIN JOIN2_TBL ON (JOIN_TBL.i = JOIN2_TBL.k);
SELECT '' AS "xxx", *
FROM JOIN_TBL CROSS JOIN JOIN2_TBL;
--
-- Non-equi-joins
--
SELECT '' AS "xxx", *
FROM JOIN_TBL JOIN JOIN2_TBL ON (JOIN_TBL.i <= JOIN2_TBL.k);
--
-- Outer joins
--
SELECT '' AS "xxx", *
FROM JOIN_TBL OUTER JOIN JOIN2_TBL USING (i);
SELECT '' AS "xxx", *
FROM JOIN_TBL LEFT OUTER JOIN JOIN2_TBL USING (i);
SELECT '' AS "xxx", *
FROM JOIN_TBL RIGHT OUTER JOIN JOIN2_TBL USING (i);
SELECT '' AS "xxx", *
FROM JOIN_TBL FULL OUTER JOIN JOIN2_TBL USING (i);
--
-- More complicated constructs
--
--
-- Clean up
--
DROP TABLE JOIN_TBL;
DROP TABLE JOIN2_TBL;
|
