【SQL】IS NOT NULLで非NULLデータを抽出する完全ガイド｜三値論理・複合条件・JOIN・GROUP BY・パフォーマンスまで解説

-- employees テーブル（社員）
-- id | name   | email                    | phone         | dept_id | salary | resigned_at
--  1 | 田中   | tanaka@example.com       | 03-1111-2222  |       1 |  30000 | NULL
--  2 | 鈴木   | suzuki@example.com       | NULL          |       1 |  45000 | NULL
--  3 | 高橋   | NULL                     | 090-3333-4444 |       2 |  38000 | NULL
--  4 | 田中(退)| NULL                    | NULL          |       2 |  32000 | 2023-12-31
--  5 | 伊藤   | ito@example.com          | 03-5555-6666  |    NULL |  50000 | NULL
--  6 | 渡辺   | watanabe@example.com     | 080-7777-8888 |       3 |  42000 | NULL

-- orders テーブル（注文）
-- id | customer_id | amount | status   | shipped_at
--  1 |           1 |  12000 | shipped  | 2024-01-17
--  2 |           2 |   3500 | pending  | NULL
--  3 |           3 |  85000 | shipped  | 2024-02-22
--  4 |           1 |   2800 | canceled | NULL

-- 基本構文
SELECT * FROM table_name WHERE column_name IS NOT NULL;

-- メールアドレスが登録されている社員を取得
SELECT id, name, email
FROM employees
WHERE email IS NOT NULL;
-- → id: 1(田中), 2(鈴木), 5(伊藤), 6(渡辺) が返る（id3,4は email が NULL）

-- 発送済みの注文（shipped_at が NULL でないもの）
SELECT id, customer_id, amount, shipped_at
FROM orders
WHERE shipped_at IS NOT NULL;
-- → id: 1, 3 が返る

-- NG: 常に 0 件になる（UNKNOWN になるため）
SELECT * FROM employees WHERE email <> NULL;  -- 0件
SELECT * FROM employees WHERE email != NULL;  -- 0件

-- NULL との算術演算も UNKNOWN
SELECT 100 + NULL;      -- NULL
SELECT 'abc' || NULL;  -- NULL（PostgreSQL）

-- NULL との論理演算
-- TRUE AND NULL  → NULL（UNKNOWN）
-- FALSE AND NULL → FALSE
-- TRUE OR NULL   → TRUE
-- FALSE OR NULL  → NULL（UNKNOWN）

-- 正しい書き方
SELECT * FROM employees WHERE email IS NOT NULL;  -- 4件返る

-- ANSI 標準の IS DISTINCT FROM（PostgreSQL）
-- NULL を含む列でも安全に != を表現できる
SELECT * FROM employees WHERE email IS DISTINCT FROM NULL;
-- → IS NOT NULL と同義（ただし他の値との比較もできる）

-- ① email と phone の両方が入力済み（AND）
SELECT id, name, email, phone
FROM employees
WHERE email IS NOT NULL
  AND phone IS NOT NULL;
-- → id: 1(田中), 6(渡辺)

-- ② email か phone のどちらか一方でも入力済み（OR）
SELECT id, name, email, phone
FROM employees
WHERE email IS NOT NULL
   OR phone IS NOT NULL;
-- → id: 1,2,3,5,6（id4のみ両方NULL）

-- ③ いずれかの連絡先がない（一方でも NULL）
SELECT id, name
FROM employees
WHERE email IS NULL OR phone IS NULL;
-- → id: 2,3,4,5

-- ④ 退職していない（resigned_at が NULL）かつメールあり
SELECT id, name, email
FROM employees
WHERE resigned_at IS NULL
  AND email IS NOT NULL;
-- → id: 1,2,5,6 の中でさらに email がある id: 1,2,5,6
--（id5は dept_id が NULL だが email はある）

-- ⑤ 部署未割当または連絡先なし
SELECT id, name
FROM employees
WHERE dept_id IS NULL
   OR (email IS NULL AND phone IS NULL);

