【SQL】文字列結合完全ガイド｜CONCAT・||演算子・CONCAT_WS・GROUP_CONCAT・STRING_AGG・NULL対策まで

-- 2つの列を結合（全RDBMSで共通）
SELECT CONCAT(last_name, first_name) AS full_name
FROM employees;
-- 結果: '山田太郎'

-- リテラル文字列の結合
SELECT CONCAT('Hello', ' World');
-- 結果: 'Hello World'

-- 3つ以上の文字列を1回のCONCATで結合
SELECT CONCAT(last_name, ' ', first_name, ' (', department, ')') AS display_name
FROM employees;
-- 結果: '山田 太郎 (営業部)'

-- 列とリテラルを組み合わせてメール本文を組み立てる
SELECT CONCAT('ID:', employee_id, ' / 氏名:', last_name, first_name)
FROM employees;

-- CONCATネスト（冗長だが動く）
SELECT CONCAT(CONCAT(last_name, ' '), first_name) AS full_name
FROM employees;

-- || 演算子（推奨：こちらが簡潔）
SELECT last_name || ' ' || first_name AS full_name
FROM employees;

-- CONCATネスト
SELECT CONCAT(CONCAT(last_name, ' '), first_name) AS full_name
FROM employees;

-- + 演算子（SQL Server 独自）
SELECT last_name + ' ' + first_name AS full_name
FROM employees;

-- CONCATネスト
SELECT CONCAT(CONCAT(last_name, ' '), first_name) AS full_name
FROM employees;

-- || 演算子（Oracle では最も一般的な書き方）
SELECT last_name || ' ' || first_name AS full_name
FROM employees;

-- PostgreSQL / Oracle
-- 列 + リテラル + 列
SELECT '商品名: ' || product_name || ' (' || price || '円)' AS label
FROM products;
-- 結果: '商品名: りんご (150円)'

-- SQL Server
-- 数値は明示的にキャストが必要
SELECT '商品名: ' + product_name + ' (' + CAST(price AS VARCHAR) + '円)' AS label
FROM products;

-- 例: middle_name が NULL の場合
-- MySQL: CONCAT は全体が NULL になる
SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name)
FROM employees;
-- middle_name が NULL → 結果: NULL（全体が消える！）

-- 対策1: COALESCE で NULL を空文字に置換
SELECT CONCAT(first_name, ' ', COALESCE(middle_name, ''), ' ', last_name)
FROM employees;
-- middle_name が NULL → 結果: '太郎  山田'（スペースが2つ入る）

-- 対策2: CONCAT_WS なら NULL を自動スキップ（MySQL推奨）
SELECT CONCAT_WS(' ', first_name, middle_name, last_name)
FROM employees;
-- middle_name が NULL → 結果: '太郎 山田'（区切り文字が1つに）

-- || 演算子は NULL が混ざると全体が NULL
SELECT first_name || ' ' || middle_name || ' ' || last_name
FROM employees;
-- middle_name が NULL → 結果: NULL

-- 対策: COALESCE で NULL を空文字に
SELECT first_name || ' ' || COALESCE(middle_name || ' ', '') || last_name
FROM employees;
-- middle_name が NULL → 結果: '太郎 山田'

-- + 演算子は NULL が混ざると全体が NULL
SELECT first_name + ' ' + middle_name + ' ' + last_name
FROM employees;
-- middle_name が NULL → 結果: NULL

-- 対策1: COALESCE
SELECT first_name + ' ' + COALESCE(middle_name + ' ', '') + last_name
FROM employees;

-- 対策2: CONCAT 関数を使う（NULL は空文字扱い）
SELECT CONCAT(first_name, ' ', middle_name, ' ', last_name)
FROM employees;
-- middle_name が NULL → 結果: '太郎  山田'（SQL Server のCONCATはNULLを空文字にする）

-- MySQL / PostgreSQL / SQL Server（2017+）
-- 第1引数 = 区切り文字、第2引数以降 = 結合対象
SELECT CONCAT_WS(', ', last_name, first_name, department) AS info
FROM employees;
-- 結果: '山田, 太郎, 営業部'

-- NULLは自動でスキップされる
SELECT CONCAT_WS(' - ', 'A', NULL, 'B', NULL, 'C');
-- 結果: 'A - B - C'（NULLの部分は区切り文字ごと消える）

-- 住所を1行にまとめる（NULLの項目を自動スキップ）
SELECT CONCAT_WS(' ',
    prefecture,    -- 都道府県
    city,          -- 市区町村
    street,        -- 番地
    building       -- 建物名（NULL可）
) AS full_address
FROM addresses;
-- building が NULL → '東京都 千代田区 丸の内1-1'（建物名はスキップ）

-- CSVの1行を生成
SELECT CONCAT_WS(',', employee_id, last_name, first_name, department)
FROM employees;
-- 結果: '101,山田,太郎,営業部'

-- Oracle: || と NVL2 で区切り文字付き結合を模倣
SELECT
    last_name
    || NVL2(first_name, ' ' || first_name, '')
    || NVL2(department, ' (' || department || ')', '')
    AS display_name
FROM employees;
-- first_name が NULL → '山田'
-- first_name が '太郎' → '山田 太郎 (営業部)'

-- MySQL: CONCAT は暗黙変換
SELECT CONCAT('価格: ', price, '円') FROM products;
-- 結果: '価格: 1500円'

-- PostgreSQL: || は暗黙変換しないためキャストが必要
SELECT '価格: ' || price::TEXT || '円' FROM products;     -- ::TEXT でキャスト
SELECT '価格: ' || CAST(price AS TEXT) || '円' FROM products;  -- 標準SQL

