集合演算子 「INTERSECT」
2つのテーブルを比較して、共通するレコードを抽出することができるのが、集合演算子である「INTERSECT」です。
ちなみに集合演算子を使った場合、基本的に重複行は抽出されません。
INTERSECT構文
以下が、「INTERSECT」の基本的な構文です。
SELECT 列1, 列2,・・・ FROM <テーブル名1> INTERSECT SELECT 列1, 列2,・・・ FROM <テーブル名2>;
検証用のデータの用意
以下の2つのテーブルとデータを使って「INTERSECT」の動きを見ていきます。
| id | name | address |
|---|---|---|
| 001 | いるか | 海 |
| 002 | うさぎ | 陸 |
| 003 | ぺんぎん | 空 |
| 004 | いるか | 海 |
| id | name | address |
|---|---|---|
| 001 | いるか | 海 |
| 002 | うさぎ | 陸 |
| 005 | ふくろう | 空 |
検証用データのSQL
--animal1テーブルとデータの作成
CREATE TABLE animal1
(
id VARCHAR2(10)
,name VARCHAR2(20)
,address VARCHAR2(20)
);
INSERT INTO animal1 ( id, name, address ) VALUES ('001', 'いるか', '海');
INSERT INTO animal1 ( id, name, address ) VALUES ('002', 'うさぎ', '陸');
INSERT INTO animal1 ( id, name, address ) VALUES ('003', 'ぺんぎん', '空');
INSERT INTO animal1 ( id, name, address ) VALUES ('004', 'いるか', '海');
----animal2テーブルとデータの作成
CREATE TABLE animal2
(
id VARCHAR2(10)
,name VARCHAR2(20)
,address VARCHAR2(20)
);
INSERT INTO animal2 ( id, name, address ) VALUES ('001', 'いるか', '海');
INSERT INTO animal2 ( id, name, address ) VALUES ('002', 'うさぎ', '陸');
INSERT INTO animal2 ( id, name, address ) VALUES ('005', 'ふくろう', '空');
検証用データの実行結果
SQL> CREATE TABLE animal1
2 (
3 id VARCHAR2(10)
4 ,name VARCHAR2(20)
5 ,address VARCHAR2(20)
6 );
表が作成されました。
SQL> INSERT INTO animal1 ( id, name, address ) VALUES ('001', 'いるか', '海');
1行が作成されました。
SQL> INSERT INTO animal1 ( id, name, address ) VALUES ('002', 'うさぎ', '陸');
1行が作成されました。
SQL> INSERT INTO animal1 ( id, name, address ) VALUES ('003', 'ぺんぎん', '空');
1行が作成されました。
SQL> INSERT INTO animal1 ( id, name, address ) VALUES ('004', 'いるか', '海');
1行が作成されました。
SQL> CREATE TABLE animal2
2 (
3 id VARCHAR2(10)
4 ,name VARCHAR2(20)
5 ,address VARCHAR2(20)
6 );
表が作成されました。
SQL> INSERT INTO animal2 ( id, name, address ) VALUES ('001', 'いるか', '海');
1行が作成されました。
SQL> INSERT INTO animal2 ( id, name, address ) VALUES ('002', 'うさぎ', '陸');
1行が作成されました。
SQL> INSERT INTO animal2 ( id, name, address ) VALUES ('005', 'ふくろう', '空');
1行が作成されました。
INTERSECTの実行例
「ANIMAL1」テーブルと「ANIMAL2テーブル」の2つのテーブルから共通のレコードを抽出してみましょう。
今回の例ですと、IDが001の「いるか」と「うさぎ」のレコードが抽出されるはずですね。
イメージを掴むためにベン図を書いてみました。
INTERASECTのSQL
以下のSQLを実行してみましょう。
SELECT id, name ,address FROM animal1 INTERSECT SELECT id, name ,address FROM animal2;
INTERASECTの実行結果
SQL> SELECT id, name ,address FROM animal1 2 INTERSECT 3 SELECT id, name ,address FROM animal2; ID NAME ADDRESS ---------- ---------- -------------------- 001 いるか 海 002 うさぎ 陸
想定どおりのレコードが取得できていますね＼(^o^)／
INTERSECTは重複行を取得しない
冒頭でも記述したように「INTERSECT」は重複行を取得しません。というか集合演算子は、重複行を取得しません。
上記までの検証結果だと重複行が出力されないのかが判別つかないので、「ANIMAL1」テーブルと「ANIMAL2テーブル」の2つのテーブルから「name」カラムと「address」カラムに絞って共通のレコードがあるか抽出してみましょう。
イメージを掴むためにベン図を書いてみました。
先程と違う点は、IDが「001」の「いるか」とIDが「004」の「いるか」がカラムを絞って抽出したことによって両方とも「name：いるか」の「address:海」のレコードになっている点です。重複行が出力されるのであれば、「いるか」が2行出力されるはずですね＼(^o^)／
INTERASECTのSQL
以下のSQLを実行してみましょう。
SELECT name ,address FROM animal1 INTERSECT SELECT name ,address FROM animal2;
INTERASECTの実行結果
SQL> SELECT name ,address FROM animal1 2 INTERSECT 3 SELECT name ,address FROM animal2; NAME ADDRESS ---------- -------------------- いるか 海 うさぎ 陸
想定どおりの結果ですね。「いるか」のレコードは1行しか抽出しませんでした。このことから重複行は取得しないということがわかったと思います。
「ALL」オプションについて
ちなみに、「ALL」オプションを付与することで重複行を取得するようにできる集合演算子もあります。「INTERSECT」演算子についてもOracle以外のDBMSだと「ALL」オプション使えるものもあるみたいです。
現在検証で使っているOracleの11g時点では「INTERSECT」に「ALL」オプションは使えないはずです。。。！残念(´・ω・｀)
コメント