組み合わせデータの操作に便利なタプル条件について

こんにちわ、株式会社THIRD IT事業部の岩見です。

GWで10連休を取った勢いで、業務の覚書きをポストします。
出かけるところがない...。

タプル条件の説明

皆さんはWebアプリケーション構築で、SQLのタプル条件って使ったことありますでしょうか？
僕は最近知りました。
エンジニア経歴はある程度長いのですが、使う機会がありませんでした。

どういうものかというと、こういう感じのものです。

sql

SELECT * FROM tablename WHERE (column1, column2) = ('value1', 'value2');

これって、以下のSQLと同等なんですよね。

sql

SELECT * FROM tablename WHERE column1 = 'value1' AND column2 = 'value2';

これって何が良い？

じゃあ、別にいらないじゃん、と思いがちですが、スマートに書けた気になる他に、明確なメリットもありました。
それは複数レコードに対して特定の条件を指定する時、インデックスを効かせやすいということです。

ご存知の方も多いと思いますが、SQL上でOR条件を使うと、インデックスが効かないケースが少なからずあります。これを回避するためには、ORをIN()に書き換えるチューニングが一般的となっています。

usersテーブルのrole_idにインデックスが貼ってあるとして、

sql

select
  *
from
  users 
where
  role_id = 1 or role_id = 2

sql　

select
  *
from users 
where
  role_id in(1,2）

上記2つでインデックス適用を期待できるのは、二番目の方です。ORを使用すると、オプティマイザがよしなに解釈してくれる場合を除き、遅くなるリスクがあります。（インデックスマージが適用される場合などは、そこまで遅くならないかもしれません）

具体例

さきほど説明した 「複数レコードに対して特定条件でSELECTする時」
について具体例を挙げてみます。
例えば、次のようなユーザーに対する役割を管理するテーブルがあるとします。

roles

id | name
-------------
1  | 管理者
2  | 業務担当者
3  | 飲み会幹事

user_roles

user_id | role_id
-----------------
1       | 1
1       | 3      
2       | 3      

ここから、
「user_idが1でrole_idが1」のデータと、「user_idが2でrole_idが3」のデータを
まとめて取得したいと思うと、どのようなSQLになるでしょうか？

SELECT
    user_id, role_id
FROM
    role_user
WHERE
    user_id IN (1,2) AND role_id IN (1, 3);

このSQLでは、期待する結果が得られません。
次のように、user_id=1の取得結果には、飲み会幹事の役割も現れてしまいます。

取得結果例

user_id | role
-----------------
1       | 管理者
1       | 飲み会幹事      
2       | 飲み会幹事      

正しい結果を取得するには以下の形が必要です。

SELECT
    user_id, role_id
FROM
    role_user
WHERE
    (user_id = 1 AND role_id = 1) OR (user_id = 2 AND role_id = 3)

取得結果例

user_id | role
-----------------
1       | 管理者
2       | 飲み会幹事      

しかしこれだと、先ほど書いたようにORの条件句を使用するので、インデックスが効かないリスクがあります。

なのでここでタプル条件を使えば、インデックスが効く形に持っていくことができます。

SELECT
    user_id, role_id
FROM
    role_user
WHERE
    (user_id, role_id) IN ((1,1), (2,3))

まとめ

以上が今回お伝えしたいことのすべてです！
大量データのテーブルから、このような形で必要分の組み合わせ結果を得たい時には
活用できるのではないでしょうか。

ここまでお読み頂きありがとうございました！