PR

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

Database

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

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

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

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

スポンサーリンク

SQL:INTERSECT

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

SELECT カラム1, カラム2, ...
FROM テーブル1
INTERSECT
SELECT カラム1, カラム2, ...
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つだけ利用すること

employeeテーブル

employee_idnamedepartment
1AliceHR
2BobIT
3CarolHR
4DavidIT

contractorテーブル

contractor_idnamedepartment
1EricHR
2AliceHR
3FrankIT
4BobIT
SELECT name, department
FROM employees
INTERSECT
SELECT name, department
FROM contractors;

結果

namedepartment
AliceHR
BobIT

SQLを1から学習したい方は

SQLやデータベースの仕組みを1から学習したい方(学び直したい方)向けに、現役エンジニア達のスキルを結集して 完全無料 のSQL教材を作成しました。

SQLは決して難しい技術ではないので、エンジニアであれば「当たり前のように」扱えて当然かも・・・?

とはいえ、案外SQLをちゃんと使ったことがない人も多いはずです。この機会に是非一度ご覧になってみてください。

このWebサイトは現役のエンジニアが以下3点を目的として運営しています。

  1. 勉強:一度理解した内容を忘れないように。
    → アウトプットは「最強のインプット」である! 
  2. 備忘:忘れたとしても後から見返せるように。
    → 未来の自分への「お手紙」を書いています。 
  3. 共有:〇〇ってこうだったんだ!の感動をシェアできるように。
    → あなたの知識は誰かにとっての「価値ある情報」です。 

副業ブログの始め方はこちらから

スポンサーリンク
DatabaseIT-Skills
シェアする
ビズドットオンラインをフォローする
タイトルとURLをコピーしました