■集合AとBの排他的集合を考える
完全外部結合を使用し、NULL条件で抽出する
/* 完全外部結合で排他的和集合を求める(p.98) */ SELECT COALESCE(A.id, B.id) AS id, COALESCE(A.name , B.name ) AS name FROM Class_A A FULL OUTER JOIN Class_B B ON A.id = B.id WHERE A.name IS NULL OR B.name IS NULL;
【テーブル作成SQL】
/* 完全外部結合 */ CREATE TABLE Class_A (id char(1), name varchar(30), PRIMARY KEY(id)); CREATE TABLE Class_B (id char(1), name varchar(30), PRIMARY KEY(id)); INSERT INTO Class_A (id, name) VALUES('1', '田中'); INSERT INTO Class_A (id, name) VALUES('2', '鈴木'); INSERT INTO Class_A (id, name) VALUES('3', '伊集院'); INSERT INTO Class_B (id, name) VALUES('1', '田中'); INSERT INTO Class_B (id, name) VALUES('2', '鈴木'); INSERT INTO Class_B (id, name) VALUES('4', '西園寺');