-- NOT NULL 制約（スキーマ定義）
CREATE TABLE users (
    id    INT PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,  -- NULL を格納できない
    email VARCHAR(200)            -- NULL を格納できる（任意項目）
);

-- IS NOT NULL 制約がない列でも検索できる
-- email は NULL 許容だが IS NOT NULL で絞り込める
SELECT id, name, email
FROM users
WHERE email IS NOT NULL;

-- NOT NULL 制約があっても IS NOT NULL は使える（常に TRUE になるが無害）
SELECT id, name
FROM users
WHERE name IS NOT NULL;  -- name は NOT NULL 制約があるので全件一致

-- 既存列に NOT NULL 制約を追加する（ALTER TABLE）
ALTER TABLE employees MODIFY COLUMN dept_id INT NOT NULL;
-- → dept_id に NULL が入っている行がある場合はエラーになる

-- departments テーブル
-- dept_id | dept_name
--       1 | 営業部
--       2 | 技術部
--       3 | 総務部

-- ① INNER JOIN: 部署が設定されている社員のみ取得
-- employees.dept_id が NULL の id=5 は JOIN で除外される
SELECT e.id, e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- → id: 1,2,3,4,6（id5は dept_id が NULL なので結果に出ない）

-- ② LEFT JOIN: すべての社員 + 部署情報（部署なしは NULL）
SELECT e.id, e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
-- → id: 1〜6（id5の dept_name は NULL）

-- ③ LEFT JOIN + IS NULL: 部署未割当の社員を抽出
SELECT e.id, e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;
-- → id: 5（部署に JOIN できなかった行）
-- ※ e.dept_id IS NULL の代わりに JOIN 側の列で確認する

-- ④ LEFT JOIN + IS NOT NULL: 部署が割り当てられている社員のみ
SELECT e.id, e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NOT NULL;
-- → INNER JOIN と同じ結果（id: 1,2,3,4,6）

-- COUNT(*) は NULL も含む全行をカウント
-- COUNT(column) は NULL を除外してカウント
SELECT
    COUNT(*) AS total_rows,
    COUNT(email) AS has_email,        -- email が NULL でない行数
    COUNT(phone) AS has_phone,        -- phone が NULL でない行数
    COUNT(dept_id) AS assigned_dept   -- 部署割当済みの行数
FROM employees;
-- total_rows: 6, has_email: 4, has_phone: 4, assigned_dept: 5

-- 部署ごとのアクティブ社員数（退職者を除く）
SELECT
    COALESCE(d.dept_name, '未割当') AS dept,
    COUNT(*) AS total,
    COUNT(e.email) AS has_email,
    SUM(CASE WHEN e.resigned_at IS NULL THEN 1 ELSE 0 END) AS active
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
GROUP BY e.dept_id, d.dept_name
ORDER BY dept;

-- メール登録率を計算
SELECT
    COUNT(email) * 100.0 / COUNT(*) AS email_registration_rate
FROM employees
WHERE resigned_at IS NULL;  -- 退職者を除く

-- 少なくとも 1 件以上の連絡先がある部署のみ
SELECT
    dept_id,
    COUNT(*) AS member_count,
    COUNT(email) AS email_count,
    COUNT(phone) AS phone_count
FROM employees
WHERE resigned_at IS NULL
GROUP BY dept_id
HAVING COUNT(email) > 0 OR COUNT(phone) > 0;

-- 全員にメールアドレスが登録されている部署
HAVING COUNT(*) = COUNT(email)
-- COUNT(*) = COUNT(email) → NULL が 1 件もない部署

-- 連絡先の登録状況をラベルで表示
SELECT
    id,
    name,
    CASE
        WHEN email IS NOT NULL AND phone IS NOT NULL THEN '両方あり'
        WHEN email IS NOT NULL AND phone IS NULL     THEN 'メールのみ'
        WHEN email IS NULL     AND phone IS NOT NULL THEN '電話のみ'
        ELSE '未登録'
    END AS contact_status
FROM employees
WHERE resigned_at IS NULL;