-- SQL Server: + は暗黙変換しないためキャストが必要
SELECT '価格: ' + CAST(price AS VARCHAR) + '円' FROM products;
SELECT '価格: ' + FORMAT(price, '#,##0') + '円' FROM products;  -- 桁区切り付き

-- Oracle: || は暗黙変換するがTO_CHARが安全
SELECT '価格: ' || TO_CHAR(price, 'FM999,999') || '円' FROM products;

-- MySQL
SELECT CONCAT('注文日: ', order_date) FROM orders;
SELECT CONCAT('注文日: ', DATE_FORMAT(order_date, '%Y年%m月%d日')) FROM orders;

-- PostgreSQL
SELECT '注文日: ' || TO_CHAR(order_date, 'YYYY年MM月DD日') FROM orders;

-- SQL Server
SELECT '注文日: ' + FORMAT(order_date, 'yyyy年MM月dd日') FROM orders;
SELECT '注文日: ' + CONVERT(VARCHAR, order_date, 111) FROM orders;  -- 2024/03/15 形式

-- Oracle
SELECT '注文日: ' || TO_CHAR(order_date, 'YYYY"年"MM"月"DD"日"') FROM orders;

-- 部署ごとにメンバー名をカンマ区切りでまとめる
SELECT
    department,
    GROUP_CONCAT(last_name ORDER BY last_name SEPARATOR ', ') AS members
FROM employees
GROUP BY department;
-- 結果: '営業部' | '伊藤, 山田, 鈴木'

-- 重複排除
SELECT
    department,
    GROUP_CONCAT(DISTINCT skill SEPARATOR ', ') AS skills
FROM employee_skills
GROUP BY department;

-- 部署ごとにメンバー名をカンマ区切りでまとめる
SELECT
    department,
    STRING_AGG(last_name, ', ' ORDER BY last_name) AS members
FROM employees
GROUP BY department;
-- 結果: '営業部' | '伊藤, 山田, 鈴木'

-- 重複排除
SELECT
    department,
    STRING_AGG(DISTINCT skill, ', ' ORDER BY skill) AS skills
FROM employee_skills
GROUP BY department;

-- 部署ごとにメンバー名をカンマ区切りでまとめる
SELECT
    department,
    STRING_AGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS members
FROM employees
GROUP BY department;
-- 結果: '営業部' | '伊藤, 山田, 鈴木'

-- 部署ごとにメンバー名をカンマ区切りでまとめる
SELECT
    department,
    LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS members
FROM employees
GROUP BY department;
-- 結果: '営業部' | '伊藤, 山田, 鈴木'

-- DISTINCT（Oracle 19c+）
SELECT
    department,
    LISTAGG(DISTINCT skill, ', ') WITHIN GROUP (ORDER BY skill) AS skills
FROM employee_skills
GROUP BY department;

-- ON OVERFLOW TRUNCATE で4000バイトを超えたら末尾を '...' にする
SELECT
    department,
    LISTAGG(skill, ', ' ON OVERFLOW TRUNCATE '...')
        WITHIN GROUP (ORDER BY skill) AS skills
FROM employee_skills
GROUP BY department;

-- MySQL
SELECT CONCAT_WS(' ', last_name, first_name) AS full_name FROM employees;

-- PostgreSQL
SELECT last_name || ' ' || first_name AS full_name FROM employees;

-- SQL Server
SELECT CONCAT(last_name, ' ', first_name) AS full_name FROM employees;

-- Oracle
SELECT last_name || ' ' || first_name AS full_name FROM employees;

-- MySQL / PostgreSQL
SELECT CONCAT_WS(' ', prefecture, city, street, building) AS address
FROM addresses;
-- building が NULL → '東京都 千代田区 丸の内1-1'

-- Oracle（CONCAT_WS がないため手動対応）
SELECT prefecture || ' ' || city || ' ' || street
    || NVL2(building, ' ' || building, '')
    AS address
FROM addresses;

-- MySQL
SELECT CONCAT(product_name, ' (¥', FORMAT(price, 0), ')') AS label
FROM products;
-- 結果: 'りんご (¥150)'

-- PostgreSQL
SELECT product_name || ' (¥' || TO_CHAR(price, 'FM999,999') || ')' AS label
FROM products;

-- SQL Server
SELECT product_name + ' (¥' + FORMAT(price, '#,##0') + ')' AS label
FROM products;

-- ステータスによって表示文言を変えてユーザー名に付与
SELECT
    user_name || ' ' ||
    CASE status
        WHEN 'admin'  THEN '[管理者]'
        WHEN 'editor' THEN '[編集者]'
        ELSE ''
    END AS display_name
FROM users;
-- 結果: '山田太郎 [管理者]'

-- MySQL: GROUP_CONCAT で記事ごとのタグ一覧
SELECT
    a.title,
    GROUP_CONCAT(t.tag_name ORDER BY t.tag_name SEPARATOR ', ') AS tags
FROM articles a
JOIN article_tags at ON a.id = at.article_id
JOIN tags t          ON at.tag_id = t.id
GROUP BY a.id, a.title;
-- 結果: 'SQL入門' | 'SQL, データベース, 初心者向け'

-- PostgreSQL: STRING_AGG で同様のことを行う
SELECT
    a.title,
    STRING_AGG(t.tag_name, ', ' ORDER BY t.tag_name) AS tags
FROM articles a
JOIN article_tags at ON a.id = at.article_id
JOIN tags t          ON at.tag_id = t.id
GROUP BY a.id, a.title;

-- NG: インデックスが効かない
SELECT * FROM employees
WHERE CONCAT(last_name, first_name) = '山田太郎';

-- OK: 個別列で条件を書く
SELECT * FROM employees
WHERE last_name = '山田' AND first_name = '太郎';