SQL in句のサンプルコード集 複数カラムの指定方法とは?
  • facebookページ
  • twitterページ
  • 2019.10.13

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

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

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

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

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

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

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

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

    SQL実行結果

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

    上記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は部署名

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

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

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

    a以下のSQLは、departments(部署テーブル)のうち、dept_name(部署名)が、’Marketing’、’Sales’、’Development’のデータを抽出し、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を取得しています。

    結果はこうなります。

    SQL実行結果

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

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

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

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

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

    SQL実行結果

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

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

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

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

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

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

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

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

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

    まとめ

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

     



    優良フリーランス案件多数掲載中!
    フリーランスエンジニアの案件をお探しなら
    ポテパンフリーランス

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE