【SQL】BETWEEN完全ガイド｜範囲指定の基本・NOT BETWEEN・>=AND<=との違い・日付/文字列・NULL・UPDATE/DELETE・パフォーマンスまで

SELECT 列名
FROM テーブル名
WHERE 列名 BETWEEN 下限値 AND 上限値;

-- 意味: 列名 >= 下限値 AND 列名 <= 上限値（両端を含む）

SELECT employee_id, name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 70000;
-- 50000 と 70000 も含まれる

-- >= AND <= で書いた場合（同じ結果）
SELECT employee_id, name, salary
FROM employees
WHERE salary >= 50000 AND salary <= 70000;

-- 20歳以上30歳以下
SELECT * FROM users
WHERE age BETWEEN 20 AND 30;

-- 数量が100〜500の在庫
SELECT product_id, stock_quantity
FROM inventory
WHERE stock_quantity BETWEEN 100 AND 500;

-- NG: 上限と下限が逆 → 結果は0件
SELECT * FROM employees
WHERE salary BETWEEN 70000 AND 50000;
-- 内部的に salary >= 70000 AND salary <= 50000 → 常にFALSE

-- OK: 正しい順序
SELECT * FROM employees
WHERE salary BETWEEN 50000 AND 70000;

-- 2024年3月の注文を抽出
SELECT order_id, order_date, amount
FROM orders
WHERE order_date BETWEEN '2024-03-01' AND '2024-03-31';

-- 直近7日間
-- MySQL
SELECT * FROM access_logs
WHERE log_date BETWEEN CURDATE() - INTERVAL 7 DAY AND CURDATE();

-- PostgreSQL
SELECT * FROM access_logs
WHERE log_date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND CURRENT_DATE;

-- SQL Server
SELECT * FROM access_logs
WHERE log_date BETWEEN DATEADD(DAY, -7, CAST(GETDATE() AS DATE)) AND CAST(GETDATE() AS DATE);

-- NG: 3月31日の 00:00:01 以降のデータが漏れる
SELECT * FROM orders
WHERE created_at BETWEEN '2024-03-01' AND '2024-03-31';
-- → '2024-03-31 00:00:00' までしか含まれない
-- → '2024-03-31 09:30:00' のデータが取れない！

-- OK: 上限を翌日未満にする（推奨）
SELECT * FROM orders
WHERE created_at >= '2024-03-01'
  AND created_at <  '2024-04-01';  -- 翌月1日の「未満」

-- OK: 上限に時刻を明示する
SELECT * FROM orders
WHERE created_at BETWEEN '2024-03-01 00:00:00' AND '2024-03-31 23:59:59';
-- ※ マイクロ秒を含むDATETIME(6)では 23:59:59.999999 まで必要

-- アルファベットの範囲（A〜Mで始まる名前）
SELECT name FROM employees
WHERE name BETWEEN 'A' AND 'M';
-- 'Alice', 'Bob', 'Charlie', 'Kate' は含まれる
-- 'Mike' は 'M' < 'Mike' なので含まれる（'M' 以上）
-- 'Nancy' は含まれない（'N' > 'M'）

-- 注意: 'M' と 'Mz' の間も含まれる
SELECT name FROM employees
WHERE name BETWEEN 'A' AND 'Mz';
-- 'Mike', 'Moses' も含まれる

-- 給与が50,000〜70,000の範囲外（50,000未満 または 70,000超）
SELECT employee_id, name, salary
FROM employees
WHERE salary NOT BETWEEN 50000 AND 70000;
-- 内部的に salary < 50000 OR salary > 70000

-- 3月以外の注文
SELECT * FROM orders
WHERE order_date NOT BETWEEN '2024-03-01' AND '2024-03-31';

-- NOT BETWEEN
WHERE salary NOT BETWEEN 50000 AND 70000
-- ↓ 同じ意味
WHERE salary < 50000 OR salary > 70000
-- ↓ NOT で囲んだ場合も同じ
WHERE NOT (salary >= 50000 AND salary <= 70000)

-- 「10以上20未満」— BETWEEN では表現不可
SELECT * FROM products
WHERE price >= 10 AND price < 20;

-- 「10より大きく20以下」
SELECT * FROM products
WHERE price > 10 AND price <= 20;

-- salary が NULL の行はどちらにも含まれない
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 70000;
-- salary が NULL → FALSE（UNKNOWN）→ 結果に含まれない

SELECT * FROM employees WHERE salary NOT BETWEEN 50000 AND 70000;
-- salary が NULL → FALSE（UNKNOWN）→ こちらにも含まれない

-- NULL も含めたい場合は明示的に OR を追加
SELECT * FROM employees
WHERE salary BETWEEN 50000 AND 70000
   OR salary IS NULL;

-- 給与50,000〜60,000の従業員に5%昇給
UPDATE employees
SET salary = salary * 1.05
WHERE salary BETWEEN 50000 AND 60000;

-- 特定期間の注文ステータスを更新
UPDATE orders
SET status = 'archived'
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

-- 1年以上前のアクセスログを削除
DELETE FROM access_logs
WHERE log_date BETWEEN '2022-01-01' AND '2023-03-31';

-- IDが100〜200の仮データを削除
DELETE FROM test_data
WHERE id BETWEEN 100 AND 200;

-- BETWEEN: 連続する範囲（1〜10の整数）
SELECT * FROM products WHERE category_id BETWEEN 1 AND 10;

-- IN: 特定の離散値
SELECT * FROM products WHERE category_id IN (1, 3, 5, 7);

-- 両方を組み合わせることも可能
SELECT * FROM orders
WHERE amount BETWEEN 1000 AND 5000
  AND status IN ('shipped', 'delivered');

SELECT
    CASE
        WHEN age BETWEEN 0  AND 19 THEN '未成年'
        WHEN age BETWEEN 20 AND 29 THEN '20代'
        WHEN age BETWEEN 30 AND 39 THEN '30代'
        WHEN age BETWEEN 40 AND 49 THEN '40代'
        ELSE '50代以上'
    END AS age_group,
    COUNT(*) AS member_count
FROM users
GROUP BY
    CASE
        WHEN age BETWEEN 0  AND 19 THEN '未成年'
        WHEN age BETWEEN 20 AND 29 THEN '20代'
        WHEN age BETWEEN 30 AND 39 THEN '30代'
        WHEN age BETWEEN 40 AND 49 THEN '40代'
        ELSE '50代以上'
    END
ORDER BY age_group;

-- 営業時間（9:00〜18:00）のアクセスログ
SELECT * FROM access_logs
WHERE CAST(access_time AS TIME) BETWEEN '09:00:00' AND '18:00:00';

-- MySQL: TIME() 関数
SELECT * FROM access_logs
WHERE TIME(access_time) BETWEEN '09:00:00' AND '18:00:00';

-- アプリからの価格帯検索（プレースホルダ利用を想定）
SELECT product_id, product_name, price
FROM products
WHERE price BETWEEN 1000 AND 5000
  AND category = '家電'
ORDER BY price;

-- OK: 列に直接 BETWEEN（インデックスが効く）
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';

-- NG: 列に関数を適用（インデックスが効かない）
SELECT * FROM orders
WHERE YEAR(order_date) BETWEEN 2024 AND 2024
  AND MONTH(order_date) BETWEEN 1 AND 3;
-- → 全行スキャンになる可能性がある

-- OK: 関数を外して範囲指定に書き直す
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';