CS 担当者のための SQL 実践編 - サポートタイムズ

f:id:selfree:20230711152559g:plain

CS 担当者が最初に知るべきプログラミング言語 SQL。本記事では SQL のより踏み込んだ内容をご紹介します。

テーブル設計を把握しよう

テーブルとは、それぞれのデータが入れるための箱のようなものです。データを保存するときに、何でもかんでも詰め込むような方法ですと、データの取り出しに時間がかかってしまいます。そのため、「このデータはここに入れる」ためのテーブルを設計をすることで、データをより効率よく取得することが可能になります。

SQL において、自社サービスのテーブル設計を把握することはとても重要です。どのテーブルにどんなデータが入っているのかを知って初めて、データを取得することができるようになります。それでは典型的なテーブル設計を見てみましょう。私たちがブログサービスを運営していると仮定します。

f:id:selfree:20230711152557p:plain

まず、大抵はどんなサービスにもユーザー情報があることでしょう。ユーザーの個別の情報を Users テーブルに保存します。今回は至ってシンプルなメールアドレスでのログインを想定しています。パスワードは生のパスワードではなく、暗号化をかけた状態で保存することが必要です。

以前の記事でご紹介したように、ユーザー情報の一覧を見る場合はSELECT文で取得できます。

SELECT * FROM Users LIMIT 50;

万単位でのユーザーをお持ちのサービスの場合は、LIMITをつけないと全部取ってきてしまうので忘れないようにしましょう。

テーブルを把握する上で重要な"関連"とは

現在、データベースの分類で最もメジャーなのは「リレーショナルデータベース」と呼ばれるものです。"リレーショナル"という言葉にもあるように「関連」という言葉がキーワードとなっています。今回の図で言えば、 Users と Articles の両者を結ぶ線です。これについてご説明します。

まず id には一意になるものを割り当てます。"一意である" とは、何かしらの文字を与えると、必ず一つだけ取得できるような文字のことです。一番手軽な方法は、データを入れるたびに 1 から順番に 1 ずつ増やしていくことです。

例えば Users に以下のようなデータ(行)が入っていたとします。

id, name, email
1, test1, test1@gmail.com
2, test2, test2@gmail.com
3, test3, test3@gmail.com

そして、 Articles には以下のようなデータ(行)が入っていたとします。

id, user_id, title
1, 1, title1
2, 1, title2
3, 2, title3

Articlesにあるuser_id に注目してください。これは、その記事が誰が書いたのかをわかるようにするために保存しています。Articles の1行目は、 user_id が 1であることがわかります。つまり、title1の記事を書いたのは、 test1 さんである という状態がこれで実現できるのです。

例えば test1 さんが書いた記事一覧は以下のような SQL で取得が可能です。

SELECT * FROM Articles WHERE user_id = 1;

テーブルの結合について

今までの SELECT による SQL は、単一のテーブルに対して投げるものだけでした。SELECT の応用例として、結合(JOIN) についてご紹介します。より詳しい話は専門書に譲るとして、本記事では概要を理解できる程度のサンプルをご用意します。

例えば「記事id が 3 のユーザーの id とメールアドレスを取得したい」という場合を考えてみましょう。上の図をみれば、記事idが3 の user_id は 2 だから、User の id が 2であるメールアドレスは test2@gmail.com だ！ということがわかります。これを1つの SELECT 文で実現してみましょう。

この場合、2つのテーブルを結合し、1つのテーブルとして表現する必要があります。以下のような SQL 文となります。

SELECT u.id, u.email 
FROM Users AS u 
INNER JOIN Artciles AS a 
  ON u.id = a.user_id 
WHERE a.id = 3;

一気に複雑になりました。まず AS によってテーブルの別名を指定しています。u. や a. といったような形式でテーブル名を表現することができるようになります。つまり、 SELECT で指定している u.id, u.email は Users の id とメールを取得するということを明示的に示しています。

続いてメインの INNER JOIN。これが内部結合と呼ばれています。 INNER JOIN の後に続く ON で、どのように結合するのかを示します。今回の場合は、 User の id と Articles の user_id が一致する条件で結合しています。私たちがデータを探すときに、「記事idが3 の user_id は 2 だから、User の id が 2である Articles は・・」を探すのとを同じことを表します。

続いての WHERE で記事 id が 3 であるものだけ、という指定をしています。

これにより、「記事id が 3 のユーザーの id とメールアドレスを取得したい」というSQLを実現することができます。