1. 特定の順序でデータを並べ替える: FIELD()関数

SQLのORDER BY句は通常、昇順または降順でデータをソートしますが、特定の値リストに基づいてカスタムの順序でソートしたい場合があります。そのような場合にFIELD()関数が非常に役立ちます。この関数は、指定された文字列がリスト内のどの位置にあるかに応じて数値を返します。

SELECT *
FROM product_catalogs
ORDER BY FIELD(category, '電子機器', '書籍', 'アパレル', '食品'), item_name;

上記の例では、product_catalogsテーブルのデータをcategoryカラムに基づいて並べ替えています。具体的には、「電子機器」、「書籍」、「アパレル」、「食品」という順序でカテゴリーが表示され、それ以外のカテゴリーはリストの後にデフォルトの順序で並べられます。

2. 条件に基づいて新しい列を生成する: CASE式

CASE式を使用すると、クエリ結果に新しい列を追加し、その値を一つ以上の条件に基づいて動的に設定できます。これは、データの分類や表示のカスタマイズに非常に強力なツールです。

SELECT
    order_id,
    customer_id,
    order_total,
    CASE
        WHEN order_total > 10000 THEN '高額顧客'
        WHEN order_total > 5000 THEN '中額顧客'
        ELSE '一般顧客'
    END AS customer_segment
FROM
    sales_orders;

このクエリでは、sales_ordersテーブルからorder_totalに基づいて顧客を「高額顧客」、「中額顧客」、「一般顧客」の3つのセグメントに分類し、customer_segmentという新しい列として表示しています。

3. 関連データの存在を確認する: EXISTS句

EXISTS句は、サブクエリが少なくとも1つの行を返す場合にTRUEとなり、そうでない場合にFALSEを返します。これは、関連するテーブルに特定の条件を満たすレコードが存在するかどうかを効率的にチェックするために使用されます。特に、結合（JOIN）では多くの列が返されるのに対し、EXISTSは真偽値のみを評価するため、大規模なデータセットでパフォーマンス上の利点がある場合があります。

SELECT e.employee_id, e.employee_name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE e.department_id = d.department_id
      AND e.department_name != d.department_name
);

上記のSQLは、employeesテーブル内のdepartment_nameが、対応するdepartmentsテーブルのdepartment_idに基づく実際の部門名と一致しない従業員を特定します。これはデータ整合性のチェックに役立ちます。

4. グループ内の文字列を連結する: GROUP_CONCAT()関数

GROUP_CONCAT()関数は、GROUP BY句によって生成されたグループ内で、指定されたカラムの値を1つの文字列に結合します。連結順序を指定したり、異なる区切り文字を使用したりすることも可能です。

SELECT
    customer_id,
    GROUP_CONCAT(product_name ORDER BY order_date ASC SEPARATOR ' | ') AS purchased_items
FROM
    customer_purchases
GROUP BY
    customer_id;

この例では、各顧客が購入した製品名をcustomer_idでグループ化し、購入日順に並べ替えて「 | 」で区切られた単一の文字列として表示しています。

5. 階層データを扱う: 自己結合

自己結合は、同じテーブルを2回以上結合することです。これは、組織の階層（例：従業員とその上司）、部品構成リスト、または地理的な隣接関係など、テーブル内のデータが自己参照的な関係を持つ場合に非常に役立ちます。

SELECT
    t1.employee_name AS '部門長',
    t2.employee_name AS '課長',
    t3.employee_name AS '担当'
FROM
    employees t1
LEFT JOIN
    employees t2 ON t1.employee_id = t2.manager_id
LEFT JOIN
    employees t3 ON t2.employee_id = t3.manager_id
WHERE
    t1.manager_id IS NULL; -- 最上位の部門長から開始

このクエリでは、employeesテーブルを自己結合して、部門長、課長、担当という3段階の従業員階層を表示しています。manager_id IS NULLの条件で最上位の従業員（部門長）を特定し、そこから下位の階層をたどっています。

6. 関連テーブルからのデータで更新する: マルチテーブルUPDATE

複数のテーブルを結合して、あるテーブルのデータを別の関連テーブルの情報に基づいて更新することができます。これは、データクレンジングや同期のシナリオで特に便利です。

UPDATE
    user_accounts ua,
    user_profiles up
SET
    ua.email = up.primary_email
