SQL 指南書集 目次 - ray88’s diary
■外部結合で差集合を求める A- B
/* 外部結合で差集合を求める:A-B(p.97) */ SELECT A.id AS id, A.name AS A_name FROM Class_A A LEFT OUTER JOIN Class_B B ON A.id = B.id WHERE B.name IS NULL;
■外部結合で差集合を求める B- A
/* 外部結合で差集合を求める:B-A(p.98) */ SELECT B.id AS id, B.name AS B_name FROM Class_A A RIGHT OUTER JOIN Class_B B ON A.id = B.id WHERE A.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', '西園寺');