やりたいこと
なんかExcelとかでリスト渡されて、「このリストのうちDBに入ってないIDだけ抜き出しといて」みたいに依頼されることありませんかね。
SQLで直接やろうにも、
select * from table where id in (123, 456・・)
だとDBに無いものは取れないし、
select * from table where id NOT in (123, 456・・)
だとDBのテーブル全体から無いものが取れるのでリストのうち無いものは取れません。
どうすればいいのか・・fromの中に直接IDたちを書ければいいのに・・
書けるんです。
fromに直接値を指定する
postgresqlでしか試してないですが、fromにvaluesが書けます。
select * from (values(123),(456)・・・)as tmp(id)
一個一個の値を()でくくらないといけないのがめんどくさいですがこれを使えばleft joinが使えるわけです。
select * from (values(123),(456)・・・)as tmp(id)
left join table on tmp.id = table.id
こいつの結果はこんな感じになるかと思います。
| tmp.id | table.id | table.name |
|---|---|---|
| 123 | 123 | hoge |
| 456 | <null> | <null> |
| 789 | 789 | fuga |
| ・・・ | ・・・ | ・・・ |
leftなので、from(values)で指定した一覧をもとに結果が出ます。
それで、今回の要求的にはDBに存在するものはいらないので、
select * from (values(123),(456)・・・)as tmp(id)
left join table on tmp.id = table.id
where table.id is null
としてやれば
| tmp.id | table.id | table.name |
|---|---|---|
| 456 | <null> | <null> |
| ・・・ | ・・・ | ・・・ |
こんな感じにリストにあるけどDBに無い一覧がリストをもとに取ることができるはずです。
リストにあるIDたちを(123),(456)・・と整形するのが少し面倒ですが
やりたいことが実現できたので残しときます。
おまけ
ググれば出てきますが、from(values)に複数値指定した場合はこんな感じで書けるみたいです。
SELECT f.*
FROM films f, (VALUES('MGM', 'Horror'), ('UA', 'Sci-Fi')) AS t (studio, kind)
WHERE f.studio = t.studio AND f.kind = t.kind;
これ型とかどう解釈されるんだろ。