-- NULL か否かをフラグ（0/1）で表現
SELECT
    id,
    name,
    CASE WHEN email   IS NOT NULL THEN 1 ELSE 0 END AS has_email,
    CASE WHEN phone   IS NOT NULL THEN 1 ELSE 0 END AS has_phone,
    CASE WHEN dept_id IS NOT NULL THEN 1 ELSE 0 END AS is_assigned
FROM employees;

-- COALESCE で NULL を代替値に置換
-- email が NULL なら '(未登録)' と表示
SELECT
    id,
    name,
    COALESCE(email, '(未登録)') AS email_display,
    COALESCE(CAST(phone AS CHAR), '(未登録)') AS phone_display
FROM employees;

-- ① WHERE で IS NOT NULL（集計前に NULL 行を除外）
-- email がある社員だけを集計対象にする
SELECT dept_id, COUNT(*) AS cnt
FROM employees
WHERE email IS NOT NULL          -- 集計前に email=NULL の行を除外
  AND resigned_at IS NULL
GROUP BY dept_id;

-- ② HAVING で IS NOT NULL（集計後にフィルター）
-- 部署ごとに集計してから、dept_id が NULL でない部署のみ表示
SELECT dept_id, COUNT(*) AS cnt
FROM employees
WHERE resigned_at IS NULL
GROUP BY dept_id
HAVING dept_id IS NOT NULL;      -- 集計後に dept_id=NULL のグループを除外

-- ③ 集計関数の結果に IS NOT NULL を使う（HAVING のみで可）
SELECT dept_id, MAX(salary) AS max_sal
FROM employees
GROUP BY dept_id
HAVING MAX(salary) IS NOT NULL;  -- MAX が NULL のグループを除外
-- ※ WHERE MAX(salary) IS NOT NULL は構文エラー

-- ① サブクエリ内で IS NOT NULL（最新の有効注文を持つ顧客）
SELECT id, name
FROM employees
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE shipped_at IS NOT NULL  -- 発送済みの注文がある顧客
);

-- ② EXISTS で「少なくとも 1 件の発送済み注文がある社員」
SELECT e.id, e.name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = e.id
      AND o.shipped_at IS NOT NULL
);

-- ③ NOT EXISTS で「発送済み注文が 1 件もない顧客」
SELECT e.id, e.name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = e.id
      AND o.shipped_at IS NOT NULL
);

-- ④ 相関サブクエリで最新の注文が発送済みの顧客
SELECT id, name
FROM employees e
WHERE (
    SELECT shipped_at
    FROM orders
    WHERE customer_id = e.id
    ORDER BY id DESC
    LIMIT 1
) IS NOT NULL;

-- インデックスを作成
CREATE INDEX idx_employees_email ON employees (email);

-- EXPLAIN でアクセスタイプを確認
EXPLAIN SELECT * FROM employees WHERE email IS NOT NULL;
-- NULL が少ない（大半が NOT NULL）場合: type=ALL（フルスキャン）になりやすい
-- NULL が多い場合: type=range（インデックス活用）になりやすい
-- → 対象行が多すぎるとオプティマイザがフルスキャンを選択する

-- IS NULL は逆に NULL が少ない場合に INDEX を使いやすい
EXPLAIN SELECT * FROM employees WHERE email IS NULL;

-- ===== PostgreSQL: Partial Index =====
-- email が NULL でない行だけのインデックス
CREATE INDEX idx_employees_email_notnull
ON employees (email)
WHERE email IS NOT NULL;

-- このインデックスは IS NOT NULL クエリで使われる
SELECT * FROM employees WHERE email IS NOT NULL;

-- ===== MySQL: Generated Column + 通常 INDEX（MySQL 8.0 以降）=====
ALTER TABLE employees
ADD COLUMN email_flag TINYINT AS (IF(email IS NOT NULL, 1, NULL)) VIRTUAL;

CREATE INDEX idx_employees_email_flag ON employees (email_flag);

-- email_flag IS NOT NULL → email IS NOT NULL と等価
SELECT * FROM employees WHERE email_flag IS NOT NULL;

