SQLの結合の基本
PostgreSQLのSQLの結合 join , outer joinのごく基本の確認メモ。
create table t1(cd varchar, v integer);
create table t2(cd varchar, v integer);
insert into t1 values('t1001',1234);
insert into t1 values('t1002',3432);
insert into t1 values('t1003',3432);
insert into t1 values('t9001',2211);
insert into t1 values('t9002',8177);
insert into t1 values('t9002',3658);
insert into t2 values('t2001',5217);
insert into t2 values('t2002',3907);
insert into t2 values('t2003',8746);
insert into t2 values('t9001',7319);
insert into t2 values('t9002',2258);
insert into t2 values('t9002',7854);
test=# select * from t1;
cd | v
-------+------
t1001 | 1234
t1002 | 3432
t1003 | 3432
t9001 | 2211
t9002 | 8177
t9002 | 3658
(6 rows)
test=# select * from t2;
cd | v
-------+------
t2001 | 5217
t2002 | 3907
t2003 | 8746
t9001 | 7319
t9002 | 2258
t9002 | 7854
(6 rows)
--- デフォルトは、 inner join
test=# select * from t1 join t2 on t1.cd=t2.cd;
cd | v | cd | v
-------+------+-------+------
t9001 | 2211 | t9001 | 7319
t9002 | 8177 | t9002 | 2258
t9002 | 8177 | t9002 | 7854
t9002 | 3658 | t9002 | 2258
t9002 | 3658 | t9002 | 7854
(5 rows)
test=#
test=# select * from t1 left outer join t2 on t1.cd=t2.cd;
cd | v | cd | v
-------+------+-------+------
t1001 | 1234 | |
t1002 | 3432 | |
t1003 | 3432 | |
t9001 | 2211 | t9001 | 7319
t9002 | 8177 | t9002 | 2258
t9002 | 8177 | t9002 | 7854
t9002 | 3658 | t9002 | 2258
t9002 | 3658 | t9002 | 7854
(8 rows)
test=#
test=#
test=# select * from t1 right outer join t2 on t1.cd=t2.cd;
cd | v | cd | v
-------+------+-------+------
| | t2001 | 5217
| | t2002 | 3907
| | t2003 | 8746
t9001 | 2211 | t9001 | 7319
t9002 | 8177 | t9002 | 2258
t9002 | 8177 | t9002 | 7854
t9002 | 3658 | t9002 | 2258
t9002 | 3658 | t9002 | 7854
(8 rows)
select * from t1 full outer join t2 on t1.cd=t2.cd;
test=# select * from t1 full outer join t2 on t1.cd=t2.cd;
cd | v | cd | v
-------+------+-------+------
t1001 | 1234 | |
t1002 | 3432 | |
t1003 | 3432 | |
| | t2001 | 5217
| | t2002 | 3907
| | t2003 | 8746
t9001 | 2211 | t9001 | 7319
t9002 | 8177 | t9002 | 2258
t9002 | 8177 | t9002 | 7854
t9002 | 3658 | t9002 | 2258
t9002 | 3658 | t9002 | 7854
(11 rows)
select * from t1 full join t2 on t1.cd=t2.cd;
は可能か。
select * from t1 full outer join t2 on t1.cd=t2.cd;
と同じになった。
select * from t1 full inner join t2 on t1.cd=t2.cd;
は不可。
select * from t1,t2;
test=# select * from t1,t2;
cd | v | cd | v
-------+------+-------+------
t1001 | 1234 | t2001 | 5217
t1001 | 1234 | t2002 | 3907
t1001 | 1234 | t2003 | 8746
t1001 | 1234 | t9001 | 7319
t1001 | 1234 | t9002 | 2258
t1001 | 1234 | t9002 | 7854
t1002 | 3432 | t2001 | 5217
t1002 | 3432 | t2002 | 3907
t1002 | 3432 | t2003 | 8746
t1002 | 3432 | t9001 | 7319
t1002 | 3432 | t9002 | 2258
t1002 | 3432 | t9002 | 7854
t1003 | 3432 | t2001 | 5217
t1003 | 3432 | t2002 | 3907
t1003 | 3432 | t2003 | 8746
t1003 | 3432 | t9001 | 7319
t1003 | 3432 | t9002 | 2258
t1003 | 3432 | t9002 | 7854
t9001 | 2211 | t2001 | 5217
t9001 | 2211 | t2002 | 3907
t9001 | 2211 | t2003 | 8746
t9001 | 2211 | t9001 | 7319
t9001 | 2211 | t9002 | 2258
t9001 | 2211 | t9002 | 7854
t9002 | 8177 | t2001 | 5217
t9002 | 8177 | t2002 | 3907
t9002 | 8177 | t2003 | 8746
t9002 | 8177 | t9001 | 7319
t9002 | 8177 | t9002 | 2258
t9002 | 8177 | t9002 | 7854
t9002 | 3658 | t2001 | 5217
t9002 | 3658 | t2002 | 3907
t9002 | 3658 | t2003 | 8746
t9002 | 3658 | t9001 | 7319
t9002 | 3658 | t9002 | 2258
t9002 | 3658 | t9002 | 7854
(36 rows)
select * from t1,t2 where t1.cd=t2.cd;
test=# select * from t1,t2 where t1.cd=t2.cd;
cd | v | cd | v
-------+------+-------+------
t9001 | 2211 | t9001 | 7319
t9002 | 8177 | t9002 | 2258
t9002 | 8177 | t9002 | 7854
t9002 | 3658 | t9002 | 2258
t9002 | 3658 | t9002 | 7854
(5 rows)
test=#