SELECT とクエリの基礎
SELECT、WHERE、ORDER BY
SELECT は1つ以上のテーブルから行を取得します。パフォーマンスと明確さのため * ではなく列を明示的に指定してください(スキーマ変更がアプリを壊しません)。WHERE はグループ化の前に行をフィルタします。ORDER BY は結果をソートします(ASC がデフォルト、DESC が降順)。LIMIT/OFFSET はページネーションを実装します — 大規模データセットにはキーセットページネーション(WHERE id > last_id)を優先します。
-- basic query: select specific columns
SELECT id, name, email
FROM users
WHERE age >= 18 AND status = 'active'
ORDER BY name ASC, created_at DESC
LIMIT 10 OFFSET 0;
-- select all columns (avoid in production)
SELECT * FROM products;
-- column aliases with AS
SELECT name AS product_name, price * 1.1 AS price_with_tax
FROM products;DISTINCT とエイリアス
DISTINCT は結果セットから重複行を削除します。個々の列ではなく行全体に作用します — SELECT DISTINCT city, country は一意の city+country ペアを返します。テーブルエイリアス(u、o)はクエリを短縮し、テーブルを自己結合する場合に必要です。列エイリアスは可読性のために出力列をリネームします。
-- unique values only
SELECT DISTINCT country FROM users;
SELECT DISTINCT city, country FROM users; -- unique combos
-- table aliases (essential for joins)
SELECT u.name, o.total
FROM users AS u
JOIN orders AS o ON u.id = o.user_id;
-- column alias (AS is optional)
SELECT name product_name, COUNT(*) count
FROM products
GROUP BY name;フィルタリング:BETWEEN、IN、IS NULL
BETWEEN は両端を含みます。IN はリストまたはサブクエリの任意の値にマッチします。NULL には IS NULL / IS NOT NULL が必要です(= NULL は使用不可)。NOT IN とサブクエリに注意してください — サブクエリが NULL を返す場合、NOT IN は行を全く返しません。NOT EXISTS を使用してください。NULL を正しく処理し、多くの場合より高速です。
SELECT * FROM products
WHERE price BETWEEN 10 AND 100 -- inclusive range
AND category IN ('tech', 'home') -- match any value
AND stock IS NOT NULL -- exclude NULLs
AND discount IS NULL; -- only NULLs
-- NOT IN with NULL caveat: returns nothing if subquery has NULL!
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders); -- risky if NULLs
-- safer: NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);LIKE とパターンマッチング
LIKE は %(ゼロ以上の文字)と _(正確に1文字)をワイルドカードとして使用します。LIKE は MySQL 以外のほとんどのデータベースで大文字小文字を区別します(MySQL はデフォルトで大文字小文字を区別しません)。PostgreSQL では大文字小文字を区別しないマッチングに ILIKE を使用します。複雑なパターンには正規表現を使用します(PostgreSQL では ~、MySQL では REGEXP)。先頭の % 付き LIKE はインデックスを使用できません — パフォーマンスのためにフルテキスト検索を検討してください。
-- LIKE: basic pattern matching
SELECT * FROM users WHERE name LIKE 'A%'; -- starts with A
SELECT * FROM users WHERE name LIKE '%son'; -- ends with son
SELECT * FROM users WHERE name LIKE '%a%'; -- contains a
SELECT * FROM users WHERE name LIKE '_a%'; -- second char is a
-- ILIKE (PostgreSQL): case-insensitive
SELECT * FROM users WHERE name ILIKE 'a%';
-- SIMILAR TO (PostgreSQL): regex-like
SELECT * FROM users WHERE name SIMILAR TO '[AB]%';
-- full regex (PostgreSQL)
SELECT * FROM users WHERE name ~ '^A[a-z]+$';CASE 式
CASE は SQL の if-then-else で、行ごとに評価されます。SELECT、WHERE、ORDER BY、HAVING に現れます。'ピボット'パターン(CASE の SUM)は行を列に変換します — レポートに便利です。CASE は WHEN がマッチせず ELSE がない場合 NULL を返します。予測可能な結果のために常に ELSE を含めてください。
-- conditional logic in queries
SELECT
name,
price,
CASE
WHEN price < 10 THEN 'cheap'
WHEN price < 50 THEN 'moderate'
WHEN price < 100 THEN 'expensive'
ELSE 'luxury'
END AS price_category
FROM products;
-- CASE in aggregation (pivot table)
SELECT
category,
COUNT(*) AS total,
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_count
FROM products
GROUP BY category;JOIN
INNER JOIN
INNER JOIN は両方のテーブルでマッチする行のみを返します。JOIN は INNER JOIN の短縮形です。マルチテーブルクエリでは、テーブルを段階的に結合します。ON は結合条件を指定し、USING(column) は両方のテーブルが同じ列名を持つ場合の短縮形です。内部結合は両側から非マッチング行を除外します。
-- only matching rows from both tables
SELECT u.name, o.total, o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 100
ORDER BY o.total DESC;
-- multiple joins
SELECT u.name, o.total, p.product_name
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;
-- USING (when column names match)
SELECT * FROM users
JOIN profiles ON users.id = profiles.user_id;LEFT JOIN(LEFT OUTER JOIN)
LEFT JOIN は左テーブルのすべての行を返し、非マッチング右行には NULL を入れます。これは「すべて含める」クエリに不可欠です。アンチジョインパターン(WHERE right.id IS NULL)は右にマッチがない左テーブルの行を見つけます — 「注文していないユーザー」に便利です。COUNT(right.id) は非 NULL 値をカウントするため、注文のないユーザーには 0 を返します。
-- all users, with their orders (NULL if no orders)
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
ORDER BY u.name;
-- find users with NO orders (anti-join pattern)
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
-- count orders per user (including zero)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;RIGHT と FULL OUTER JOIN
RIGHT JOIN はすべての右テーブル行を返します。テーブルを入れ替えて LEFT JOIN を使用するのと同等です(より読みやすい)。FULL OUTER JOIN は両方のテーブルのすべての行を返し、マッチがない場合は NULL を入れます — データ照合に便利です。MySQL は FULL OUTER JOIN を直接サポートしません。LEFT JOIN UNION RIGHT JOIN でエミュレートします。
-- RIGHT JOIN: all rows from right table
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- returns all orders, even orphaned ones (user_id = NULL)
-- FULL OUTER JOIN: all rows from both tables
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- returns all users AND all orders, matching where possible
-- Note: RIGHT JOIN is rarely used (just swap tables and use LEFT)
-- FULL OUTER JOIN is useful for finding mismatches between tables
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id
WHERE u.id IS NULL OR o.user_id IS NULL;CROSS JOIN と自己結合
CROSS JOIN はデカルト積を 生成します — A のすべての行と B のすべての行をペアにします。組み合わせの生成(サイズ × 色)に使用します。自己結合(テーブルを自身に結合)は階層データ(従業員-管理者)、重複の発見、同じテーブル内の行の比較によく使用されます。自己結合では常にテーブルエイリアスを使用して2つの「コピー」を区別します。
-- CROSS JOIN: Cartesian product (every combination)
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;
-- produces all size+color combinations
-- Self join: join a table to itself
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- self join for hierarchical data
SELECT c.name AS child, p.name AS parent
FROM categories c
JOIN categories p ON c.parent_id = p.id;
-- self join to find duplicates
SELECT a.id, a.email, b.id AS dup_id
FROM users a
JOIN users b ON a.email = b.email AND a.id < b.id;NATURAL JOIN と JOIN タイプのまとめ
NATURAL JOIN は同じ名前の列で自動的に結合します — 便利ですが、スキーマ変更が結合動作を黙って変更する可能性があるため危険です。本番では避けてください。LATERAL 結合はサブクエリが外部クエリの列を参照できるようにします — 「グループごとのトップ N」クエリに強力です。カン マ構文(FROM a, b)は CROSS JOIN と同等です。
-- NATURAL JOIN: joins on all matching column names
-- (rarely recommended — implicit, fragile)
SELECT * FROM users NATURAL JOIN profiles;
-- joins on any column that exists in BOTH tables
-- Summary of join types:
-- INNER JOIN : matching rows only
-- LEFT JOIN : all left + matching right
-- RIGHT JOIN : all right + matching left
-- FULL JOIN : all from both sides
-- CROSS JOIN : Cartesian product
-- SELF JOIN : table joined to itself
-- LATERAL JOIN (PostgreSQL): subquery can reference outer query
SELECT u.name, recent.*
FROM users u,
LATERAL (
SELECT * FROM orders o
WHERE o.user_id = u.id
ORDER BY o.created_at DESC
LIMIT 3
) recent;GROUP BY と集計
GROUP BY と HAVING
GROUP BY は行をグループに折りたたみ、グループごとに1行にします。集計関数(COUNT、SUM、AVG、MIN、MAX)は各グループで動作します。WHERE はグループ化の前に個々の行をフィルタし、HAVING は集計後にグループをフィルタします。SELECT の非集計列は GROUP BY に現れなければなりません(標準 SQL)。MySQL は寛容ですが予測不能です — すべての非集計列を GROUP BY に含めてください。
-- aggregate per group
SELECT category, COUNT(*) AS cnt, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY cnt DESC;
-- HAVING filters groups (after aggregation)
-- WHERE filters rows (before aggregation)
SELECT dept, AVG(salary) AS avg_sal
FROM employees
WHERE status = 'active' -- filter rows first
GROUP BY dept
HAVING AVG(salary) > 50000; -- then filter groups集計関数
COUNT(*) は NULL を含むすべての行をカウントし、COUNT(column) は非 NULL 値のみをカウントします。COUNT(DISTINCT col) は一意の値をカウントします。SUM/AVG は NULL を無視します。AVG = SUM/COUNT(非 NULL)なので、NULL は平均に影響します。STRING_AGG(PostgreSQL)/ GROUP_CONCAT(MySQL)はグループごとに文字列を連結します。BOOL_OR/BOOL_AND はいずれか/すべての値が true の場合 true を返します。
SELECT
COUNT(*) AS total_rows, -- counts all rows
COUNT(email) AS emails_filled, -- counts non-NULL emails
COUNT(DISTINCT country) AS countries,
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order,
MIN(amount) AS smallest_order,
MAX(amount) AS largest_order,
-- string aggregation (PostgreSQL)
STRING_AGG(name, ', ') AS all_names,
-- boolean aggregation
BOOL_OR(is_active) AS any_active,
BOOL_AND(is_active) AS all_active
FROM orders;