【SQL】INTERSECT(共通部分の選択)をわかりやすく3分で解説

Database

SQLの集合演算子 INTERSECT の使い方をデータベース初心者向けにわかりやすく3分で解説します。

INTERSECT はSELECT文で抽出した結果の共通部分を取得する際に利用します。

SQL INTERSECT
このページで学べる内容
  • INTERSECT の使い方
  • INTERSECT の注意点

プログラマーやデータベースエンジニアを目指す方であれば知らないと恥ずかしい基本知識の1つです。是非最後までご覧ください。

スポンサーリンク

SQL:INTERSECT

INTERSECT は同じ集合演算子である UNION/UNION ALL と同じ構文ルールです。

SELECT カラム名 FROM テーブル名1
INTERSECT
SELECT カラム名 FROM テーブル名2;

文法自体は非常に簡単でSELECT文INTERSECTで繋げるだけ。早速、実際にINTERSECTを利用したSQL文を実行してみましょう。

ここでは、以下のような2つのテーブルを利用します。

参考までに上記のMemberList_A/MemberList_Bテーブルを用意するためのCREATE TABLE文/INSERT文を掲載しておきます。

CREATE TABLE MemberList_A (
Number       INT(2),
Name     VARCHAR(50),
Class    VARCHAR(1)
);

/*MemberList_A定義
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| Number | int(2)      | YES  |     | NULL    |       |
| Name   | varchar(50) | YES  |     | NULL    |       |
| Class  | varchar(1)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
*/


CREATE TABLE MemberList_B (
Number       INT(2),
Name     VARCHAR(50),
Class    VARCHAR(1)
);

/*MemberList_B定義
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| Number | int(2)      | YES  |     | NULL    |       |
| Name   | varchar(50) | YES  |     | NULL    |       |
| Class  | varchar(1)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
*/


-- INSERT_A
INSERT INTO MemberList_A (Number,Name,Class) VALUES (1,'イチロウ','A');
INSERT INTO MemberList_A (Number,Name,Class) VALUES (2,'ジロウ','A');
INSERT INTO MemberList_A (Number,Name,Class) VALUES (3,'サブロウ','A');
INSERT INTO MemberList_A (Number,Name,Class) VALUES (4,'シロウ','A');
-- INSERT_B
INSERT INTO MemberList_B (Number,Name,Class) VALUES (1,'イチロウ','B');
INSERT INTO MemberList_B (Number,Name,Class) VALUES (2,'ジロウ','B');
INSERT INTO MemberList_B (Number,Name,Class) VALUES (3,'ゴロウ','B');
INSERT INTO MemberList_B (Number,Name,Class) VALUES (4,'ハチロウ','B');
SELECT Number, Name FROM MemberList_A
INTERSECT
SELECT Number, NAme FROM MemberList_B;

/* 結果
+--------+----------+
| Number | Name     |
+--------+----------+
|      1 | イチロウ |
|      2 | ジロウ   |
+--------+----------+
*/

ご覧のように、2件のレコードが取得されました。この取得された2件のレコードは、指定した2つのテーブルの両方に存在するレコードです。

SQL INTERSECT

INTERSECTは以下の図の考え方が基本で、2つのテーブルの「両方に存在」するレコードを抽出します。

INTERSECT
図:INTERSECT

UNION/UNION ALL は両方のテーブルに存在するすべてのレコードを単純に統合して取得するため、同じ図で表すと以下のようになります。

図:UNION

この図を頭に入れておくと、UNIONとINTERSECTの違いがよくわかるはずです。

MySQL:INTERSECT

実はMySQLではINTERSECTが実装されていないため使用することができません。

MySQLで同様の処理を行いたい場合は、サブクエリINNER JOINを利用して代替方法を考える必要があります。

-- INTERSECTの代替例(MySQL)
SELECT
  A.Number,
  A.Name
FROM
  MemberList_A AS A
INNER JOIN
  MemberList_B AS B
ON
  A.Number = B.Number AND
  A.Name = B.Name;

/* 結果
+--------+----------+
| Number | Name     |
+--------+----------+
|      1 | イチロウ |
|      2 | ジロウ   |
+--------+----------+
*/

INTERSECTの注意点

INTERSECTもUNIONと同様に以下の3つに注意が必要です。

INTERSECTの注意点
  • ①:指定するカラムの数は同じにすること
  • ②:指定するカラムのデータ型が一致していること
  • ③:ORDER BY句は最後に1つだけ利用すること

INTERSECT―注意点1:指定するカラムの数は同じにすること

INTERSECTは、指定するカラムの数が必ず一致している必要があります。

例えば、以下のSQLは2つのSELECT文で指定しているレコード数が異なるためエラーが発生します。

-- エラーが発生するパターン1
SELECT Number,Name,Class FROM MemberList_A
INTERSECT
SELECT Number,Name FROM MemberList_B;

--ERROR 1222 (21000): The used SELECT statements have a different number of columns

INTERSECT―注意点2:指定するカラムのデータ型が一致していること

注意点の2つ目が「データ型」です。指定するカラム(=左から数えて同じ位置にあるカラム)のデータ型は一致している必要があります。

例えば、以下のようなSQLはエラーになります。

SELECT Number FROM MemberList_A
INTERSECT
SELECT Class FROM MemberLIst_B;

/* 
Number →INT型
Class  →Varchar型
*/

INTERSECT―注意点3:ORDER BY句は最後に1つだけ利用すること

INTERSECTを利用するSQL文では、ORDER BY句は全体の最後に一度だけしか利用できないという注意点があります。

例えば、以下のSQL文では、ORDER BY句をそれぞれのSELECT文の中で利用しているためエラーが発生します。

SELECT Number,Name FROM MemberList_A
ORDER BY Number
INTERSECT
SELECT Number,Name FROM MemberList_B
ORDER BY Number;

-- ERROR 1221 (HY000): Incorrect usage of UNION and ORDER BY

正しい文法では、以下のように全体の最後に1度だけ利用します。

SELECT Number,Name FROM MemberList_A
INTERSECT
SELECT Number,Name FROM MemberList_B
ORDER BY Number;

/* 正常に結果が取得できる
+--------+----------+
| Number | Name     |
+--------+----------+
|      1 | イチロウ |
|      1 | イチロウ |
|      2 | ジロウ   |
|      2 | ジロウ   |
|      3 | サブロウ |
|      3 | ゴロウ   |
|      4 | ハチロウ |
|      4 | シロウ   |
+--------+----------+
*/
INTERSECTのまとめ
  • INTERSECT
    • 指定したテーブルに共通して存在するレコードを取得
  • INTERSECTの注意点
    • ①:指定するカラムの数は同じにすること
    • ②:指定するカラムのデータ型が一致していること
    • ③:ORDER BY句は最後に1つだけ利用すること

システムエンジニアを目指したい方は

システムエンジニアを目指す方や、IT知識を1から身につけたい方は以下のページをご覧ください。

正直どこから学び始めればよいかわからない。どのように勉強していけば、エンジニアとしてのスキルが磨けるか?が分からない・・・という方は必見です。

タイトルとURLをコピーしました