副問い合わせ
副問い合わせの基本
ts
// 副問い合わせ (サブクエリ) を学ぶ
//
// [1] 2 回の SELECT が必要な状況
// 先に MAX(出金額) を求め、その値を使ってもう一度 SELECT する。
// [2] 副問い合わせ
// 上の 2 文を 1 文に合体する。WHERE の右辺に SELECT を丸カッコで埋め込む。
// SELECT 費目, 出金額 FROM 家計簿
// WHERE 出金額 = (SELECT MAX(出金額) FROM 家計簿)
// [3] 処理のしくみ
// 内側の SELECT が先に実行され、具体的な値 (7560) に「化ける」。
// その後で外側の SELECT が実行される。
// [4] 副問い合わせの 3 つのパターン (結果の形で分類)
// ・スカラー … 1 行 1 列の単一値 → = や比較演算子で使う
// ・ベクター … n 行 1 列の複数値 → IN などで使う
// ・マトリックス … n 行 m 列の表 → FROM 句に表として使う
type KakeiboRow = { 費目: string; 出金額: number }
async function show(label: string, rows: readonly unknown[]) {
console.log(`\n--- ${label} ---`)
console.table(rows)
}
async function main() {
try {
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
await sql.unsafe(`
CREATE TABLE 家計簿 (
費目 VARCHAR(20),
出金額 INTEGER
);
`)
await sql`INSERT INTO 家計簿 (費目, 出金額) VALUES
('食費', 380),
('給料', 0),
('教養娯楽費', 2800),
('交際費', 5000),
('水道光熱費', 7560)
`
const all = await sql<KakeiboRow[]>`SELECT 費目, 出金額 FROM 家計簿 ORDER BY 出金額`
console.log(`\n=== 家計簿 (${all.length}件) ===`)
console.table(all)
// ============================================================
// [1] 2 回の SELECT が必要な状況
// まず最大の出金額を取得し、その値を覚えておく → 2 本目で使う
// ============================================================
console.log('\n========== [1] 2 回の SELECT で求める ==========')
const [{ 最大 }] = await sql<{ 最大: number }[]>`
SELECT MAX(出金額) AS 最大 FROM 家計簿
`
console.log(`(1) まず最大の出金額を取得 → ${最大} (この値を書き留める)`)
await show(
'(2) 書き留めた額を当てはめて費目と金額を取得',
await sql`
SELECT 費目, 出金額 FROM 家計簿 WHERE 出金額 = ${最大}
`,
)
// ============================================================
// [2] 副問い合わせ: 2 文を 1 文に合体する
// ============================================================
console.log('\n========== [2] 副問い合わせで 1 文にまとめる ==========')
await show(
'WHERE 出金額 = (SELECT MAX(出金額) ...)',
await sql`
SELECT 費目, 出金額
FROM 家計簿
WHERE 出金額 = (SELECT MAX(出金額) FROM 家計簿)
`,
)
// ============================================================
// [3] 処理のしくみ: 内側だけ実行すると 7560 に「化ける」
// ============================================================
console.log('\n========== [3] 内側の副問い合わせは先に値へ化ける ==========')
await show(
'内側 (SELECT MAX(出金額) FROM 家計簿) の結果',
await sql`
SELECT MAX(出金額) AS 内側の結果 FROM 家計簿
`,
)
console.log('→ この 7560 が外側の WHERE 出金額 = 7560 に当てはめられる')
// ============================================================
// [4] 3 つのパターン
// ============================================================
console.log('\n========== [4] 副問い合わせの 3 つのパターン ==========')
// (a) スカラー: 1 行 1 列の単一値 → 比較演算子で使う
// 平均以上の出金をした費目を求める
await show(
'(a) スカラー: 出金額 >= (平均) の費目',
await sql`
SELECT 費目, 出金額
FROM 家計簿
WHERE 出金額 >= (SELECT AVG(出金額) FROM 家計簿)
ORDER BY 出金額
`,
)
// (b) ベクター: n 行 1 列の複数値 → IN で使う
// 出金額が 2800 以上の費目一覧を内側で作り、それに該当する行を取る
await show(
'(b) ベクター: 費目 IN (出金額2800以上の費目たち)',
await sql`
SELECT 費目, 出金額
FROM 家計簿
WHERE 費目 IN (SELECT 費目 FROM 家計簿 WHERE 出金額 >= 2800)
ORDER BY 出金額
`,
)
// (c) マトリックス: n 行 m 列の表 → FROM 句に表として使う
// 内側で「費目・年額(出金額*12)」の表を作り、外側で年額3万超を絞る
await show(
'(c) マトリックス: FROM (副問い合わせの表)',
await sql`
SELECT 費目, 年額
FROM (SELECT 費目, 出金額 * 12 AS 年額 FROM 家計簿) AS 年間集計
WHERE 年額 > 30000
ORDER BY 年額
`,
)
} catch (e) {
console.error('❌ エラーが発生しました:', e)
} finally {
await sql.end()
}
}
main()実行結果
sh
=== 家計簿 (5件) ===
┌─────────┬──────────────┬────────┐
│ (index) │ 費目 │ 出金額 │
├─────────┼──────────────┼────────┤
│ 0 │ '給料' │ 0 │
│ 1 │ '食費' │ 380 │
│ 2 │ '教養娯楽費' │ 2800 │
│ 3 │ '交際費' │ 5000 │
│ 4 │ '水道光熱費' │ 7560 │
└─────────┴──────────────┴────────┘
========== [1] 2 回の SELECT で求める ==========
(1) まず最大の出金額を取得 → 7560 (この値を書き留める)
--- (2) 書き留めた額を当てはめて費目と金額を取得 ---
┌─────────┬──────────────┬────────┐
│ (index) │ 費目 │ 出金額 │
├─────────┼──────────────┼────────┤
│ 0 │ '水道光熱費' │ 7560 │
└─────────┴──────────────┴────────┘
========== [2] 副問い合わせで 1 文にまとめる ==========
--- WHERE 出金額 = (SELECT MAX(出金額) ...) ---
┌─────────┬──────────────┬────────┐
│ (index) │ 費目 │ 出金額 │
├─────────┼──────────────┼────────┤
│ 0 │ '水道光熱費' │ 7560 │
└─────────┴──────────────┴────────┘
========== [3] 内側の副問い合わせは先に値へ化ける ==========
--- 内側 (SELECT MAX(出金額) FROM 家計簿) の結果 ---
┌─────────┬────────────┐
│ (index) │ 内側の結果 │
├─────────┼────────────┤
│ 0 │ 7560 │
└─────────┴────────────┘
→ この 7560 が外側の WHERE 出金額 = 7560 に当てはめられる
========== [4] 副問い合わせの 3 つのパターン ==========
--- (a) スカラー: 出金額 >= (平均) の費目 ---
┌─────────┬──────────────┬────────┐
│ (index) │ 費目 │ 出金額 │
├─────────┼──────────────┼────────┤
│ 0 │ '交際費' │ 5000 │
│ 1 │ '水道光熱費' │ 7560 │
└─────────┴──────────────┴────────┘
--- (b) ベクター: 費目 IN (出金額2800以上の費目たち) ---
┌─────────┬──────────────┬────────┐
│ (index) │ 費目 │ 出金額 │
├─────────┼──────────────┼────────┤
│ 0 │ '教養娯楽費' │ 2800 │
│ 1 │ '交際費' │ 5000 │
│ 2 │ '水道光熱費' │ 7560 │
└─────────┴──────────────┴────────┘
--- (c) マトリックス: FROM (副問い合わせの表) ---
┌─────────┬──────────────┬───────┐
│ (index) │ 費目 │ 年額 │
├─────────┼──────────────┼───────┤
│ 0 │ '教養娯楽費' │ 33600 │
│ 1 │ '交際費' │ 60000 │
│ 2 │ '水道光熱費' │ 90720 │
└─────────┴──────────────┴───────┘副問い合わせの3つのパターン
副問い合わせ（サブクエリ）の結果は、必ず「表（行×列）」の形で返ります。その表の大きさによって、次の3つの使い方に分けられます。
- 単一の値の代わりとして、副問い合わせの検索結果を用いる。
- 複数の値の代わりとして、副問い合わせの検索結果を用いる。
- 表の値の代わりとして、副問い合わせの検索結果を用いる。
3パターンの対応表
| パターン | 結果の形 | 別名 | 使う場所 | 例 |
|---|---|---|---|---|
| 単一の値 | 1行1列 | スカラー | = など比較演算子 | WHERE 出金額 = (SELECT MAX(出金額) FROM 家計簿) |
| 複数の値 | n行1列 | ベクター | IN (...) | WHERE 費目 IN (SELECT 費目 FROM 家計簿 WHERE 出金額 >= 2800) |
| 表の値 | n行m列 | マトリックス | FROM (...) | FROM (SELECT 費目, 出金額 * 12 AS 年額 FROM 家計簿) AS 年間集計 |
読み解くコツ
- 一番内側のカッコ
( )を探す。 - その中の SELECT を単体で実行したつもりになり、結果（値・リスト・表）を思い浮かべる。
- カッコごと、その結果に置き換える（＝「化ける」）。
- 1つ外側に出て、同じことを繰り返す。
ポイント：副問い合わせの結果は技術的には常に「表」。1行1列なら「値」、n行1列なら「リスト」として扱え、n行m列の表は
FROMでテーブルとして使う。
単一行副問い合わせ
ts
// 単一の値の代わりに副問い合わせを用いる (単一行副問い合わせ)
//
// 単一行副問い合わせ = 検索結果が 1 行 1 列の値になる副問い合わせ。
// 1 つの値を書ける場所なら基本的にどこでも書ける。
// ・SELECT の選択列リスト
// ・UPDATE の SET 句
// ・WHERE の条件式 (= など) など
//
// [1] SET 句で利用する
// 家計簿アーカイブの食費の平均を求め、家計簿集計の食費の平均を更新する。
// [2] 選択列リストで利用する
// 家計簿集計の食費の合計を、各明細の隣に「過去の合計額」として並べる。
type ShukeiRow = {
費目: string
合計: number
平均: number
最大: number
最小: number
回数: number
}
async function show(label: string, rows: readonly unknown[]) {
console.log(`\n--- ${label} ---`)
console.table(rows)
}
async function showShukei(label: string) {
const rows = await sql<ShukeiRow[]>`
SELECT 費目, 合計, 平均, 最大, 最小, 回数 FROM 家計簿集計 ORDER BY 費目
`
await show(label, rows)
}
async function main() {
try {
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿集計;`)
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿アーカイブ;`)
await sql.unsafe(`
CREATE TABLE 家計簿集計 (
費目 VARCHAR(20) PRIMARY KEY,
合計 INTEGER, 平均 INTEGER, 最大 INTEGER, 最小 INTEGER, 回数 INTEGER
);
`)
await sql.unsafe(`
CREATE TABLE 家計簿アーカイブ (
日付 DATE,
メモ VARCHAR(40),
費目 VARCHAR(20),
出金額 INTEGER
);
`)
// 集計テーブル (作成済みの集計結果)
await sql`INSERT INTO 家計簿集計 (費目, 合計, 平均, 最大, 最小, 回数) VALUES
('居住費', 240000, 80000, 80000, 80000, 3),
('水道光熱費', 11760, 5880, 7560, 4200, 2),
('食費', 10380, 3460, 5000, 380, 3),
('教養娯楽費', 4600, 2300, 2800, 1800, 2),
('給料', 840000, 280000, 280000, 280000, 3)
`
// 明細を貯めたアーカイブテーブル (食費は 5000 が 2 件)
await sql`INSERT INTO 家計簿アーカイブ (日付, メモ, 費目, 出金額) VALUES
('2023-12-24', 'レストランみやび', '食費', 5000),
('2024-01-13', '新年会', '食費', 5000),
('2023-12-20', '電気代', '水道光熱費', 4200),
('2024-01-05', '映画', '教養娯楽費', 1800)
`
await showShukei('家計簿集計テーブル (更新前)')
// ============================================================
// [1] SET 句で単一行副問い合わせを利用する
// SET 平均 = (アーカイブの食費の平均) → 5000 に化ける
// ============================================================
console.log('\n========== [1] SET 句で副問い合わせを利用 ==========')
// 先に副問い合わせ単体の結果を確認 (1行1列 = 5000 に化ける)
await show(
'副問い合わせ単体: アーカイブの食費の平均',
await sql`
SELECT AVG(出金額) AS 食費の平均
FROM 家計簿アーカイブ
WHERE 出金額 > 0 AND 費目 = '食費'
`,
)
await sql`
UPDATE 家計簿集計
SET 平均 = (SELECT AVG(出金額)
FROM 家計簿アーカイブ
WHERE 出金額 > 0
AND 費目 = '食費')
WHERE 費目 = '食費'
`
await showShukei('家計簿集計テーブル (食費の平均を更新後 ※平均のみ更新で不整合)')
// ============================================================
// [2] 選択列リストで単一行副問い合わせを利用する
// (SELECT 合計 FROM 家計簿集計 WHERE 費目='食費') → 10380 に化ける
// ============================================================
console.log('\n========== [2] 選択列リストで副問い合わせを利用 ==========')
// 先に副問い合わせ単体の結果を確認 (1行1列 = 10380)
await show(
'副問い合わせ単体: 家計簿集計の食費の合計',
await sql`
SELECT 合計 AS 食費の合計 FROM 家計簿集計 WHERE 費目 = '食費'
`,
)
await show(
'食費の各明細 + 過去の合計額 (副問い合わせ)',
await sql`
SELECT 日付, メモ, 出金額,
(SELECT 合計 FROM 家計簿集計 WHERE 費目 = '食費') AS 過去の合計額
FROM 家計簿アーカイブ
WHERE 費目 = '食費'
ORDER BY 日付
`,
)
} catch (e) {
console.error('❌ エラーが発生しました:', e)
} finally {
await sql.end()
}
}
main()実行結果
sh
--- 家計簿集計テーブル (更新前) ---
┌─────────┬──────────────┬────────┬────────┬────────┬────────┬──────┐
│ (index) │ 費目 │ 合計 │ 平均 │ 最大 │ 最小 │ 回数 │
├─────────┼──────────────┼────────┼────────┼────────┼────────┼──────┤
│ 0 │ '居住費' │ 240000 │ 80000 │ 80000 │ 80000 │ 3 │
│ 1 │ '教養娯楽費' │ 4600 │ 2300 │ 2800 │ 1800 │ 2 │
│ 2 │ '水道光熱費' │ 11760 │ 5880 │ 7560 │ 4200 │ 2 │
│ 3 │ '給料' │ 840000 │ 280000 │ 280000 │ 280000 │ 3 │
│ 4 │ '食費' │ 10380 │ 3460 │ 5000 │ 380 │ 3 │
└─────────┴──────────────┴────────┴────────┴────────┴────────┴──────┘
========== [1] SET 句で副問い合わせを利用 ==========
--- 副問い合わせ単体: アーカイブの食費の平均 ---
┌─────────┬─────────────────────────┐
│ (index) │ 食費の平均 │
├─────────┼─────────────────────────┤
│ 0 │ '5000.0000000000000000' │
└─────────┴─────────────────────────┘
--- 家計簿集計テーブル (食費の平均を更新後 ※平均のみ更新で不整合) ---
┌─────────┬──────────────┬────────┬────────┬────────┬────────┬──────┐
│ (index) │ 費目 │ 合計 │ 平均 │ 最大 │ 最小 │ 回数 │
├─────────┼──────────────┼────────┼────────┼────────┼────────┼──────┤
│ 0 │ '居住費' │ 240000 │ 80000 │ 80000 │ 80000 │ 3 │
│ 1 │ '教養娯楽費' │ 4600 │ 2300 │ 2800 │ 1800 │ 2 │
│ 2 │ '水道光熱費' │ 11760 │ 5880 │ 7560 │ 4200 │ 2 │
│ 3 │ '給料' │ 840000 │ 280000 │ 280000 │ 280000 │ 3 │
│ 4 │ '食費' │ 10380 │ 5000 │ 5000 │ 380 │ 3 │
└─────────┴──────────────┴────────┴────────┴────────┴────────┴──────┘
========== [2] 選択列リストで副問い合わせを利用 ==========
--- 副問い合わせ単体: 家計簿集計の食費の合計 ---
┌─────────┬────────────┐
│ (index) │ 食費の合計 │
├─────────┼────────────┤
│ 0 │ 10380 │
└─────────┴────────────┘
--- 食費の各明細 + 過去の合計額 (副問い合わせ) ---
┌─────────┬──────────────────────────┬────────────────────┬────────┬──────────────┐
│ (index) │ 日付 │ メモ │ 出金額 │ 過去の合計額 │
├─────────┼──────────────────────────┼────────────────────┼────────┼──────────────┤
│ 0 │ 2023-12-24T00:00:00.000Z │ 'レストランみやび' │ 5000 │ 10380 │
│ 1 │ 2024-01-13T00:00:00.000Z │ '新年会' │ 5000 │ 10380 │
└─────────┴──────────────────────────┴────────────────────┴────────┴──────────────┘複数行副問い合わせ
ts
// 複数の値の代わりに副問い合わせを用いる (複数行副問い合わせ)
//
// 複数行副問い合わせ = 検索結果が n 行 1 列 (複数の値) になる副問い合わせ。
// 「複数の値を列挙する場所」に書ける。代表は IN / ANY / ALL。
//
// [1] IN 演算子 (値リスト) … WHERE 費目 IN ('食費', ...)
// [2] IN 演算子 (副問い合わせ) … WHERE 費目 IN (SELECT DISTINCT 費目 ...)
// [3] ANY / ALL 演算子 … WHERE 出金額 < ANY (SELECT ...)
// [4] エラーになる例 … 単一値用の < に複数行を渡すとエラー
// [5] 副問い合わせと NULL … NOT IN に NULL が混ざると 0 行になる
async function runTest(label: string, run: () => Promise<readonly unknown[]>) {
try {
const rows = await run()
console.log(`\n✅ ${label} → ${rows.length}件`)
console.table(rows)
} catch (e) {
const msg = e instanceof Error ? e.message : String(e)
console.log(`\n❌ ${label} → エラー: ${msg}`)
}
}
async function main() {
try {
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿集計;`)
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿アーカイブ;`)
await sql.unsafe(`
CREATE TABLE 家計簿集計 (
費目 VARCHAR(20) PRIMARY KEY,
合計 INTEGER, 平均 INTEGER, 最大 INTEGER, 最小 INTEGER, 回数 INTEGER
);
`)
await sql.unsafe(`
CREATE TABLE 家計簿 (
日付 DATE,
費目 VARCHAR(20),
メモ VARCHAR(40),
入金額 INTEGER,
出金額 INTEGER
);
`)
await sql.unsafe(`
CREATE TABLE 家計簿アーカイブ (
日付 DATE,
メモ VARCHAR(40),
費目 VARCHAR(20),
出金額 INTEGER
);
`)
await sql`INSERT INTO 家計簿集計 (費目, 合計, 平均, 最大, 最小, 回数) VALUES
('居住費', 240000, 80000, 80000, 80000, 3),
('水道光熱費', 11760, 5880, 7560, 4200, 2),
('食費', 10380, 3460, 5000, 380, 3),
('教養娯楽費', 4600, 2300, 2800, 1800, 2),
('給料', 840000, 280000, 280000, 280000, 3)
`
// 家計簿(明細)は 居住費 を含まない 4 費目
await sql`INSERT INTO 家計簿 (日付, 費目, メモ, 入金額, 出金額) VALUES
('2024-02-03', '食費', 'コーヒーを購入', 0, 380),
('2024-02-10', '水道光熱費', '電気代', 0, 7560),
('2024-02-12', '教養娯楽費', '映画', 0, 1800),
('2024-02-25', '給料', '給料', 280000, 0)
`
// アーカイブの食費は 5000 が 2 件 + 費目 NULL の行を 1 件混ぜておく
await sql`INSERT INTO 家計簿アーカイブ (日付, メモ, 費目, 出金額) VALUES
('2023-12-24', 'レストランみやび', '食費', 5000),
('2024-01-13', '新年会', '食費', 5000),
('2024-01-20', '用途不明の出費', NULL, 1200)
`
// ============================================================
// [1] IN 演算子 (値リスト)
// ============================================================
console.log('\n========== [1] IN (値リスト) ==========')
await runTest(
"費目 IN ('食費','水道光熱費','教養娯楽費','給料')",
() => sql`
SELECT 費目, 合計, 平均, 最大, 最小, 回数
FROM 家計簿集計
WHERE 費目 IN ('食費', '水道光熱費', '教養娯楽費', '給料')
ORDER BY 費目
`,
)
// ============================================================
// [2] IN 演算子 (副問い合わせ) … 値リストを副問い合わせに置き換える
// 副問い合わせは「家計簿に存在する費目の一覧」に化ける
// ============================================================
console.log('\n========== [2] IN (副問い合わせ) ==========')
await runTest(
'内側: SELECT DISTINCT 費目 FROM 家計簿',
() => sql`
SELECT DISTINCT 費目 FROM 家計簿 ORDER BY 費目
`,
)
await runTest(
'費目 IN (SELECT DISTINCT 費目 FROM 家計簿)',
() => sql`
SELECT 費目, 合計, 平均, 最大, 最小, 回数
FROM 家計簿集計
WHERE 費目 IN (SELECT DISTINCT 費目 FROM 家計簿)
ORDER BY 費目
`,
)
// ============================================================
// [3] ANY 演算子 … 「いずれかの値より小さい」を判定
// 出金額 < ANY (アーカイブの食費の金額たち) = いずれか1つより小さい
// ============================================================
console.log('\n========== [3] ANY 演算子 ==========')
await runTest(
'出金額 < ANY (アーカイブの食費の金額)',
() => sql`
SELECT 日付, 費目, メモ, 入金額, 出金額
FROM 家計簿
WHERE 費目 = '食費'
AND 出金額 < ANY (SELECT 出金額 FROM 家計簿アーカイブ WHERE 費目 = '食費')
`,
)
// ============================================================
// [4] エラーになる例: 単一値用の < に複数行を渡す
// どの値と比べればよいか分からずエラーになる → ANY を付ければOK
// ============================================================
console.log('\n========== [4] 単一値演算子に複数行を渡すとエラー ==========')
await runTest(
'出金額 < (複数行を返す副問い合わせ) … エラー',
() => sql`
SELECT 費目, 出金額 FROM 家計簿
WHERE 出金額 < (SELECT 出金額 FROM 家計簿アーカイブ WHERE 費目 = '食費')
`,
)
await runTest(
'出金額 < ANY (...) に直すとOK',
() => sql`
SELECT 費目, 出金額 FROM 家計簿
WHERE 出金額 < ANY (SELECT 出金額 FROM 家計簿アーカイブ WHERE 費目 = '食費')
ORDER BY 出金額
`,
)
// ============================================================
// [5] 副問い合わせと NULL: NOT IN に NULL が混ざると 0 行
// ============================================================
console.log('\n========== [5] 副問い合わせと NULL ==========')
// 値リストに NULL があると NOT IN は 0 行になる
await runTest(
"NOT IN ('食費','水道光熱費', NULL) … 0行になる",
() => sql`
SELECT 費目 FROM 家計簿集計
WHERE 費目 NOT IN ('食費', '水道光熱費', NULL)
`,
)
// 副問い合わせの結果に NULL が混ざる場合も同じく 0 行
await runTest(
'NOT IN (NULLを含む副問い合わせ) … 0行になる',
() => sql`
SELECT 費目 FROM 家計簿集計
WHERE 費目 NOT IN (SELECT 費目 FROM 家計簿アーカイブ)
ORDER BY 費目
`,
)
// 対処1: 副問い合わせ側で IS NOT NULL を付けて NULL を除く
await runTest(
'対処1: ... WHERE 費目 IS NOT NULL で NULL を除外',
() => sql`
SELECT 費目 FROM 家計簿集計
WHERE 費目 NOT IN (SELECT 費目 FROM 家計簿アーカイブ WHERE 費目 IS NOT NULL)
ORDER BY 費目
`,
)
// 対処2: COALESCE で NULL を別の値に置き換える
await runTest(
"対処2: COALESCE(費目, 'なし') で NULL を置換",
() => sql`
SELECT 費目 FROM 家計簿集計
WHERE 費目 NOT IN (SELECT COALESCE(費目, 'なし') FROM 家計簿アーカイブ)
ORDER BY 費目
`,
)
} catch (e) {
console.error('❌ 想定外のエラーが発生しました:', e)
} finally {
await sql.end()
}
}
main()実行結果
sh
========== [1] IN (値リスト) ==========
✅ 費目 IN ('食費','水道光熱費','教養娯楽費','給料') → 4件
┌─────────┬──────────────┬────────┬────────┬────────┬────────┬──────┐
│ (index) │ 費目 │ 合計 │ 平均 │ 最大 │ 最小 │ 回数 │
├─────────┼──────────────┼────────┼────────┼────────┼────────┼──────┤
│ 0 │ '教養娯楽費' │ 4600 │ 2300 │ 2800 │ 1800 │ 2 │
│ 1 │ '水道光熱費' │ 11760 │ 5880 │ 7560 │ 4200 │ 2 │
│ 2 │ '給料' │ 840000 │ 280000 │ 280000 │ 280000 │ 3 │
│ 3 │ '食費' │ 10380 │ 3460 │ 5000 │ 380 │ 3 │
└─────────┴──────────────┴────────┴────────┴────────┴────────┴──────┘
========== [2] IN (副問い合わせ) ==========
✅ 内側: SELECT DISTINCT 費目 FROM 家計簿 → 4件
┌─────────┬──────────────┐
│ (index) │ 費目 │
├─────────┼──────────────┤
│ 0 │ '教養娯楽費' │
│ 1 │ '水道光熱費' │
│ 2 │ '給料' │
│ 3 │ '食費' │
└─────────┴──────────────┘
✅ 費目 IN (SELECT DISTINCT 費目 FROM 家計簿) → 4件
┌─────────┬──────────────┬────────┬────────┬────────┬────────┬──────┐
│ (index) │ 費目 │ 合計 │ 平均 │ 最大 │ 最小 │ 回数 │
├─────────┼──────────────┼────────┼────────┼────────┼────────┼──────┤
│ 0 │ '教養娯楽費' │ 4600 │ 2300 │ 2800 │ 1800 │ 2 │
│ 1 │ '水道光熱費' │ 11760 │ 5880 │ 7560 │ 4200 │ 2 │
│ 2 │ '給料' │ 840000 │ 280000 │ 280000 │ 280000 │ 3 │
│ 3 │ '食費' │ 10380 │ 3460 │ 5000 │ 380 │ 3 │
└─────────┴──────────────┴────────┴────────┴────────┴────────┴──────┘
========== [3] ANY 演算子 ==========
✅ 出金額 < ANY (アーカイブの食費の金額) → 1件
┌─────────┬──────────────────────────┬────────┬──────────────────┬────────┬────────┐
│ (index) │ 日付 │ 費目 │ メモ │ 入金額 │ 出金額 │
├─────────┼──────────────────────────┼────────┼──────────────────┼────────┼────────┤
│ 0 │ 2024-02-03T00:00:00.000Z │ '食費' │ 'コーヒーを購入' │ 0 │ 380 │
└─────────┴──────────────────────────┴────────┴──────────────────┴────────┴────────┘
========== [4] 単一値演算子に複数行を渡すとエラー ==========
❌ 出金額 < (複数行を返す副問い合わせ) … エラー → エラー: more than one row returned by a subquery used as an expression
✅ 出金額 < ANY (...) に直すとOK → 3件
┌─────────┬──────────────┬────────┐
│ (index) │ 費目 │ 出金額 │
├─────────┼──────────────┼────────┤
│ 0 │ '給料' │ 0 │
│ 1 │ '食費' │ 380 │
│ 2 │ '教養娯楽費' │ 1800 │
└─────────┴──────────────┴────────┘
========== [5] 副問い合わせと NULL ==========
✅ NOT IN ('食費','水道光熱費', NULL) … 0行になる → 0件
┌─────────┐
│ (index) │
├─────────┤
└─────────┘
✅ NOT IN (NULLを含む副問い合わせ) … 0行になる → 0件
┌─────────┐
│ (index) │
├─────────┤
└─────────┘
✅ 対処1: ... WHERE 費目 IS NOT NULL で NULL を除外 → 4件
┌─────────┬──────────────┐
│ (index) │ 費目 │
├─────────┼──────────────┤
│ 0 │ '居住費' │
│ 1 │ '教養娯楽費' │
│ 2 │ '水道光熱費' │
│ 3 │ '給料' │
└─────────┴──────────────┘
✅ 対処2: COALESCE(費目, 'なし') で NULL を置換 → 4件
┌─────────┬──────────────┐
│ (index) │ 費目 │
├─────────┼──────────────┤
│ 0 │ '居住費' │
│ 1 │ '教養娯楽費' │
│ 2 │ '水道光熱費' │
│ 3 │ '給料' │
└─────────┴──────────────┘表の結果となる副問合せ
ts
// 表の代わりに副問い合わせを用いる (表副問い合わせ)
//
// 表副問い合わせ = 検索結果が n 行 m 列の「表」になる副問い合わせ。
// 表を書ける場所 (FROM 句や INSERT 文) に書ける。
//
// [1] FROM 句で利用する
// 2 つの表を UNION した結果を 1 つの表 (SUB) とみなして集計する。
// [2] INSERT 文で利用する
// 集計結果 (表形式) を 1 回の INSERT で複数行まとめて登録する。
// [3] 相関副問い合わせ (EXISTS)
// 内側の副問い合わせから外側の表の列を参照するパターン。
async function show(label: string, rows: readonly unknown[]) {
console.log(`\n--- ${label} (${rows.length}件) ---`)
console.table(rows)
}
async function main() {
try {
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿;`)
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿アーカイブ;`)
await sql.unsafe(`DROP TABLE IF EXISTS 家計簿集計;`)
await sql.unsafe(`
CREATE TABLE 家計簿 (日付 DATE, 費目 VARCHAR(20), 出金額 INTEGER);
`)
await sql.unsafe(`
CREATE TABLE 家計簿アーカイブ (日付 DATE, 費目 VARCHAR(20), 出金額 INTEGER);
`)
await sql.unsafe(`
CREATE TABLE 家計簿集計 (
費目 VARCHAR(20) PRIMARY KEY,
合計 INTEGER, 平均 INTEGER, 回数 INTEGER
);
`)
// 家計簿 (今月分)
await sql`INSERT INTO 家計簿 (日付, 費目, 出金額) VALUES
('2024-01-05', '食費', 5000),
('2024-01-10', '水道光熱費', 7560),
('2024-01-15', '食費', 380),
('2024-01-20', '給料', 0)
`
// 家計簿アーカイブ (過去分。2024年1月分とそれ以外を混ぜておく)
await sql`INSERT INTO 家計簿アーカイブ (日付, 費目, 出金額) VALUES
('2024-01-08', '居住費', 80000),
('2024-01-25', '教養娯楽費', 9600),
('2023-12-30', '食費', 4200)
`
// ============================================================
// [1] FROM 句で表副問い合わせを利用する
// 家計簿 と アーカイブの2024年1月分を UNION した表を SUB として集計
// 合計: 5000+7560+380+0 + 80000+9600 = 102540
// ============================================================
console.log('\n========== [1] FROM 句で利用する ==========')
// 先に内側 (UNION した表) だけを見てみる
await show(
'内側: 家計簿 ∪ アーカイブ(2024年1月) の表',
await sql`
SELECT 日付, 費目, 出金額 FROM 家計簿
UNION
SELECT 日付, 費目, 出金額 FROM 家計簿アーカイブ
WHERE 日付 >= '2024-01-01' AND 日付 <= '2024-01-31'
`,
)
await show(
'外側: その表(SUB)を集計して出金額合計',
await sql`
SELECT SUM(SUB.出金額) AS 出金額合計
FROM (SELECT 日付, 費目, 出金額 FROM 家計簿
UNION
SELECT 日付, 費目, 出金額 FROM 家計簿アーカイブ
WHERE 日付 >= '2024-01-01' AND 日付 <= '2024-01-31') AS SUB
`,
)
// ============================================================
// [2] INSERT 文で表副問い合わせを利用する
// 集計結果(表)を 1 回の INSERT で複数行まとめて登録する
// ============================================================
console.log('\n========== [2] INSERT 文で利用する ==========')
await sql`
INSERT INTO 家計簿集計 (費目, 合計, 平均, 回数)
SELECT 費目, SUM(出金額), AVG(出金額), COUNT(*)
FROM 家計簿
WHERE 出金額 > 0
GROUP BY 費目
`
await show(
'1回のINSERTで複数行が登録された家計簿集計',
await sql`
SELECT 費目, 合計, 平均, 回数 FROM 家計簿集計 ORDER BY 費目
`,
)
// ============================================================
// [3] 相関副問い合わせ (EXISTS)
// 家計簿集計のうち、家計簿(今月)にも登場する費目だけを抽出する。
// 内側の WHERE で外側の「家計簿集計.費目」を参照しているのがポイント。
// ============================================================
console.log('\n========== [3] 相関副問い合わせ (EXISTS) ==========')
await show(
'今月の家計簿にも登場する費目だけ抽出',
await sql`
SELECT 費目, 合計
FROM 家計簿集計
WHERE EXISTS (SELECT *
FROM 家計簿
WHERE 家計簿.費目 = 家計簿集計.費目)
ORDER BY 費目
`,
)
} catch (e) {
console.error('❌ エラーが発生しました:', e)
} finally {
await sql.end()
}
}
main()実行結果
sh
========== [1] FROM 句で利用する ==========
--- 内側: 家計簿 ∪ アーカイブ(2024年1月) の表 (6件) ---
┌─────────┬──────────────────────────┬──────────────┬────────┐
│ (index) │ 日付 │ 費目 │ 出金額 │
├─────────┼──────────────────────────┼──────────────┼────────┤
│ 0 │ 2024-01-15T00:00:00.000Z │ '食費' │ 380 │
│ 1 │ 2024-01-20T00:00:00.000Z │ '給料' │ 0 │
│ 2 │ 2024-01-08T00:00:00.000Z │ '居住費' │ 80000 │
│ 3 │ 2024-01-25T00:00:00.000Z │ '教養娯楽費' │ 9600 │
│ 4 │ 2024-01-05T00:00:00.000Z │ '食費' │ 5000 │
│ 5 │ 2024-01-10T00:00:00.000Z │ '水道光熱費' │ 7560 │
└─────────┴──────────────────────────┴──────────────┴────────┘
--- 外側: その表(SUB)を集計して出金額合計 (1件) ---
┌─────────┬────────────┐
│ (index) │ 出金額合計 │
├─────────┼────────────┤
│ 0 │ '102540' │
└─────────┴────────────┘
========== [2] INSERT 文で利用する ==========
--- 1回のINSERTで複数行が登録された家計簿集計 (2件) ---
┌─────────┬──────────────┬──────┬──────┬──────┐
│ (index) │ 費目 │ 合計 │ 平均 │ 回数 │
├─────────┼──────────────┼──────┼──────┼──────┤
│ 0 │ '水道光熱費' │ 7560 │ 7560 │ 1 │
│ 1 │ '食費' │ 5380 │ 2690 │ 2 │
└─────────┴──────────────┴──────┴──────┴──────┘
========== [3] 相関副問い合わせ (EXISTS) ==========
--- 今月の家計簿にも登場する費目だけ抽出 (2件) ---
┌─────────┬──────────────┬──────┐
│ (index) │ 費目 │ 合計 │
├─────────┼──────────────┼──────┤
│ 0 │ '水道光熱費' │ 7560 │
│ 1 │ '食費' │ 5380 │
└─────────┴──────────────┴──────┘副問い合わせのまとめ
SQL文のネスト
- SQL文の中に別のSELECT文を記述することができ、これを副問い合わせや副照会、またはサブクエリという。
- 副問い合わせは、実行すると何らかの値に置き換わる。
- 副問い合わせは、より内側にあるものから外側に向かって順に評価されていく。
副問い合わせのパターン
- 副問い合わせの結果が1行1列になるものを単一行副問い合わせという。
- 副問い合わせの結果がn行1列になるものを複数行副問い合わせという。
- 副問い合わせの結果がn行m列の表形式になる副問い合わせも利用される。
複数行副問い合わせと演算子
- 複数行副問い合わせは、IN、ANY、ALL演算子などと併せてよく用いられる。
- 複数行副問い合わせの結果にNULLが含まれると、NOT IN、<> ALL演算子の評価結果もNULLとなる。
問題
牛を個体識別番号で管理している個体識別テーブルがあります。このテーブルについて、次の設問1〜3で指示されたSQL文を作成してください。
個体識別テーブル
| 列名 | データ型 | 備考 |
|---|---|---|
| 個体識別番号 | CHAR(4) | 牛を一意に管理する番号 |
| 出生日 | DATE | その牛が出生した日付 |
| 雌雄コード | CHAR(1) | 牛の性別を表すコード 1:雄 2:雌 |
| 母牛番号 | CHAR(4) | 母牛の個体識別番号 |
| 品種コード | CHAR(2) | 牛の品種を表すコード 01:乳用種 02:肉用種 03:交雑種 |
| 飼育県 | VARCHAR(10) | 牛を飼育している都道府県名 |
設問
1. 飼育県別に飼育頭数を集計し、その結果を次の頭数集計テーブルに登録する。
頭数集計テーブル
| 列名 | データ型 | 備考 |
|---|---|---|
| 飼育県 | VARCHAR(20) | 牛を飼育している都道府県名 |
| 頭数 | INTEGER | 飼育している牛の数 |
ts
await sql`
INSERT INTO 頭数集計 (飼育県, 頭数)
SELECT 飼育県, COUNT(*)
FROM 個体識別
GROUP BY 飼育県
`2. 1で作成した頭数集計テーブルで、飼育頭数の多いほうから3つの都道府県で飼育されている牛のデータを、個体識別テーブルより抽出する。抽出する項目は、都道府県名、個体識別番号、雌雄とする。ただし、雌雄はコードではなく「雄」「雌」の日本語表記とする。
ts
await sql`
SELECT 飼育県 AS 都道府県名,
個体識別番号,
CASE 雌雄コード WHEN '1' THEN '雄' WHEN '2' THEN '雌' END AS 雌雄
FROM 個体識別
WHERE 飼育県 IN (SELECT 飼育県 FROM 頭数集計 ORDER BY 頭数 DESC OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY)
`3. 個体識別テーブルには母牛についてもデータ登録されており、母牛が乳用種である牛の一覧を個体識別テーブルより抽出したい。抽出する項目は、個体識別番号、品種、出生日、母牛番号とする。ただし、品種はコードではなく「乳用種」「肉用種」「交雑種」の日本語表記とする。
ts
await sql`
SELECT 個体識別番号,
CASE 品種コード
WHEN '01' THEN '乳用種'
WHEN '02' THEN '肉用種'
WHEN '03' THEN '交雑種'
END AS 品種,
出生日,
母牛番号
FROM 個体識別
WHERE 母牛番号 IN (SELECT 個体識別番号
FROM 個体識別
WHERE 品種コード = '01')
`