SQL基本コマンド集

結合・サブクエリ編

5. テーブル結合（JOIN）

INNER JOIN（内部結合）

両方のテーブルに一致するデータのみを取得

-- ユーザーとその注文を取得
SELECT users.name, orders.amount, orders.order_date
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- テーブル別名を使う（推奨）
SELECT u.name, o.amount, o.order_date
FROM users AS u
INNER JOIN orders AS o ON u.id = o.user_id;

-- JOINとだけ書いてもINNER JOINと同じ
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

LEFT JOIN（左外部結合）

左テーブルの全データと、右テーブルの一致するデータを取得（一致しない場合はNULL）

-- 全ユーザーとその注文（注文がないユーザーも含む）
SELECT u.name, o.amount, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 注文していないユーザーを抜き出す
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- LEFT OUTER JOINと書いても同じ
SELECT u.name, o.amount
FROM users u
LEFT OUTER JOIN orders o ON u.id = o.user_id;

RIGHT JOIN（右外部結合）

右テーブルの全データと、左テーブルの一致するデータを取得

-- 全注文とそのユーザー情報
SELECT u.name, o.amount, o.order_date
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- 注: LEFT JOINでテーブル順を逆にしても同じ結果
SELECT u.name, o.amount, o.order_date
FROM orders o
LEFT JOIN users u ON o.user_id = u.id;

FULL OUTER JOIN（完全外部結合）

両方のテーブルの全データを取得（PostgreSQL、SQL Serverで対応、MySQLは未対応）

-- PostgreSQLの場合
SELECT u.name, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- MySQLで代替手段（UNIONを使用）
SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.name, o.amount FROM users u RIGHT JOIN orders o ON u.id = o.user_id;

CROSS JOIN（クロス結合）

両方のテーブルの全組み合わせ（直積）

-- 全ユーザーと全商品の組み合わせ
SELECT u.name, p.name AS product_name
FROM users u
CROSS JOIN products p;

-- 注意: 結果行数 = usersの行数 × productsの行数

自己結合（Self Join）

同じテーブルを結合する

-- 例: 社員とその上司を表示する
SELECT 
  e1.name AS employee_name,
  e2.name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

複数テーブルの結合

-- 3つのテーブルを結合
SELECT 
  u.name,
  o.order_date,
  p.name AS product_name,
  p.price
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

6. サブクエリ

WHERE句でのサブクエリ

-- 平均価格以上の商品
SELECT * FROM products
WHERE price >= (SELECT AVG(price) FROM products);

-- 注文があるユーザー
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- 注文がないユーザー
SELECT * FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL);

FROM句でのサブクエリ（派生テーブル）

-- ユーザー別の注文合計を取得し、それを並び替え
SELECT *
FROM (
  SELECT user_id, SUM(amount) AS total_amount
  FROM orders
  GROUP BY user_id
) AS user_totals
WHERE total_amount >= 10000
ORDER BY total_amount DESC;

-- 必ず別名（AS）が必要

SELECT句でのサブクエリ

-- 各ユーザーの注文合計を並べて表示
SELECT 
  u.name,
  u.email,
  (SELECT SUM(amount) FROM orders WHERE user_id = u.id) AS total_amount
FROM users u;

-- 注意: パフォーマンスが悪い場合がJOINを使う方が良い

EXISTS / NOT EXISTS

-- 注文があるユーザー
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 注文がないユーザー
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- INよりEXISTSの方が高速な場合が多い

相関サブクエリ

-- 各カテゴリ内で最高価格の商品
SELECT p1.*
FROM products p1
WHERE p1.price = (
  SELECT MAX(p2.price)
  FROM products p2
  WHERE p2.category = p1.category
);

7. 集合演算

UNION（和集合）

複数のSELECT結果を結合（重複を除く）

-- 2つのテーブルのユーザーを統合
SELECT name, email FROM active_users
UNION
SELECT name, email FROM inactive_users;

-- 注意:
-- - カラム数と型が一致している必要がある
-- - 自動的に重複が除かれる
-- - ORDER BYは最後のクエリの後にのみ記述可能

UNION ALL

重複を含めて結合

-- 重複も含めて全て取得
SELECT name FROM users
UNION ALL
SELECT name FROM customers;

-- UNIONより高速（重複チェックがないため）

INTERSECT（積集合）

両方に共通するデータ（MySQLは未対応、PostgreSQL、SQL Serverで対応）

-- PostgreSQLの場合
SELECT email FROM users
INTERSECT
SELECT email FROM customers;

-- MySQLでの代替手段
SELECT DISTINCT u.email
FROM users u
INNER JOIN customers c ON u.email = c.email;

EXCEPT / MINUS（差集合）

最初のクエリにはあるが、2番目にはないデータ（MySQLは未対応）

-- PostgreSQLの場合（EXCEPT）
SELECT email FROM users
EXCEPT
SELECT email FROM customers;

-- Oracleの場合（MINUS）
SELECT email FROM users
MINUS
SELECT email FROM customers;

-- MySQLでの代替手段
SELECT u.email
FROM users u
LEFT JOIN customers c ON u.email = c.email
WHERE c.email IS NULL;