create table test_master
(
id int,
name varchar,
value int,
value2 int
);
create table test_log
(
id int,
username varchar
);
insert into test_master values (1, 'master1', 10, 11);
insert into test_master values (2, 'master2', 20, 12);
insert into test_master values (3, 'master3', 30, 13);
insert into test_master values (4, 'master4', 40, 14);
insert into test_master values (5, 'master5', 50, 15);
insert into test_master values (6, 'master6', 60, 16);
insert into test_master values (7, 'master7', 70, 17);
insert into test_log values (1, 'kimura');
insert into test_log values (2, 'kimura');
insert into test_log values (3, 'kimura');
insert into test_log values (4, 'kimura');
insert into test_log values (1, 'yamada');
insert into test_log values (2, 'yamada');
insert into test_log values (5, 'yamada');
insert into test_log values (1, 'abe');
insert into test_log values (4, 'abe');
insert into test_log values (5, 'abe');
insert into test_log values (1, 'tanaka');
insert into test_log values (9, 'tanaka');
select * from test_master join test_log on test_master.id = test_log.id;
select * from test_master left join test_log on test_master.id = test_log.id;
select * from test_master right join test_log on test_master.id = test_log.id;
select * from test_master full outer join test_log on test_master.id = test_log.id;
select * from test_log left join test_master on test_log.id = test_master.id;
select * from test_log right join test_master on test_log.id = test_master.id;
sample_db=# select * from test_master join test_log on test_master.id = test_log.id;
id | name | value | value2 | id | username
----+---------+-------+--------+----+----------
1 | master1 | 10 | 11 | 1 | kimura
1 | master1 | 10 | 11 | 1 | yamada
1 | master1 | 10 | 11 | 1 | abe
1 | master1 | 10 | 11 | 1 | tanaka
2 | master2 | 20 | 12 | 2 | kimura
2 | master2 | 20 | 12 | 2 | yamada
3 | master3 | 30 | 13 | 3 | kimura
4 | master4 | 40 | 14 | 4 | kimura
4 | master4 | 40 | 14 | 4 | abe
5 | master5 | 50 | 15 | 5 | yamada
5 | master5 | 50 | 15 | 5 | abe
(11 rows)
sample_db=# select * from test_master left join test_log on test_master.id = test_log.id;
id | name | value | value2 | id | username
----+---------+-------+--------+----+----------
1 | master1 | 10 | 11 | 1 | kimura
1 | master1 | 10 | 11 | 1 | yamada
1 | master1 | 10 | 11 | 1 | abe
1 | master1 | 10 | 11 | 1 | tanaka
2 | master2 | 20 | 12 | 2 | kimura
2 | master2 | 20 | 12 | 2 | yamada
3 | master3 | 30 | 13 | 3 | kimura
4 | master4 | 40 | 14 | 4 | kimura
4 | master4 | 40 | 14 | 4 | abe
5 | master5 | 50 | 15 | 5 | yamada
5 | master5 | 50 | 15 | 5 | abe
6 | master6 | 60 | 16 | |
7 | master7 | 70 | 17 | |
(13 rows)
sample_db=# select * from test_master right join test_log on test_master.id = test_log.id;
id | name | value | value2 | id | username
----+---------+-------+--------+----+----------
1 | master1 | 10 | 11 | 1 | kimura
1 | master1 | 10 | 11 | 1 | yamada
1 | master1 | 10 | 11 | 1 | abe
1 | master1 | 10 | 11 | 1 | tanaka
2 | master2 | 20 | 12 | 2 | kimura
2 | master2 | 20 | 12 | 2 | yamada
3 | master3 | 30 | 13 | 3 | kimura
4 | master4 | 40 | 14 | 4 | kimura
4 | master4 | 40 | 14 | 4 | abe
5 | master5 | 50 | 15 | 5 | yamada
5 | master5 | 50 | 15 | 5 | abe
| | | | 9 | tanaka
(12 rows)
sample_db=# select * from test_master full outer join test_log on test_master.id = test_log.id;
id | name | value | value2 | id | username
----+---------+-------+--------+----+----------
1 | master1 | 10 | 11 | 1 | kimura
1 | master1 | 10 | 11 | 1 | yamada
1 | master1 | 10 | 11 | 1 | abe
1 | master1 | 10 | 11 | 1 | tanaka
2 | master2 | 20 | 12 | 2 | kimura
2 | master2 | 20 | 12 | 2 | yamada
3 | master3 | 30 | 13 | 3 | kimura
4 | master4 | 40 | 14 | 4 | kimura
4 | master4 | 40 | 14 | 4 | abe
5 | master5 | 50 | 15 | 5 | yamada
5 | master5 | 50 | 15 | 5 | abe
6 | master6 | 60 | 16 | |
7 | master7 | 70 | 17 | |
| | | | 9 | tanaka
(14 rows)
sample_db=# select * from test_log left join test_master on test_log.id = test_master.id;
id | username | id | name | value | value2
----+----------+----+---------+-------+--------
1 | kimura | 1 | master1 | 10 | 11
1 | yamada | 1 | master1 | 10 | 11
1 | abe | 1 | master1 | 10 | 11
1 | tanaka | 1 | master1 | 10 | 11
2 | kimura | 2 | master2 | 20 | 12
2 | yamada | 2 | master2 | 20 | 12
3 | kimura | 3 | master3 | 30 | 13
4 | kimura | 4 | master4 | 40 | 14
4 | abe | 4 | master4 | 40 | 14
5 | yamada | 5 | master5 | 50 | 15
5 | abe | 5 | master5 | 50 | 15
9 | tanaka | | | |
(12 rows)
sample_db=# select * from test_log right join test_master on test_log.id = test_master.id;
id | username | id | name | value | value2
----+----------+----+---------+-------+--------
1 | kimura | 1 | master1 | 10 | 11
1 | yamada | 1 | master1 | 10 | 11
1 | abe | 1 | master1 | 10 | 11
1 | tanaka | 1 | master1 | 10 | 11
2 | kimura | 2 | master2 | 20 | 12
2 | yamada | 2 | master2 | 20 | 12
3 | kimura | 3 | master3 | 30 | 13
4 | kimura | 4 | master4 | 40 | 14
4 | abe | 4 | master4 | 40 | 14
5 | yamada | 5 | master5 | 50 | 15
5 | abe | 5 | master5 | 50 | 15
| | 6 | master6 | 60 | 16
| | 7 | master7 | 70 | 17
(13 rows)
|
