[SQL] テーブル結合(1)

where句とJOINで同じ結果になるけど…どう使い分けるの？
と思ったんだけど…
whereは抽出の条件
join on は結合の条件と考えるとなんとなく意味が異なる気がしてきた。

DROP TABLE AAA_TB_A;
DROP TABLE AAA_TB_B;

-- 同じ構成で２つのテーブルを作る
CREATE TABLE AAA_TB_A (
    ID VARCHAR2(20),
    TYPE VARCHAR2(20)
);
CREATE TABLE AAA_TB_B (
    ID VARCHAR2(20),
    TYPE VARCHAR2(20)
);
select * from AAA_TB_A;
select * from AAA_TB_B;

delete from AAA_TB_A;
delete from AAA_TB_B;

INSERT INTO AAA_TB_A values('A001', 'TYPE1');
INSERT INTO AAA_TB_A values('A002', 'TYPE2');
INSERT INTO AAA_TB_A values('A003', 'TYPE3');

INSERT INTO AAA_TB_B values('B001a', 'TYPE1');
INSERT INTO AAA_TB_B values('B001b', 'TYPE1');
INSERT INTO AAA_TB_B values('B001c', 'TYPE1');
INSERT INTO AAA_TB_B values('B002a', 'TYPE2');
INSERT INTO AAA_TB_B values('B002b', 'TYPE2');
INSERT INTO AAA_TB_B values('B004a', 'TYPE4');

-- 以下の２つの結果は同じ
select * from AAA_TB_A tba, AAA_TB_B tbb where tba.TYPE = tbb.TYPE;
select * from AAA_TB_A tba INNER JOIN AAA_TB_B tbb on tba.TYPE = tbb.TYPE;
-- OUTER JOIN はどちらかにのテーブルを基準に無い列NULLにするイメージ
select * from AAA_TB_A tba LEFT OUTER JOIN AAA_TB_B tbb on tba.TYPE = tbb.TYPE;
select * from AAA_TB_A tba RIGHT OUTER JOIN AAA_TB_B tbb on tba.TYPE = tbb.TYPE;