WHERE
    ua.user_id = up.user_id
    AND ua.email IS NULL; -- emailが未設定のユーザーのみ更新

このSQLは、user_accountsテーブルとuser_profilesテーブルをuser_idで結合し、user_accounts.emailがまだ設定されていない場合に、user_profiles.primary_emailの値でその列を更新します。

7. NULL値のソート順序を制御する: ORDER BYとIF(IS NULL, ...)

ORDER BY句でNULL値を持つカラムをソートする場合、データベースシステムによってはNULL値の扱いが異なります。NULL値を常に先頭または末尾に配置したい場合、IF()関数とIS NULLを組み合わせてその順序を明示的に制御できます。

SELECT
    task_id,
    task_name,
    due_date
FROM
    project_tasks
ORDER BY
    IF(due_date IS NULL, 0, 1), -- NULLのdue_dateを最初に、NULLでないdue_dateを次に
    due_date ASC;               -- NULLでないものはdue_dateの昇順

このクエリは、project_tasksテーブルのタスクをdue_dateで並べ替えます。due_dateがNULLのタスクはリストの先頭に表示され、それ以外のタスクはdue_dateの昇順で続きます。

8. グループ化集計の合計値を生成する: WITH ROLLUP

WITH ROLLUP修飾子は、GROUP BY句と組み合わせて使用され、指定されたグループ化列のすべての組み合わせに対して集計行を生成します。これにより、サブトータルとグランドトータルを単一のクエリで取得できます。

SELECT
    COALESCE(region, '全地域') AS region,
    COALESCE(product_category, '合計') AS product_category,
    SUM(sales_amount) AS total_sales
FROM
    monthly_sales
GROUP BY
    region, product_category WITH ROLLUP;

上記のSQLは、地域と製品カテゴリごとの売上合計を計算し、さらに地域ごとの合計と全体の合計（グランドトータル）を生成します。COALESCE()関数は、ROLLUPによって生成された集計行のNULL値を、「全地域」や「合計」といった意味のあるラベルに置き換えるために使用されます。

9. クエリを構造化し再利用可能にする: 共通テーブル式 (CTE)

共通テーブル式（CTE、WITH AS句）は、複雑なクエリの可読性と保守性を向上させる一時的な名前付き結果セットです。CTEは、同じクエリ内で複数回参照できるため、重複するサブクエリを避けることができます。

WITH HighValueCustomers AS (
    SELECT customer_id, customer_name
    FROM customers
    WHERE total_spend > 10000
), RecentOrders AS (
    SELECT customer_id, order_id, order_date
    FROM orders
    WHERE order_date > DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
)
SELECT
    HVC.customer_name,
    RO.order_id,
    RO.order_date
FROM
    HighValueCustomers HVC
JOIN
    RecentOrders RO ON HVC.customer_id = RO.customer_id
WHERE
    RO.order_id IS NOT NULL; -- 最近の注文がある高額顧客のみ

この例では、2つのCTE、HighValueCustomersとRecentOrdersを定義しています。これらを使って、過去3ヶ月間に注文があった高額顧客の名前、注文ID、注文日を抽出するメインクエリを構築しています。

10. データが存在すれば更新、存在しなければ挿入 (UPSERT): ON DUPLICATE KEY UPDATE

MySQLのINSERT ... ON DUPLICATE KEY UPDATE構文は、「UPSERT」（Update or Insert）操作を実行するための効率的な方法です。テーブルに主キーまたは一意キーの重複が発生した場合に、挿入ではなく更新操作が実行されます。

INSERT INTO user_settings (user_id, theme_color, notification_enabled)
VALUES (101, 'dark', TRUE)
ON DUPLICATE KEY UPDATE
    theme_color = VALUES(theme_color),
    notification_enabled = VALUES(notification_enabled);

-- 別のユーザーで再実行した場合（user_id 101は既に存在）
INSERT INTO user_settings (user_id, theme_color, notification_enabled)
VALUES (101, 'light', FALSE)
ON DUPLICATE KEY UPDATE
    theme_color = VALUES(theme_color),
    notification_enabled = VALUES(notification_enabled);

このSQLは、user_settingsテーブルに新しい設定を挿入しようとします。もしuser_idが既存のレコードと重複する場合（user_idが一意キーであると仮定）、既存のレコードのtheme_colorとnotification_enabledが、VALUES()関数で指定された新しい値で更新されます。