SQLの基本はデカルト積で得られた集合ってことですね。
正規化だとか「表」だとかプライマリキーとか、そういう話は一度忘れてしまうこと。手続き型の解法も忘れてしまえ。
以下、JOINの振る舞いについて記載します。（MySQLとDB2の方言をただ並べただけかも・・・）
準備。
drop table hoge;
create table hoge(
col1 char(1),
colh_2 char(1)
);
drop table piyo;
create table piyo(
col1 char(1),
colp_2 char(1)
);
delete from hoge;
insert into hoge values('a','a');
insert into hoge values('b','b');
insert into hoge values('c','c');
insert into hoge values('X','x');
insert into hoge values('Y','y');
insert into hoge values('Z','z');
delete from piyo;
insert into piyo values('1','1');
insert into piyo values('2','2');
insert into piyo values('3','3');
insert into piyo values('X','X');
insert into piyo values('Y','Y');
insert into piyo values('Z','Z');
MySQLとDB2で試しました。JOINについては方言があることを確認（仕様としてどうなのか、までは踏み込んでいません。MySQLに癖がある気がします）。
(1) デカルト積－１
MySQL: select h.col1, h.colh_2, p.col1, p.colp_2 from hoge h, piyo p;
DB2: select h.col1, h.colh_2, p.col1, p.colp_2 from hoge h, piyo p;
(2) デカルト積－２
MySQL: select h.colh_2, p.colp_2 from hoge h, piyo p;
DB2: select h.colh_2, p.colp_2 from hoge h, piyo p;
(3) しつこくデカルト積－３
MySQL: select h.col1, p.col1 from hoge h, piyo p;
DB2: select h.col1, p.col1 from hoge h, piyo p;
要するに
from hoge, piyo
した時点で、裏に巨大な直積集合ができているわけです。
今度はjoinの振る舞いを確認
(4) JOIN単体
MySQL: select h.colh_2, p.colp_2 from hoge h join piyo p; (1)と同じ結果が得られる。
DB2: JOIN単体ではエラー
（select h.colh_2, p.colp_2 from hoge h, piyo pで同様の結果が得られる）
(5) 今度はクロス結合。
MySQL: select h.colh_2, p.colp_2 from hoge h cross join piyo p;
DB2: CROSS JOINはエラー（でもMySQLでも単に'cross'が無視されているだけかも？）
(6) INNER JOIN ON ～
MySQL: select h.colh_2, p.colp_2 from hoge h inner join piyo p;
DB2: select h.colh_2, p.colp_2 from hoge h inner join piyo p on h.col1 = p.col1;
結果に相違あり。MySQLでは直積集合がそのまま表示されている。（inner が無視されている？）
DB2では合致する行のみが表示される。↓
db2 => select h.colh_2, p.colp_2 from hoge h inner join piyo p on h.col1 = p.col1
COLH_2 COLP_2
------ ------
x X
y Y
z Z
3 レコードが選択されました。
同様の結果をMySQLで表示するためには、
mysql> select h.colh_2, p.colp_2 from hoge h inner join piyo p where h.col1 = p.col1;
+--------+--------+
| colh_2 | colp_2 |
+--------+--------+
| x | X |
| y | Y |
| z | Z |
+--------+--------+
3 rows in set (0.00 sec)
とする。
(7) LEFT JOIN
MySQL: select h.colh_2, p.colp_2 from hoge h natural left outer join piyo p;
DB2: select h.colh_2, p.colp_2 from hoge h left outer join piyo p on h.col1 = p.col1;
これはMySQLとDB2で結果が同じ。
+--------+--------+
| colh_2 | colp_2 |
+--------+--------+
| a | NULL |（DB2では'-'ハイフンが表示される）
| b | NULL |
| c | NULL |
| x | X |
| y | Y |
| z | Z |
+--------+--------+
6 rows in set (0.00 sec)
(8) RIGHT JOIN
MySQL: select h.colh_2, p.colp_2 from hoge h natural right outer join piyo p;
DB2: select h.colh_2, p.colp_2 from hoge h right outer join piyo p on h.col1 = p.col1;
（DB2での実行例）
db2 => select h.colh_2, p.colp_2 from hoge h right outer join piyo p on h.col1 = p.col1;
COLH_2 COLP_2
------ ------
- 1
- 2
- 3
x X
y Y
z Z
6 レコードが選択されました。
これもLEFT JOIN同様両者の結果は変わらず。
(9) FULL OUTER JOIN
MySQL: full outer join 未対応
DB2: select h.colh_2, p.colp_2 from hoge h full outer join piyo p on h.col1 = p.col1;
db2 => select h.colh_2, p.colp_2 from hoge h full outer join piyo p on h.col1 = p.col1;
COLH_2 COLP_2
------ ------
- 1
- 2
- 3
x X
y Y
z Z
a -
b -
c -
9 レコードが選択されました。
同様の結果をMySQLで出すためには
mysql> select h.colh_2, p.colp_2 from hoge h natural right outer join piyo p
-> union
-> select h.colh_2, p.colp_2 from hoge h natural left outer join piyo p
-> ;
+--------+--------+
| colh_2 | colp_2 |
+--------+--------+
| NULL | 1 |
| NULL | 2 |
| NULL | 3 |
| x | X |
| y | Y |
| z | Z |
| a | NULL |
| b | NULL |
| c | NULL |
+--------+--------+
9 rows in set (0.00 sec)
まあ美しくはありません。
なんだか支離滅裂なようですが、SQLが難しいってことがよく分かりましたよ。あたしゃ。
もうこれから原則JOIN禁止。副照会はいかなる理由があっても厳禁。
以上。
.
0 件のコメント:
コメントを投稿