SQL in句のサンプルコード集 複数カラムの指定方法とは？

SQLのin句のサンプルコードを紹介致します。

なお、MySQLのサンプルデータベースEmployeesを、SQL実行結果の表示にはphpMyAdminを使って解説しています。

SQLのin句の基本的な使い方

in句の基本的な構文は、以下の通りです。

inで指定したリストにデータが含まれていれば真となります。

select カラム名 from テーブル名
where カラム名 in ("文字列1","文字列2"…）

以下のSQLは、departments（部署名テーブル）から、dept_name（部署名）が「Finance」または「Marketing」のデータのみを取得します。

SELECT * FROM `departments` WHERE dept_name in ("Finance","Marketing")

実行結果はこうなります。

SQL実行結果

inで指定したリスト以外という条件にするには、not inを使います。

SELECT * FROM `departments` WHERE dept_name not in ("Finance","Marketing")

上記SQLは、「Finance」「Marketing」以外のデータを取得します。

SQL in句のサンプルコード集

SQL in句で、複数カラムを指定するには

inで指定するリストは、複数カラムのペアを指定可能です。

以下のSQLは、departments（部署名テーブル）から、以下の条件を満たすデータを取得します。

• dept_noがd001で、dept_nameがMarketing
• dept_noがd002で、dept_nameがProduction

※dept_noは部署番号、dept_nameは部署名

SELECT * FROM `departments` 
where (dept_no,dept_name) in ( ("d001","Marketing"), ("d004","Production") )

上の例は、in句で2カラムを指定しましたが、3カラム、4カラム以上の指定も可能です。

SQL in句で、inリスト項目を指定順にソートするにはORDER BY FIELD

MySQL前提ですが、以下のようにorder byとfieldを組み合わせ、任意のキーワードでソートが可能です。

ORDER BY FIELD(カラム名, キーワード1,キーワード2…)

a以下のSQLは、departments（部署テーブル）のうち、dept_name（部署名）が、’Marketing’、’Sales’、’Development’のデータを抽出し、dept_nameを’Marketing’、’Sales’、’Development’の順にソートします。

SELECT * FROM `departments`
where dept_name in ('Marketing','Sales', 'Development')
order by field( dept_name, 'Marketing','Sales', 'Development')

order by fieldはin句と組み合わせずに単体でも使用可能です。

OracleやPostgres、SQLServerにはfieldに相当する機能はありません。

他のDBMSでは、case式での置き換えが可能です。

参考）ORDER BYで、単純な昇順降順「以外」で並べる！

なお、プログラムやSQL内にソート情報を決め打ちで持つことはプログラムのメンテナンス性の低下やバグにもつながります。できれば、コード側で固有リストを持たなくても良いように、データベースを設計するべきでしょう。

SQL in句 のリストを副問い合わせで指定するには

in句のリストを副問い合わせ（サブクエリ）で指定することも可能です。

以下のSQLは、departments（部署テーブル）から、3万人以上の社員がいる部署のデータのみ取得します。

サブクエリでは、dept_emp（部署・社員紐付けテーブル）をdept_no（部署番号）でグルーピングし、所属社員数30,000以上の部署のdept_noを取得しています。

select * from departments 
where departments.dept_no in (
		SELECT dept_no
		from dept_emp 
		group by dept_no 
		having count(*) > 30000
	)

結果はこうなります。

SQL実行結果

なお、サブクエリでカラムを2つ以上返す場合は、下記のエラーがでます。

#1241 - Operand should contain 1 column(s)

エラーの意味は「カラム数が不正」と考えておいて良いでしょう。

例えば、以下のSQLは、サブクエリでdept_noとcntの2カラムを返すため、エラーとなります。

select * from departments 
where departments.dept_no in (
		SELECT dept_no,count(*) as cnt
		from dept_emp 
		group by dept_no 
		having cnt > 30000
	)

SQLのin句のサブクエリを高速化するには、結合に置き換える

select * from departments 
where dept_no in ( select dept_no from dept_emp where emp_no=10001 )

上記SQLは、emp_no（社員番号）が100001の社員が所属する部署の情報をdepartments（部署テーブル）から取得するSQLです。サブクエリ内で、dept_emp（部署・社員紐付テーブル）から、emp_no（社員番号）が10001の社員のdept_no（部署番号）を取得しています。

SQL実行結果

in句とサブクエリの組み合わせは、条件の複雑化やデータの件数によって極端にパフォーマンスが落ちる可能性があります。

SQLを外部結合（left outer join）に書き換えることで、最適化の可能性があります。

select departments.dept_no, dept_name from departments 
left join dept_emp on departments.dept_no=dept_emp.dept_no
where emp_no=10001

書き換えによって、実際に最適化できるかどうかは、explain（実行計画）を確認しましょう。

SQLのin句で使える最大数はいくつ？

Oracleでは1000個、MySQLでは最大パケットサイズに依存（SQL全体の長さの制限）という情報が見つかりました。

参考）SQL IN句に1000件以上要素を指定したときのエラーの対処法（Oracle)

参考）in句に指定できる個数の制限について – MySQL初心者日記 – MySQLグループ

仕様としては充分な上限と考えられますが、SQL内で1000個のものinリストを含むのは、著しくメンテナンス性を欠くと言えるでしょう。

ある程度大きなサイズのリストは、テーブルなどに外出しするのが妥当でしょう。

まとめ

ポテパンダの一言メモ

• SQLのin句を使うと、任意のリストを条件として使える
• SQLのin句では、複数カラムのペア指定が可能
• in句で指定したキーワードでのソートが可能
• in句のサブクエリは結合表現への書き換えで速度改善の可能性あり