-- ===== 複合インデックス: NULL 列を後ろに置く =====
-- (resigned_at, email) より (resigned_at) + (email) の別インデックスが効果的な場合も
CREATE INDEX idx_emp_active_email ON employees (resigned_at, email);
SELECT * FROM employees
WHERE resigned_at IS NULL AND email IS NOT NULL;

-- COALESCE: 最初の非 NULL 値を返す（全 RDBMS 共通）
SELECT
    id,
    name,
    COALESCE(email, phone, '連絡先なし') AS contact
FROM employees;
-- email が NULL → phone を使う
-- phone も NULL → '連絡先なし' を使う

-- IFNULL（MySQL 専用・2引数のみ）
SELECT IFNULL(email, '(未登録)') FROM employees;

-- NVL（Oracle 専用）
SELECT NVL(email, '(未登録)') FROM employees;

-- NULLIF: 2つの値が等しければ NULL を返す（0除算対策に便利）
SELECT
    total_sales / NULLIF(orders_count, 0) AS avg_per_order
FROM monthly_summary;
-- orders_count が 0 の場合 NULLIF が NULL を返し → 除算が NULL になる（ZeroDivisionError を防ぐ）

-- COALESCE で IS NOT NULL フィルターを省略するパターン
SELECT id, name, COALESCE(email, phone) AS contact
FROM employees
WHERE COALESCE(email, phone) IS NOT NULL;
-- email か phone のどちらかがある行のみ取得

-- resigned_at / deleted_at が NULL = 現役ユーザー
SELECT id, name, email
FROM employees
WHERE resigned_at IS NULL
ORDER BY name;

-- アクティブかつ連絡可能（メールまたは電話がある）
SELECT id, name,
       COALESCE(email, phone) AS contact
FROM employees
WHERE resigned_at IS NULL
  AND (email IS NOT NULL OR phone IS NOT NULL);

-- 未入力（NULL）の件数を列ごとに一覧表示
SELECT
    SUM(CASE WHEN email   IS NULL THEN 1 ELSE 0 END) AS missing_email,
    SUM(CASE WHEN phone   IS NULL THEN 1 ELSE 0 END) AS missing_phone,
    SUM(CASE WHEN dept_id IS NULL THEN 1 ELSE 0 END) AS missing_dept,
    COUNT(*) AS total
FROM employees
WHERE resigned_at IS NULL;

-- 未入力率（%）
SELECT
    ROUND(SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1)
        AS email_missing_pct,
    ROUND(SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1)
        AS phone_missing_pct
FROM employees
WHERE resigned_at IS NULL;

-- memberships テーブル（expired_at が NULL = 無期限会員）

-- 現時点で有効な会員（有効期限が未来 OR 無期限）
SELECT id, name, expired_at
FROM memberships
WHERE expired_at IS NULL             -- 無期限
   OR expired_at >= CURDATE();        -- まだ期限内

-- 期限付きのみ（NULL を除外して有効期限でソート）
SELECT id, name, expired_at
FROM memberships
WHERE expired_at IS NOT NULL
ORDER BY expired_at ASC;

-- 30日以内に期限が切れる会員
SELECT id, name, expired_at,
       DATEDIFF(expired_at, CURDATE()) AS days_left
FROM memberships
WHERE expired_at IS NOT NULL
  AND expired_at BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY)
ORDER BY expired_at;

-- テーブル全体の NULL 状況をダッシュボード的に確認
SELECT
    'employees' AS table_name,
    COUNT(*) AS total_rows,
    SUM(CASE WHEN name     IS NOT NULL THEN 1 ELSE 0 END) AS name_not_null,
    SUM(CASE WHEN email    IS NOT NULL THEN 1 ELSE 0 END) AS email_not_null,
    SUM(CASE WHEN phone    IS NOT NULL THEN 1 ELSE 0 END) AS phone_not_null,
    SUM(CASE WHEN dept_id  IS NOT NULL THEN 1 ELSE 0 END) AS dept_not_null,
    SUM(CASE WHEN salary   IS NOT NULL THEN 1 ELSE 0 END) AS salary_not_null
FROM employees;