SQLで最新のレコードを取得する3つの方法

こんにちは、やっと花粉症がおさまってきたシステムエンジニアのリョータです。
今年もすでに4ヶ月目に突入し、新たなスタートを切った方も多いのではないでしょうか。

今回は、最新のレコードを取得するSQLについて、解説を3つほどご紹介したいと思います。
下記のSQL文は、私がSQLを仕事で使い始めてからつまずいたもので、初心に戻ってまとめようと思ったのがきっかけです。
3つともサブクエリを使ったSQLとなっています。是非、ご参考にしてみてください。

条件

下のSQLは、全社員の所属部署(staff_section)テーブルの最新を取るものです。
社員番号(staff_no)が社員のキーになり、開始日(start_date)は必ず入力されています。
所属期間はかぶらないものとします。

社員番号(staff_no)でレコードが絞れる(※)なら、社員番号にインデックスを張ると、コストが改善されます。
一般的にはヒット率が全体の15%以下の場合。

SQLと解説

それぞれのSQLと解説をしていきます。
どのSQLにも一長一短なところがあるので、適宜使っていきましょう。

SQL1:最大の開始日と比較


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
)
WHERE句の開始日の条件に「=」のサブクエリを使いました。
MySQLでは動きましたが、他ではエラーになるかもしれません。
そのときは、「=」を「IN」とするとうまくいくかもしれません。

クエリの考え方はシンプルです。

ss01テーブルで取得したレコードのうち、同じ社員番号の最大の開始日と等しいレコードを取得します。

欠点として、「=(IN)」で比較しているため、条件が複合化した場合に対応できません。

SQL2:最大の開始日のレコードが存在する


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
)
WHERE句にEXISTSを使いました。

ss01テーブルで取得したレコードのうち、
同じ社員番号の最大の開始日と等しいレコードが存在する場合にレコードを取得します。

SQL1と比べて、条件が複合化してもHAVING句で対処できます。
ですが、このSQLでは必ず所属部署テーブルにレコードが存在する必要があります。

所属が決まっておらずレコードを登録しないレコード(まだ配属の決まっていない新入社員がいるなど)があると、取得できなくなります。

SQL3:自身の開始日よりも大きな開始日は存在しない


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
)
WHERE句にNOT EXISTSのサブクエリを使いました。

ss01テーブルで取得したレコードの開始日より、開始日が大きいものがss02テーブルに存在しないレコードを取得しています。

このSQLの特徴は、所属部署テーブルに存在しない社員も表示されることです。
レコードがない可能性がある場合は、こちらを使いましょう。

MAX(start_date)から、NOT EXISTSへの変換は、
「開始日が一番大きい(最新)」から
「(自身の)開始日より大きい開始日は存在しない」
という読み替えによって行っています。

やってはいけないSQL

MySQLですと、このSQLもSQL3と同じ結果を返すことがあります。


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.no
「返すことがあります。」と書いたのは、私が実行した環境では同じように出力されましたが、別の環境ではわからないためです。

FROM句のサブクエリで開始日降順でソートし、社員番号でグループすると1番最初のレコードが取得されるらしく、同じ結果となるようです。

ただし、ソートには非常にコストがかかりますし、仕様も推測でしかなく、結果が異なる場合もあり得るので、使わないようにしましょう。

おわりに

MAXをMINに(>を<に)変更することで、最古のデータを取得することもできます。

MySQLでは、敬遠されていた歴史もあるサブクエリですが、十分に早くなりましたし、どんどん使っていきたいですね。

ではまた。
DXO株式会社

DXO株式会社

〒103-0014
東京都中央区日本橋蛎殻町2-13-6
EDGE水天宮8F
E-Mail : contact-info@dxo.co.jp
URL : https://dxo.co.jp