SQL総復習① SELECT文
こんにちは!
スマレジ テックファームのMichiです!
最近、フレームワークのORマッパーばかり使っているせいで、SQLの書き方を忘れてしまったので、 今週からしばらくは「SQL総復習」と称して、基礎的な内容から振り返っていきたいと思います。
1回目の当記事では、SQLの基本 SELECT
文について学びます。
SELECT文の記述
サンプルテーブル
ここでは、customers
とorders
という、2つのテーブルを例にします。
customers +----+------------+--------------+ | id | first_name | last_name | +----+------------+--------------+ | 1 | John | Smith | | 2 | Jane | Doe | | 3 | Mike | Johnson | | 4 | Lisa | Brown | +----+------------+--------------+ orders +----+-------------+--------+--------+ | id | order_date | amount | customer_id | +----+-------------+--------+--------+ | 1 | 2023-01-01 | 100 | 1 | | 2 | 2023-01-02 | 200 | 1 | | 3 | 2023-01-03 | 300 | 2 | | 4 | 2023-01-04 | 400 | 3 | | 5 | 2023-01-05 | 500 | 3 | | 6 | 2023-01-06 | 600 | 4 | +----+-------------+--------+--------+
では、SELECT
文の記述について、個別に見ていきましょう。
尚、SELECT
とFROM
は基本中の基本なので解説は省きます。
JOIN
JOIN
を使用すると、2つ以上のテーブルを結合することができます。
結合条件は、ON
キーワードを使用して指定します。
次のクエリは、customers
テーブルとorders
テーブルをcustomers_id
列で結合した結果を表示します。
SELECT c.first_name, c.last_name, o.order_date, o.amount FROM customers AS c JOIN orders AS o ON c.id = o.cust_id; 出力: +------------+--------------+-------------+--------+ | first_name | last_name | order_date | amount | +------------+--------------+-------------+--------+ | John | Smith | 2022-01-01 | 100 | | John | Smith | 2022-01-02 | 200 | | Jane | Doe | 2022-01-03 | 300 | | Mike | Johnson | 2022-01-04 | 400 | | Mike | Johnson | 2022-01-05 | 500 | | Lisa | Brown | 2022-01-06 | 600 | +------------+--------------+-------------+--------+
尚、JOIN
を含むSELECT
句では、c.first_name
のように、<テーブルの別名>.<列名>
という形式で記述します。
これは、どのテーブルからどの列を持ってきているかを分かりやすくするための措置です。
列名が被らない場合は、<テーブルの別名>
の部分は省略できます(この例では、id
カラム以外すべて省略可能)。
しかし、分かりやすさという点からも、すべての列を<テーブルの別名>.<列名>
の形式で記述する方がよいでしょう。
WHERE
WHERE
を使用すると、指定された条件を満たす行を選択できます。
次のクエリは、amount
列が300以上の注文を選択します。
SELECT * FROM orders WHERE amount >= 300; 出力: +----+-------------+--------+--------+ | id | order_date | amount | cust_id +----+-------------+--------+--------+ | 3 | 2023-01-03 | 300 | 2 | | 4 | 2023-01-04 | 400 | 3 | | 5 | 2023-01-05 | 500 | 3 | | 6 | 2023-01-06 | 600 | 4 | +----+-------------+--------+--------+
WHERE
句の条件指定には、比較演算子のほかに、BETWEEN
での範囲指定やLIKE
でのあいまい検索が使用できます。
GROUP BY
GROUP BY
を使用すると、指定された列の値に基づいて結果をグループ化できます。
次のクエリは、customer_id
列に基づいて注文をグループ化し、各顧客の合計注文金額を計算します。
SELECT customer_id, SUM(amount) as total_amount FROM orders GROUP BY customer_id; 出力: +-------------+--------------+ | customer_id | total_amount | +-------------+--------------+ | 1 | 300 | | 2 | 300 | | 3 | 900 | | 4 | 600 | +-------------+--------------+
HAVING
HAVING
を使用すると、GROUP BY
でグループ化された結果に対して、指定された条件を適用できます。
いわば、WHERE
のグループ化バージョンでしょうか。
次のクエリは、各顧客の合計注文金額が500以上の顧客のみを選択します。
SELECT customer_id, SUM(amount) as total_amount FROM orders GROUP BY customer_id HAVING total_amount >= 500; 出力: +-------------+--------------+ | customer_id | total_amount | +-------------+--------------+ | 3 | 900 | | 4 | 600 | +-------------+--------------+
ORDER BY
ORDER BY
を使用すると、結果を指定された列の値に基づいてソートできます。
ORDER BY <指定したい列名>
の後に、ASC
(昇順)かDESC
(降順)かを指定します。
尚、省略した場合は、ASC
(昇順)になります。
次のクエリは、last_name
列に基づいて顧客をアルファベットの昇順にソートします。
SELECT * FROM customers ORDER BY last_name ASC; 出力: +----+------------+--------------+ | id | first_name | last_name | +----+------------+--------------+ | 4 | Lisa | Brown | | 2 | Jane | Doe | | 3 | Mike | Johnson | | 1 | John | Smith | +----+------------+--------------+
尚、ORDER BY
を指定しなかった場合、出力は一見するとid
列の昇順であるかのように見えます。
SELECT * FROM customers; 出力: +----+------------+--------------+ | id | first_name | last_name | +----+------------+--------------+ | 1 | John | Smith | | 2 | Jane | Doe | | 3 | Mike | Johnson | | 4 | Lisa | Brown | +----+------------+--------------+
しかし、これはただの偶然にすぎません。
実際は、ORDER BY
を指定しなかった場合の出力は完全なランダムであり、次回も同じ順で表示される保証はないのです。
したがって、基本的には毎回ORDER BY
は指定することをおすすめします。
LIMIT
LIMIT
を使用すると、結果の行数を制限できます。
LIMIT
キーワードの後にoffset
値(何番目のデータから取得するのか。省略した場合は0)とcount
値(取得する行数)を指定します。尚、offset
値は指定した値+1行目のデータから取得するので注意してください。
次のクエリは、顧客テーブルから最初の2行のみを選択します。この場合、offset
値には0を指定しているので、1行目のデータから取得するという意味になります。
SELECT * FROM customers LIMIT 0, 2; 出力: +----+------------+--------------+ | id | first_name | last_name | +----+------------+--------------+ | 1 | John | Smith | | 2 | Jane | Doe | +----+------------+--------------+
記述順と実行順
記述順
さて、先ほど解説した順は、実際のクエリの記述順になっています。
複数の句を同時に記述する場合、この順で書かないと動きません。
- SELECT(取得するカラムを指定)
- FROM(対象となるテーブルを指定)
- JOIN(テーブルの結合条件を指定)
- WHERE(絞り込みの条件を指定)
- GROUP BY(グループ化の条件を指定)
- HAVING(グループ化した後の絞り込み条件を指定)
- ORDER BY(並び替えの条件を指定)
- LIMIT(取得する行数を制限)
実行順
一方で、クエリの実行順は記述順とは異なります。
具体的には次の通りです。
- FROM(対象となるテーブルを指定)
- JOIN(テーブルの結合条件を指定)
- WHERE(絞り込みの条件を指定)
- GROUP BY(グループ化の条件を指定)
- HAVING(グループ化した後の絞り込み条件を指定)
- SELECT(取得するカラムを指定)
- ORDER BY(並び替えの条件を指定)
- LIMIT(取得する行数を制限)
見た目の並び順とDB内部の実行順が異なるのです。
これがSELECT
文のややこしいところですね。
すべてを一度に実行する
これらすべての句を、一つのクエリとして記述する場合は次のようになります。
SELECT c.id, c.first_name, c.last_name, SUM(o.amount) AS total_amount FROM customers AS c JOIN orders AS o ON c.id = o.customer_id WHERE o.order_date >= '2023-01-03' GROUP BY c.id HAVING total_amount >= 500 ORDER BY c.last_name ASC LIMIT 0, 2;
このクエリを実行順に解説すると、
customers
テーブルを参照する(FROM)- 1.に
orders
テーブルを結合する(JOIN) - 2023年1月3日以降の注文に絞り込む(WHERE)
- 顧客idごとに注文をグループ化する、(GROUP BY)
- 注文の合計額が500ドル以上である顧客(レコード)に絞り込む(HAVING)
- 5.までに絞り込まれた条件に合致するレコードを取得(SELECT)
- 6.の結果を
last_name
によって昇順で並べ替える(ORDER BY) - 出力時は、最大で2つの行を返す (LIMIT)
となるわけです。
よって、出力はこうなります。
出力: +----+------------+-----------+--------------+ | id | first_name | last_name | total_amount | +----+------------+-----------+--------------+ | 3 | Mike | Johnson | 900 | | 4 | Lisa | Brown | 600 | +----+------------+-----------+--------------+
まとめ
SQL総復習 第1回目は、基本のSELECT
文についておさらいしました。
ここまでお読みいただきありがとうございました。