今年もすでに4ヶ月目に突入し、新たなスタートを切った方も多いのではないでしょうか。
今回は、最新のレコードを取得するSQLについて、解説を3つほどご紹介したいと思います。
下記のSQL文は、私がSQLを仕事で使い始めてからつまずいたもので、初心に戻ってまとめようと思ったのがきっかけです。
3つともサブクエリを使ったSQLとなっています。是非、ご参考にしてみてください。
条件
下のSQLは、全社員の所属部署(staff_section)テーブルの最新を取るものです。社員番号(staff_no)が社員のキーになり、開始日(start_date)は必ず入力されています。
所属期間はかぶらないものとします。
社員番号(staff_no)でレコードが絞れる(※)なら、社員番号にインデックスを張ると、コストが改善されます。
一般的にはヒット率が全体の15%以下の場合。
SQLと解説
それぞれのSQLと解説をしていきます。どのSQLにも一長一短なところがあるので、適宜使っていきましょう。
SQL１：最大の開始日と比較
SELECT
*
FROM
staff sf01
LEFT OUTER JOIN
staff_section ss01
ON
ss01.staff_no = sf01.no
WHERE
ss01.start_date = (
SELECT
MAX(ss02.start_date)
FROM
staff_section ss02
WHERE
ss02.staff_no = ss01.staff_no
GROUP BY
ss02.staff_no
)
MySQLでは動きましたが、他ではエラーになるかもしれません。
そのときは、「=」を「IN」とするとうまくいくかもしれません。
クエリの考え方はシンプルです。
ss01テーブルで取得したレコードのうち、同じ社員番号の最大の開始日と等しいレコードを取得します。
欠点として、「=(IN)」で比較しているため、条件が複合化した場合に対応できません。
SQL２：最大の開始日のレコードが存在する
SELECT
*
FROM
staff sf01
LEFT OUTER JOIN
staff_section ss01
ON
ss01.staff_no = sf01.no
WHERE
EXISTS (
SELECT
*
FROM
staff_section ss02
WHERE
ss02.staff_no = ss01.staff_no
GROUP BY
ss02.staff_no
HAVING
MAX(ss02.start_date) = ss01.start_date
)ss01テーブルで取得したレコードのうち、
同じ社員番号の最大の開始日と等しいレコードが存在する場合にレコードを取得します。
SQL１と比べて、条件が複合化してもHAVING句で対処できます。
ですが、このSQLでは必ず所属部署テーブルにレコードが存在する必要があります。
所属が決まっておらずレコードを登録しないレコード(まだ配属の決まっていない新入社員がいるなど)があると、取得できなくなります。
SQL３：自身の開始日よりも大きな開始日は存在しない
SELECT
*
FROM
staff sf01
LEFT OUTER JOIN
staff_section ss01
ON
ss01.staff_no = sf01.no
WHERE
NOT EXISTS (
SELECT
*
FROM
staff_section ss02
WHERE
ss02.staff_no = ss01.staff_no
AND ss02.start_date > ss01.start_date
)ss01テーブルで取得したレコードの開始日より、開始日が大きいものがss02テーブルに存在しないレコードを取得しています。
このSQLの特徴は、所属部署テーブルに存在しない社員も表示されることです。
レコードがない可能性がある場合は、こちらを使いましょう。
MAX(start_date)から、NOT EXISTSへの変換は、
「開始日が一番大きい(最新)」から
「(自身の)開始日より大きい開始日は存在しない」
という読み替えによって行っています。
やってはいけないSQL
MySQLですと、このSQLもSQL３と同じ結果を返すことがあります。
SELECT
*
FROM
staff sf01
LEFT OUTER JOIN
(
SELECT
*
FROM
staff_section ss02
ORDER BY
ss02.staff_no,
ss02.start_date DESC
) ss01
ON
ss01.staff_no = sf01.no
GROUP BY
sf01.noFROM句のサブクエリで開始日降順でソートし、社員番号でグループすると1番最初のレコードが取得されるらしく、同じ結果となるようです。
ただし、ソートには非常にコストがかかりますし、仕様も推測でしかなく、結果が異なる場合もあり得るので、使わないようにしましょう。
おわりに
MAXをMINに(>を<に)変更することで、最古のデータを取得することもできます。MySQLでは、敬遠されていた歴史もあるサブクエリですが、十分に早くなりましたし、どんどん使っていきたいですね。
ではまた。
