【SQL学習入門】6回目:あまり知られていないけど便利なSQL構文(Access)

Accessで使えるSQL学習入門ということで過去5回に渡って解説をすすめていましたが、最終回の今回は「あまり知られていないけど便利なSQL文」について解説します。

ちなみに、前回の記事はこちらです。
≫ 【SQL学習入門】5回目:Accessテーブルの構成を編集できるデータ定義クエリ

本記事のネタ元は、Microsoft公式サイトの中でAccessのSQL文を解説しているページです。

ALL、DISTINCT、DISTINCTROW、TOP 述語 - Microsoft

本記事では、公式サイトの内容から更に実務を想定して動きを再現しました。
SQLのスキルレベルを上げたい方にとって、きっと実戦で活かせる内容になっていると思います。

さらに、今回の内容はAccessだけでなく他のデータベースシステムにも使えます。

学習の前に(サンプルデータベースの中身)

今回の内容を学習するにあたり、サンプルデータベースをご活用ください。

DBサンプル(その他SQL).accdb

データベースの中に２つのテーブルを作っておきました。

今回主に使うのは「報酬支払実績」テーブルです。
2021年4月から6月まで3ヶ月分の報酬金額データが入力されています。

「派遣会社リスト」テーブルは、「報酬支払実績」テーブルの「派遣会社CD」と紐づいています。

また、今回学習するSQL文は６つのクエリにまとめられています。

クエリのデータシートビューを開けば、SQL文が結果を導き出しているか確認することができます。
また、SQLビューでSQL文(今回の学習内容)を確認できます。

ALL文で便利に検索条件を設定する

AccessにはALL文というSQL文があります。クエリ「01_ALL文1」のSQLビューを開いてみましょう。

SELECT ALL * FROM 報酬支払実績;

データシートビューを表示して、SQL文の結果を見てみましょう。
テーブル「報酬支払実績」と同じ結果になっています。

「SELECT * FROM ～」などのような一般的なSQL文と同じ結果なので、これだけではあまりALL文を使う意味が無いように感じますよね？

しかし、WHERE句で条件を絞り込むときにALL文を使うと、通常のWHERE句では再現できない絞り込みを実現できます。

では、クエリ 「02_ALL文2」のSQLビューを開いてみましょう。

SELECT * FROM 報酬支払実績
 WHERE 報酬支払実績.金額>All
 (SELECT 金額 FROM 報酬支払実績 WHERE 名前 = '鈴木　次郎');

このSQL文を使うと、「鈴木　次郎」さんより報酬が多いレコードを全て抽出できます。

鈴木次郎さんのレコードは3件ありますが、一番金額が高いのは340,000円です。

テーブル「報酬支払実績」の中から、340,000円より高いレコード4件に絞り込まれたものがデータシートビューに表示されます。

次にクエリ「 03_ALL文3」のSQLビューを開いてみましょう。

SELECT * FROM 報酬支払実績
 WHERE 報酬支払実績.金額>All
 (SELECT MIN(金額) FROM 報酬支払実績 WHERE 名前 = '鈴木　次郎');

SQL文は金額をMIN(最小値)に設定してありまよね?「鈴木　次郎」さんの最低報酬額よりも高いレコードが抽出されます。

鈴木次郎さんの最小値は280,000円です

テーブル「報酬支払実績」の中から、280,000円より高いレコード9件に絞り込まれたものがデータシートビューに表示されます。

いずれもWHERE以降にALLを書かれていて、SELECT文が中に入っています。つまり、サブクエリ（副問い合わせ）になっているということです。
デザインビューからも、抽出条件に「ALL(SELECT～)」となっているのが分かります。

今回の例のように「〇〇さんより多い金額のデータを抽出したい」といったような設定をしたいときがありませんか？
そんな時にも、ALL文を使えば数値を指定せずに自動でふさわしい条件を抽出してくれます。

DISTINCT文で重複させない

DISTINCT文はデータの重複を省きたい時に使います。

クエリ「 04_DISTINCT文1」のSQLビューを開いてみましょう。

SELECT DISTINCT 名前 FROM 報酬支払実績;

データシートビューで結果を確認してみると、純粋な名前のリストが表示されていることが分かります。

次に、クエリ「 05_DISTINCT文2」のSQLビューを開いてみましょう。

SELECT DISTINCT 派遣会社リスト.派遣会社名, 報酬支払実績.金額
 FROM 報酬支払実績
 INNER JOIN 派遣会社リスト ON 報酬支払実績.派遣会社CD = 派遣会社リスト.派遣会社CD;

データシートビューで確認してみましょう。

例文の「DISTINCT」抜いて、クエリの中の重複データを調べてみます。

• Aデザイン㈲の300,000円
• C通信㈱の280,000円

全レコード16件の中から2つの重複があります。
一方、DISTINCT文を入れたSQL文の場合では、重複が排除されているので14件になっています。

重複を省くことのできるDISTINCT文もいろいろな場面で使えますが、特に便利なのは検索用のコンボボックスを設置するときです。
「マスタに登録されているけどテーブルに入力されていないデータ」は検索リストに不要なムダなデータです。
コンボボックスを使ってリストから選択するときは、もちろんムダなデータが無い方がいいですよね？
更に、新しい名前がテーブルに追加されれば、リストに自動追加までしてくれます。
DISTINCT文を上手に使えば、より使いやすいコンボボックスに仕上げることができるのです。

TOP文を使って上位のデータを表示する

TOP文はその言葉通りですが、上位の数を指定して表示させるために使われます。

本当に必要な情報を取り出すためには、さらにSQL文に手を加える必要があります。
それは、ORDER BYを使ってデータを並び替えておくということです。

クエリ「07_TOP文2」をSQLビューで開いてみます。

SELECT TOP 5 年月, 名前, 派遣会社CD, 金額
 FROM 報酬支払実績
 ORDER BY 金額 DESC;

このように金額で降順(DESC)に並び替えておくと、金額の大きいトップ5を抽出できます。

逆に昇順(ASC)を指定すれば、金額が少ないワースト5が抽出されます。

それでは、仮にORDER BY文を書き忘れた場合はどのような結果になるのでしょうか？
クエリ「06_TOP文1」のSQLビューを確認してみましょう。

SELECT TOP 5 報酬支払実績.年月, 報酬支払実績.名前, 報酬支払実績.派遣会社CD, 報酬支払実績.金額
 FROM 報酬支払実績;

データシートビューで表示されてみると、金額が高い順で情報が抽出されていないですよね。

どのような規則になっているのか分からないので、いったん「TOP 16」に変えて全レコードを表示してみます。
どうやら一列目から順番に昇順設定されているようです。

このように、TOP文を使うとき「ORDER BY」句を忘れると、思わぬ結果を導くことがありますので気をつけましょう。

まとめ

Micosoft公式サイトには「DISTINCTROW」という構文も紹介されていますが、残念ながら今回はマスターできませんでした。
後日しっかりと理解できたところで追記していきたいと思います。

これで、５回にわたり投稿してきたＳＱＬ入門編はこれで終了です。
デジタル化の加速により、データベースはこれからもいろいろな分野で活用されることは避けられません。
データベース内のデータを思い通りに操ることができるようになれば、さまざまな業種で貴重な戦力となることができるでしょう。
当ブログではMicrosoft Accessを通してデータベースについての情報発信を行っていますので、今後もたまに覗いていただけるとうれしいです。

本日もありがとうございました。