oceanbase

1
drop table if exists tjuo1,tjuo2,tjuo3;
2
create table tjuo1 (pk int,c1 int, primary key(pk));
3
create table tjuo2 (pk int,c1 int, c2 int, primary key(pk));
4
create table tjuo3 (pk int,c2 int, primary key(pk));
5
insert into tjuo1 values(1, 11), (2, 12), (3, 13);
6
insert into tjuo2 values(4, 11, 21), (5, 12, 22), (6, 13, 23);
7
insert into tjuo3 values(7, 21), (8, 22), (9, 23);
8
select * from tjuo1;
9
pk	c1
10
1	11
11
2	12
12
3	13
13
select * from tjuo2;
14
pk	c1	c2
15
4	11	21
16
5	12	22
17
6	13	23
18
select * from tjuo3;
19
pk	c2
20
7	21
21
8	22
22
9	23
23
SELECT * FROM tjuo1 JOIN  (SELECT * FROM tjuo2) as tjuo2_alias  on tjuo1.c1= tjuo2_alias.c1;
24
pk	c1	pk	c1	c2
25
1	11	4	11	21
26
2	12	5	12	22
27
3	13	6	13	23
28
SELECT * FROM tjuo1 RIGHT JOIN (SELECT * FROM tjuo2) as tjuo2_alias  USING (c1);
29
c1	pk	c2	pk
30
11	4	21	1
31
12	5	22	2
32
13	6	23	3
33
SELECT * FROM tjuo1 JOIN tjuo2 on tjuo1.c1=tjuo2.c1 JOIN tjuo3 on tjuo2.c2=tjuo3.c2;
34
pk	c1	pk	c1	c2	pk	c2
35
1	11	4	11	21	7	21
36
2	12	5	12	22	8	22
37
3	13	6	13	23	9	23
38
SELECT * FROM tjuo1 RIGHT JOIN tjuo2 USING (c1) RIGHT JOIN tjuo3 USING (c2);
39
c2	pk	c1	pk	pk
40
21	7	11	4	1
41
22	8	12	5	2
42
23	9	13	6	3
43
SELECT * FROM tjuo1 RIGHT JOIN (SELECT * FROM tjuo2) as tjuo2_alias USING (c1) RIGHT JOIN tjuo3 USING (c2);
44
c2	pk	c1	pk	pk
45
21	7	11	4	1
46
22	8	12	5	2
47
23	9	13	6	3
48
SELECT * FROM  (SELECT * FROM tjuo1) tjuo1_alias RIGHT JOIN (SELECT * FROM tjuo2) as tjuo2_alias USING (c1) RIGHT JOIN (SELECT * FROM tjuo3) as tjuo3_alias USING (c2);
49
c2	pk	c1	pk	pk
50
21	7	11	4	1
51
22	8	12	5	2
52
23	9	13	6	3
53
SELECT * FROM  (SELECT * FROM tjuo1) tjuo1_alias RIGHT JOIN tjuo2  USING (c1) RIGHT JOIN (SELECT * FROM tjuo3) as tjuo3_alias USING (c2);
54
c2	pk	c1	pk	pk
55
21	7	11	4	1
56
22	8	12	5	2
57
23	9	13	6	3
58
SELECT * FROM  (SELECT c2 FROM tjuo1 as i1 JOIN (SELECT * FROM tjuo2) as i2 USING (c1) ) as t12 RIGHT JOIN (SELECT * FROM tjuo3) as tjuo3_alias USING (c2);
59
c2	pk
60
21	7
61
22	8
62
23	9
63
SELECT * FROM  (SELECT c2 FROM tjuo1 as i1 JOIN (SELECT * FROM tjuo2) as i2 USING (c1) ) as t12;
64
c2
65
21
66
22
67
23
68
drop table tjuo1, tjuo2, tjuo3;
69