検索結果の加工
検索結果を加工する主なキーワード
| キーワード | 内容 |
|---|---|
| DISTINCT | 検索結果から重複行を除外する |
| ORDER BY | 検索結果の順序を並び替える |
| OFFSET - FETCH | 検索結果から件数を限定して取得する |
| UNION | 検索結果にほかの検索結果を足し合わせる |
| EXCEPT | 検索結果からほかの検索結果を差し引く |
| INTERSECT | 検索結果とほかの検索結果で重複する部分を取得する |
住副業を除外する
sql
SELECT DISTINCT 列名...
FROM テーブル名例
ts
// SELECT DISTINCT の挙動を確かめるための実験用テーブル
// 列名(原文) -> SQL列名
// カテゴリ -> category VARCHAR(20)
// 販売店 -> store CHAR(1) NULL可 (NULLの扱いを確認するため)
// 価格 -> price INTEGER
async function createSalesTable() {
return sql.unsafe(`
CREATE TABLE IF NOT EXISTS SALES (
category VARCHAR(20),
store CHAR(1),
price INTEGER
);
`)
}
async function seedSales() {
// 意図的に重複行・NULL を混ぜる
// 重複(完全一致): ゲーム/A/5000 ×2, ゲーム/B/4500 ×2
// NULL含む: DVD/NULL/2800, DVD/NULL/3200
// category 単独: ゲーム/書籍/DVD の3種
// store 単独: A, B, C, NULL の4種
await sql`INSERT INTO SALES VALUES ('ゲーム', 'A', 5000)`
await sql`INSERT INTO SALES VALUES ('ゲーム', 'A', 5000)`
await sql`INSERT INTO SALES VALUES ('ゲーム', 'B', 4500)`
await sql`INSERT INTO SALES VALUES ('ゲーム', 'B', 4500)`
await sql`INSERT INTO SALES VALUES ('書籍', 'A', 1200)`
await sql`INSERT INTO SALES VALUES ('書籍', 'C', 1100)`
await sql`INSERT INTO SALES VALUES ('DVD', 'A', 3000)`
await sql`INSERT INTO SALES VALUES ('DVD', NULL, 2800)`
await sql`INSERT INTO SALES VALUES ('DVD', NULL, 3200)`
}
async function distinctMain() {
try {
await createSalesTable()
// 既にデータがある場合は再投入をスキップ(冪等)
const [{ count }] = await sql<{ count: number }[]>`
SELECT COUNT(*)::int AS count FROM SALES
`
if (count === 0) {
await seedSales()
}
const all = await sql`SELECT * FROM SALES`
console.log('--- 全件 (重複行・NULL含む) ---')
console.table(all)
// 1) 普通のSELECT: 重複したまま全行返る
// category列だけを取り出すと、ゲーム×4, 書籍×2, DVD×3 で計9件
const q1 = await sql`SELECT category FROM SALES`
console.log('\n[1] SELECT category → 9件 (重複あり)')
console.table(q1)
// 2) SELECT DISTINCT: 重複を排除
// DISTINCTは「SELECTした列の組み合わせ」が同じ行をまとめる
// → ゲーム, 書籍, DVD の3件
const q2 = await sql`SELECT DISTINCT category FROM SALES`
console.log('\n[2] SELECT DISTINCT category → 3件')
console.table(q2)
// 3) DISTINCT on 単一列(NULL含む): NULL は1つにまとめられる
// storeの値: A,A,B,B,A,C,A,NULL,NULL → DISTINCT: A, B, C, NULL の4件
// 重要: WHEREでは NULL=NULL は UNKNOWN だが、
// DISTINCTでは NULL同士は「同じもの」として扱われる(SQL標準)
const q3 = await sql`SELECT DISTINCT store FROM SALES`
console.log('\n[3] SELECT DISTINCT store → 4件 (NULLは1つにまとまる)')
console.table(q3)
// 4) DISTINCT on 複数列: 「列ごと」ではなく「組み合わせ」が一意になる
// (category, store) のペアでユニーク
// → (ゲーム,A), (ゲーム,B), (書籍,A), (書籍,C), (DVD,A), (DVD,NULL) の6件
// ※DVD/NULL は2行あったが、組み合わせとしては1つ
const q4 = await sql`SELECT DISTINCT category, store FROM SALES`
console.log('\n[4] SELECT DISTINCT category, store → 6件 (組み合わせがユニーク)')
console.table(q4)
// 5) DISTINCT + ORDER BY: 重複排除のあとに並び替えできる
const q5 = await sql`SELECT DISTINCT category FROM SALES ORDER BY category`
console.log('\n[5] DISTINCT + ORDER BY')
console.table(q5)
// 6) COUNT(DISTINCT col): 重複を除いたユニークな値の件数を数える
// 通常の COUNT(category) は NULL以外を全部数える(=9)
// COUNT(DISTINCT category) はユニーク値の数(=3)
const q6 = await sql`
SELECT
COUNT(*) AS total_rows,
COUNT(category) AS count_category_non_null,
COUNT(DISTINCT category) AS distinct_category,
COUNT(store) AS count_store_non_null,
COUNT(DISTINCT store) AS distinct_store
FROM SALES
`
console.log('\n[6] COUNT と COUNT(DISTINCT) の比較')
// total_rows : 9 (全行数)
// count_category_non_null : 9 (categoryはNULLなし)
// distinct_category : 3 (ゲーム/書籍/DVD)
// count_store_non_null : 7 (NULL2件を除いた数)
// distinct_store : 3 (A/B/C; ※COUNT(DISTINCT)はNULLを数えない!)
console.table(q6)
// 7) DISTINCT は SELECT 全体に1回だけ書く
// ❌ SELECT DISTINCT(category), store ... はDISTINCT(...)の関数呼び出しに見えるが、
// 実態は SELECT DISTINCT category, store と同じで全列にかかる
// ✓ 関数のように個別の列だけを重複排除することはできない
const q7 = await sql`SELECT DISTINCT(category), store FROM SALES`
console.log('\n[7] DISTINCT(category), store の挙動 (=DISTINCT category, store と同じ)')
console.table(q7)
} catch (e) {
console.error('❌ エラーが発生しました:', e)
} finally {
await sql.end()
}
}
distinctMain()実行結果
sh
--- 全件 (重複行・NULL含む) ---
┌─────────┬──────────┬───────┬───────┐
│ (index) │ category │ store │ price │
├─────────┼──────────┼───────┼───────┤
│ 0 │ 'ゲーム' │ 'A' │ 5000 │
│ 1 │ 'ゲーム' │ 'A' │ 5000 │
│ 2 │ 'ゲーム' │ 'B' │ 4500 │
│ 3 │ 'ゲーム' │ 'B' │ 4500 │
│ 4 │ '書籍' │ 'A' │ 1200 │
│ 5 │ '書籍' │ 'C' │ 1100 │
│ 6 │ 'DVD' │ 'A' │ 3000 │
│ 7 │ 'DVD' │ null │ 2800 │
│ 8 │ 'DVD' │ null │ 3200 │
└─────────┴──────────┴───────┴───────┘
[1] SELECT category → 9件 (重複あり)
┌─────────┬──────────┐
│ (index) │ category │
├─────────┼──────────┤
│ 0 │ 'ゲーム' │
│ 1 │ 'ゲーム' │
│ 2 │ 'ゲーム' │
│ 3 │ 'ゲーム' │
│ 4 │ '書籍' │
│ 5 │ '書籍' │
│ 6 │ 'DVD' │
│ 7 │ 'DVD' │
│ 8 │ 'DVD' │
└─────────┴──────────┘
[2] SELECT DISTINCT category → 3件
┌─────────┬──────────┐
│ (index) │ category │
├─────────┼──────────┤
│ 0 │ '書籍' │
│ 1 │ 'ゲーム' │
│ 2 │ 'DVD' │
└─────────┴──────────┘
[3] SELECT DISTINCT store → 4件 (NULLは1つにまとまる)
┌─────────┬───────┐
│ (index) │ store │
├─────────┼───────┤
│ 0 │ null │
│ 1 │ 'B' │
│ 2 │ 'C' │
│ 3 │ 'A' │
└─────────┴───────┘
[4] SELECT DISTINCT category, store → 6件 (組み合わせがユニーク)
┌─────────┬──────────┬───────┐
│ (index) │ category │ store │
├─────────┼──────────┼───────┤
│ 0 │ '書籍' │ 'C' │
│ 1 │ 'DVD' │ 'A' │
│ 2 │ 'ゲーム' │ 'B' │
│ 3 │ 'DVD' │ null │
│ 4 │ 'ゲーム' │ 'A' │
│ 5 │ '書籍' │ 'A' │
└─────────┴──────────┴───────┘
[5] DISTINCT + ORDER BY
┌─────────┬──────────┐
│ (index) │ category │
├─────────┼──────────┤
│ 0 │ 'DVD' │
│ 1 │ 'ゲーム' │
│ 2 │ '書籍' │
└─────────┴──────────┘
[6] COUNT と COUNT(DISTINCT) の比較
┌─────────┬────────────┬─────────────────────────┬───────────────────┬──────────────────────┬────────────────┐
│ (index) │ total_rows │ count_category_non_null │ distinct_category │ count_store_non_null │ distinct_store │
├─────────┼────────────┼─────────────────────────┼───────────────────┼──────────────────────┼────────────────┤
│ 0 │ '9' │ '9' │ '3' │ '7' │ '3' │
└─────────┴────────────┴─────────────────────────┴───────────────────┴──────────────────────┴────────────────┘
[7] DISTINCT(category), store の挙動 (=DISTINCT category, store と同じ)
┌─────────┬──────────┬───────┐
│ (index) │ category │ store │
├─────────┼──────────┼───────┤
│ 0 │ '書籍' │ 'C' │
│ 1 │ 'DVD' │ 'A' │
│ 2 │ 'ゲーム' │ 'B' │
│ 3 │ 'DVD' │ null │
│ 4 │ 'ゲーム' │ 'A' │
│ 5 │ '書籍' │ 'A' │
└─────────┴──────────┴───────┘検索結果を並び替える
sql
SELECT 列名... FROM テーブル名
ORDER BY 列名 並び順※ 並び順はには、ASCまたはDESCを指定する(省略するとASCと同じ意味になる)。
例
ts
// ORDER BY: 日付順 & 複数列ソートの実験用テーブル
// - 同じ部署が複数いる(副キーの効果を見る)
// - 同じ給与が複数(タイブレーカーの効果)
// - 同じ入社日が複数(日付ソートの同値ケース)
// - NULLを含む(NULLの並び順)
async function createEmployeesTable() {
return sql.unsafe(`
CREATE TABLE IF NOT EXISTS EMPLOYEES (
id INTEGER PRIMARY KEY,
name VARCHAR(20),
department VARCHAR(20),
salary INTEGER,
hired_at DATE
);
`)
}
async function seedEmployees() {
// 同じ給与: 開発の田中/高橋 = 500000
// 同じ入社日: 佐藤/田中 = 2019-04-01 (日付タイブレーク用)
// NULL: 伊藤の salary, 渡辺の hired_at
await sql`INSERT INTO EMPLOYEES VALUES (1, '佐藤', '営業', 400000, '2019-04-01')`
await sql`INSERT INTO EMPLOYEES VALUES (2, '鈴木', '営業', 350000, '2021-04-01')`
await sql`INSERT INTO EMPLOYEES VALUES (3, '田中', '開発', 500000, '2019-04-01')`
await sql`INSERT INTO EMPLOYEES VALUES (4, '高橋', '開発', 500000, '2022-04-01')`
await sql`INSERT INTO EMPLOYEES VALUES (5, '伊藤', '人事', NULL, '2023-10-01')`
await sql`INSERT INTO EMPLOYEES VALUES (6, '渡辺', '人事', 420000, NULL)`
await sql`INSERT INTO EMPLOYEES VALUES (7, '山本', '開発', 480000, '2018-04-01')`
}
async function orderByMain() {
try {
await createEmployeesTable()
const [{ count }] = await sql<{ count: number }[]>`
SELECT COUNT(*)::int AS count FROM EMPLOYEES
`
if (count === 0) {
await seedEmployees()
}
const all = await sql`SELECT * FROM EMPLOYEES`
console.log('--- 元データ (ORDER BYなし) ---')
console.table(all)
// ============================================================
// パート1: 日付順
// ============================================================
console.log('\n========== 日付順ソート ==========')
// 1-A) 入社日が古い順(ASC) - DATE型はそのまま大小比較できる
// → 山本(2018), 佐藤(2019), 田中(2019), 鈴木(2021), 高橋(2022),
// 伊藤(2023), 渡辺(NULL) ※NULLはASCでは最後
const q1a = await sql`
SELECT name, hired_at FROM EMPLOYEES
ORDER BY hired_at ASC
`
console.log('\n[1-A] 入社日が古い順 (hired_at ASC)')
console.table(q1a)
// 1-B) 入社日が新しい順(DESC) - NULLはDESCでは先頭に来るので明示
// → 渡辺(NULL), 伊藤(2023), 高橋(2022), 鈴木(2021), 田中(2019),
// 佐藤(2019), 山本(2018)
// ※「新しい順」という意味では NULL を末尾に置くのが自然なケースが多い
const q1b = await sql`
SELECT name, hired_at FROM EMPLOYEES
ORDER BY hired_at DESC NULLS LAST
`
console.log('\n[1-B] 入社日が新しい順 (DESC NULLS LAST)')
console.table(q1b)
// 1-C) 期間で絞り込み + 入社日順
// WHERE で日付範囲を絞ってから ORDER BY するパターン
const q1c = await sql`
SELECT name, hired_at FROM EMPLOYEES
WHERE hired_at BETWEEN '2019-01-01' AND '2022-12-31'
ORDER BY hired_at ASC
`
console.log('\n[1-C] 2019〜2022年に入社した人を古い順')
console.table(q1c)
// ============================================================
// パート2: 複数列ソート
// ============================================================
console.log('\n========== 複数列ソート ==========')
// 2-A) 部署で並べ、同じ部署内では給与の高い順 (典型例)
// ORDER BY に , で列を並べると "主→副→..." の優先度になる
// → 営業: 佐藤(400k), 鈴木(350k)
// 開発: 田中(500k), 高橋(500k), 山本(480k) ※500k同士の順は不定
// 人事: 渡辺(420k), 伊藤(NULL)
const q2a = await sql`
SELECT name, department, salary FROM EMPLOYEES
ORDER BY department ASC, salary DESC
`
console.log('\n[2-A] 部署昇順 → 給与降順')
console.table(q2a)
// 2-B) 同値タイブレーカー: 給与が同じ田中/高橋を入社日で決定する
// 3列ソートで完全に決定的な順序になる
// → 開発内: 山本(480k), 田中(500k/2019), 高橋(500k/2022)
// ※給与降順の後で、同じ給与の中は入社日昇順
const q2b = await sql`
SELECT name, department, salary, hired_at FROM EMPLOYEES
ORDER BY department ASC, salary DESC, hired_at ASC
`
console.log('\n[2-B] 部署→給与降順→入社日昇順 (3列で同値タイを解消)')
console.table(q2b)
// 2-C) 各列ごとにASC/DESCを混在 (ASC/DESCは列単位で指定)
// "新しい入社順 → 同じ日なら名前順" のような並べ方
const q2c = await sql`
SELECT name, hired_at FROM EMPLOYEES
ORDER BY hired_at DESC NULLS LAST, name ASC
`
console.log('\n[2-C] 入社日が新しい順 → 同日なら名前順')
console.table(q2c)
// 2-D) 主キー以外の列で複数列ソート
// "部署で昇順、同部署内では入社日が古い順、さらに同日なら id順"
const q2d = await sql`
SELECT id, name, department, hired_at FROM EMPLOYEES
ORDER BY department ASC, hired_at ASC NULLS LAST, id ASC
`
console.log('\n[2-D] 部署→入社日(古い順)→id (同部署で同入社日のタイ解消)')
console.table(q2d)
// ============================================================
// パート3: 日付 × 複数列の応用
// ============================================================
console.log('\n========== 応用パターン ==========')
// 3-A) 入社年度ごとにグルーピングして、年度内は給与降順
// EXTRACT(YEAR FROM date) で年だけ取り出せる
// → 同じ入社年でも、給与の多い人を上に並べる
const q3a = await sql`
SELECT name, hired_at, salary,
EXTRACT(YEAR FROM hired_at)::int AS hired_year
FROM EMPLOYEES
WHERE hired_at IS NOT NULL
ORDER BY EXTRACT(YEAR FROM hired_at) ASC, salary DESC NULLS LAST
`
console.log('\n[3-A] 入社年度昇順 → 同年度内で給与降順 (年だけ取り出してソート)')
console.table(q3a)
// 3-B) 「最近入社した人を新しい順、ただし同じ部署はまとめる」
// 部署を主キー、入社日を副キーで降順
const q3b = await sql`
SELECT department, name, hired_at FROM EMPLOYEES
ORDER BY department ASC, hired_at DESC NULLS LAST
`
console.log('\n[3-B] 部署ごとに新しい入社順')
console.table(q3b)
// 3-C) 入社日トップ3 (古参から3名)
const q3c = await sql`
SELECT name, hired_at FROM EMPLOYEES
WHERE hired_at IS NOT NULL
ORDER BY hired_at ASC
LIMIT 3
`
console.log('\n[3-C] 古参トップ3 (ORDER BY hired_at ASC LIMIT 3)')
console.table(q3c)
} catch (e) {
console.error('❌ エラーが発生しました:', e)
} finally {
await sql.end()
}
}
orderByMain()実行結果
sh
--- 元データ (ORDER BYなし) ---
┌─────────┬────┬────────┬────────────┬────────┬──────────────────────────┐
│ (index) │ id │ name │ department │ salary │ hired_at │
├─────────┼────┼────────┼────────────┼────────┼──────────────────────────┤
│ 0 │ 1 │ '佐藤' │ '営業' │ 400000 │ 2019-04-01T00:00:00.000Z │
│ 1 │ 2 │ '鈴木' │ '営業' │ 350000 │ 2021-04-01T00:00:00.000Z │
│ 2 │ 3 │ '田中' │ '開発' │ 500000 │ 2019-04-01T00:00:00.000Z │
│ 3 │ 4 │ '高橋' │ '開発' │ 500000 │ 2022-04-01T00:00:00.000Z │
│ 4 │ 5 │ '伊藤' │ '人事' │ null │ 2023-10-01T00:00:00.000Z │
│ 5 │ 6 │ '渡辺' │ '人事' │ 420000 │ null │
│ 6 │ 7 │ '山本' │ '開発' │ 480000 │ 2018-04-01T00:00:00.000Z │
└─────────┴────┴────────┴────────────┴────────┴──────────────────────────┘
========== 日付順ソート ==========
[1-A] 入社日が古い順 (hired_at ASC)
┌─────────┬────────┬──────────────────────────┐
│ (index) │ name │ hired_at │
├─────────┼────────┼──────────────────────────┤
│ 0 │ '山本' │ 2018-04-01T00:00:00.000Z │
│ 1 │ '田中' │ 2019-04-01T00:00:00.000Z │
│ 2 │ '佐藤' │ 2019-04-01T00:00:00.000Z │
│ 3 │ '鈴木' │ 2021-04-01T00:00:00.000Z │
│ 4 │ '高橋' │ 2022-04-01T00:00:00.000Z │
│ 5 │ '伊藤' │ 2023-10-01T00:00:00.000Z │
│ 6 │ '渡辺' │ null │
└─────────┴────────┴──────────────────────────┘
[1-B] 入社日が新しい順 (DESC NULLS LAST)
┌─────────┬────────┬──────────────────────────┐
│ (index) │ name │ hired_at │
├─────────┼────────┼──────────────────────────┤
│ 0 │ '伊藤' │ 2023-10-01T00:00:00.000Z │
│ 1 │ '高橋' │ 2022-04-01T00:00:00.000Z │
│ 2 │ '鈴木' │ 2021-04-01T00:00:00.000Z │
│ 3 │ '田中' │ 2019-04-01T00:00:00.000Z │
│ 4 │ '佐藤' │ 2019-04-01T00:00:00.000Z │
│ 5 │ '山本' │ 2018-04-01T00:00:00.000Z │
│ 6 │ '渡辺' │ null │
└─────────┴────────┴──────────────────────────┘
[1-C] 2019〜2022年に入社した人を古い順
┌─────────┬────────┬──────────────────────────┐
│ (index) │ name │ hired_at │
├─────────┼────────┼──────────────────────────┤
│ 0 │ '佐藤' │ 2019-04-01T00:00:00.000Z │
│ 1 │ '田中' │ 2019-04-01T00:00:00.000Z │
│ 2 │ '鈴木' │ 2021-04-01T00:00:00.000Z │
│ 3 │ '高橋' │ 2022-04-01T00:00:00.000Z │
└─────────┴────────┴──────────────────────────┘
========== 複数列ソート ==========
[2-A] 部署昇順 → 給与降順
┌─────────┬────────┬────────────┬────────┐
│ (index) │ name │ department │ salary │
├─────────┼────────┼────────────┼────────┤
│ 0 │ '伊藤' │ '人事' │ null │
│ 1 │ '渡辺' │ '人事' │ 420000 │
│ 2 │ '佐藤' │ '営業' │ 400000 │
│ 3 │ '鈴木' │ '営業' │ 350000 │
│ 4 │ '高橋' │ '開発' │ 500000 │
│ 5 │ '田中' │ '開発' │ 500000 │
│ 6 │ '山本' │ '開発' │ 480000 │
└─────────┴────────┴────────────┴────────┘
[2-B] 部署→給与降順→入社日昇順 (3列で同値タイを解消)
┌─────────┬────────┬────────────┬────────┬──────────────────────────┐
│ (index) │ name │ department │ salary │ hired_at │
├─────────┼────────┼────────────┼────────┼──────────────────────────┤
│ 0 │ '伊藤' │ '人事' │ null │ 2023-10-01T00:00:00.000Z │
│ 1 │ '渡辺' │ '人事' │ 420000 │ null │
│ 2 │ '佐藤' │ '営業' │ 400000 │ 2019-04-01T00:00:00.000Z │
│ 3 │ '鈴木' │ '営業' │ 350000 │ 2021-04-01T00:00:00.000Z │
│ 4 │ '田中' │ '開発' │ 500000 │ 2019-04-01T00:00:00.000Z │
│ 5 │ '高橋' │ '開発' │ 500000 │ 2022-04-01T00:00:00.000Z │
│ 6 │ '山本' │ '開発' │ 480000 │ 2018-04-01T00:00:00.000Z │
└─────────┴────────┴────────────┴────────┴──────────────────────────┘
[2-C] 入社日が新しい順 → 同日なら名前順
┌─────────┬────────┬──────────────────────────┐
│ (index) │ name │ hired_at │
├─────────┼────────┼──────────────────────────┤
│ 0 │ '伊藤' │ 2023-10-01T00:00:00.000Z │
│ 1 │ '高橋' │ 2022-04-01T00:00:00.000Z │
│ 2 │ '鈴木' │ 2021-04-01T00:00:00.000Z │
│ 3 │ '佐藤' │ 2019-04-01T00:00:00.000Z │
│ 4 │ '田中' │ 2019-04-01T00:00:00.000Z │
│ 5 │ '山本' │ 2018-04-01T00:00:00.000Z │
│ 6 │ '渡辺' │ null │
└─────────┴────────┴──────────────────────────┘
[2-D] 部署→入社日(古い順)→id (同部署で同入社日のタイ解消)
┌─────────┬────┬────────┬────────────┬──────────────────────────┐
│ (index) │ id │ name │ department │ hired_at │
├─────────┼────┼────────┼────────────┼──────────────────────────┤
│ 0 │ 5 │ '伊藤' │ '人事' │ 2023-10-01T00:00:00.000Z │
│ 1 │ 6 │ '渡辺' │ '人事' │ null │
│ 2 │ 1 │ '佐藤' │ '営業' │ 2019-04-01T00:00:00.000Z │
│ 3 │ 2 │ '鈴木' │ '営業' │ 2021-04-01T00:00:00.000Z │
│ 4 │ 7 │ '山本' │ '開発' │ 2018-04-01T00:00:00.000Z │
│ 5 │ 3 │ '田中' │ '開発' │ 2019-04-01T00:00:00.000Z │
│ 6 │ 4 │ '高橋' │ '開発' │ 2022-04-01T00:00:00.000Z │
└─────────┴────┴────────┴────────────┴──────────────────────────┘
========== 応用パターン ==========
[3-A] 入社年度昇順 → 同年度内で給与降順 (年だけ取り出してソート)
┌─────────┬────────┬──────────────────────────┬────────┬────────────┐
│ (index) │ name │ hired_at │ salary │ hired_year │
├─────────┼────────┼──────────────────────────┼────────┼────────────┤
│ 0 │ '山本' │ 2018-04-01T00:00:00.000Z │ 480000 │ 2018 │
│ 1 │ '田中' │ 2019-04-01T00:00:00.000Z │ 500000 │ 2019 │
│ 2 │ '佐藤' │ 2019-04-01T00:00:00.000Z │ 400000 │ 2019 │
│ 3 │ '鈴木' │ 2021-04-01T00:00:00.000Z │ 350000 │ 2021 │
│ 4 │ '高橋' │ 2022-04-01T00:00:00.000Z │ 500000 │ 2022 │
│ 5 │ '伊藤' │ 2023-10-01T00:00:00.000Z │ null │ 2023 │
└─────────┴────────┴──────────────────────────┴────────┴────────────┘
[3-B] 部署ごとに新しい入社順
┌─────────┬────────────┬────────┬──────────────────────────┐
│ (index) │ department │ name │ hired_at │
├─────────┼────────────┼────────┼──────────────────────────┤
│ 0 │ '人事' │ '伊藤' │ 2023-10-01T00:00:00.000Z │
│ 1 │ '人事' │ '渡辺' │ null │
│ 2 │ '営業' │ '鈴木' │ 2021-04-01T00:00:00.000Z │
│ 3 │ '営業' │ '佐藤' │ 2019-04-01T00:00:00.000Z │
│ 4 │ '開発' │ '高橋' │ 2022-04-01T00:00:00.000Z │
│ 5 │ '開発' │ '田中' │ 2019-04-01T00:00:00.000Z │
│ 6 │ '開発' │ '山本' │ 2018-04-01T00:00:00.000Z │
└─────────┴────────────┴────────┴──────────────────────────┘
[3-C] 古参トップ3 (ORDER BY hired_at ASC LIMIT 3)
┌─────────┬────────┬──────────────────────────┐
│ (index) │ name │ hired_at │
├─────────┼────────┼──────────────────────────┤
│ 0 │ '山本' │ 2018-04-01T00:00:00.000Z │
│ 1 │ '佐藤' │ 2019-04-01T00:00:00.000Z │
│ 2 │ '田中' │ 2019-04-01T00:00:00.000Z │
└─────────┴────────┴──────────────────────────┘ORDER BY 句をつけないと順序保証されない
ORDER BY 句をつけないSELECT文では、結果順の並び順は、実質的に「ランダム」である。
行数を指定して取得する
sql
SELECT 列名... FROM テーブル名
ORDER BY 列名...
OFFSET 先頭から除外する行数 ROWS
FETCH NEXT 取得行数 ROWS ONLY例
ts
// OFFSET / FETCH NEXT (= ページング) を学ぶための実験用テーブル
// 学生10人をスコア降順に並べた状態で、ページごとに切り出す感覚を確認する
async function createStudentsTable() {
return sql.unsafe(`
CREATE TABLE IF NOT EXISTS STUDENTS (
name VARCHAR(20) PRIMARY KEY,
score INTEGER
);
`)
}
async function seedStudents() {
// スコアの高い順に並べると 95→50 まで5刻み程度で10人
await sql`INSERT INTO STUDENTS VALUES ('佐藤', 95)`
await sql`INSERT INTO STUDENTS VALUES ('鈴木', 88)`
await sql`INSERT INTO STUDENTS VALUES ('田中', 82)`
await sql`INSERT INTO STUDENTS VALUES ('高橋', 78)`
await sql`INSERT INTO STUDENTS VALUES ('山田', 75)`
await sql`INSERT INTO STUDENTS VALUES ('伊藤', 70)`
await sql`INSERT INTO STUDENTS VALUES ('渡辺', 65)`
await sql`INSERT INTO STUDENTS VALUES ('中村', 60)`
await sql`INSERT INTO STUDENTS VALUES ('小林', 55)`
await sql`INSERT INTO STUDENTS VALUES ('加藤', 50)`
}
async function fetchMain() {
try {
await createStudentsTable()
const [{ count }] = await sql<{ count: number }[]>`
SELECT COUNT(*)::int AS count FROM STUDENTS
`
if (count === 0) await seedStudents()
// まずスコア降順の全件を確認 (ページングは「スコア順に上から3件ずつ」のイメージ)
const all = await sql`SELECT name, score FROM STUDENTS ORDER BY score DESC`
console.log('--- 全10件 (score DESC) ---')
console.table(all)
// ============================================================
// [1] 基本構文: OFFSET n ROWS FETCH NEXT m ROWS ONLY
// ============================================================
// OFFSET n ROWS 先頭から n 行スキップする
// FETCH NEXT m ROWS ONLY そこから m 行だけ取得する
//
// ★必ず ORDER BY とセットで使う★
// ORDER BY が無いと「どの順で並べるか」が決まらないので、
// 何度実行しても同じ結果になる保証が無くなる(=ページングの意味がない)
// [1-1] 1ページ目 (上位3名): スキップ0、3件取得
// → 佐藤(95), 鈴木(88), 田中(82)
const page1 = await sql`
SELECT name, score FROM STUDENTS
ORDER BY score DESC
OFFSET 0 ROWS
FETCH NEXT 3 ROWS ONLY
`
console.log('\n[1-1] 1ページ目 (OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY)')
console.table(page1)
// [1-2] 2ページ目 (4〜6位): 3件スキップして3件取得
// → 高橋(78), 山田(75), 伊藤(70)
const page2 = await sql`
SELECT name, score FROM STUDENTS
ORDER BY score DESC
OFFSET 3 ROWS
FETCH NEXT 3 ROWS ONLY
`
console.log('\n[1-2] 2ページ目 (OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY)')
console.table(page2)
// [1-3] 3ページ目 (7〜9位): 6件スキップして3件取得
// → 渡辺(65), 中村(60), 小林(55)
const page3 = await sql`
SELECT name, score FROM STUDENTS
ORDER BY score DESC
OFFSET 6 ROWS
FETCH NEXT 3 ROWS ONLY
`
console.log('\n[1-3] 3ページ目 (OFFSET 6 ROWS FETCH NEXT 3 ROWS ONLY)')
console.table(page3)
// [1-4] 4ページ目 (10位のみ): 9件スキップして3件取得しようとするが、残り1件
// FETCH NEXT は「最大m件」の意味なので、足りない分は単に少なく返るだけ(エラーではない)
// → 加藤(50) の1件
const page4 = await sql`
SELECT name, score FROM STUDENTS
ORDER BY score DESC
OFFSET 9 ROWS
FETCH NEXT 3 ROWS ONLY
`
console.log('\n[1-4] 4ページ目 (残り1件しか無いケース)')
console.table(page4)
// ============================================================
// [2] 範囲外を指定したらどうなる?
// ============================================================
// [2-1] OFFSET が全件数を超える → 0件 (エラーではない)
const beyond = await sql`
SELECT name, score FROM STUDENTS
ORDER BY score DESC
OFFSET 100 ROWS
FETCH NEXT 3 ROWS ONLY
`
console.log('\n[2-1] OFFSET 100 → スキップ過多で0件')
console.table(beyond)
// ============================================================
// [3] OFFSET / FETCH を片方だけ使う
// ============================================================
// [3-1] FETCH NEXT だけ (OFFSET省略 = 先頭から取得)
// 先頭3件と同じ意味
const onlyFetch = await sql`
SELECT name, score FROM STUDENTS
ORDER BY score DESC
FETCH NEXT 3 ROWS ONLY
`
console.log('\n[3-1] FETCH NEXT のみ (= OFFSET 0 と同じ。先頭3件)')
console.table(onlyFetch)
// [3-2] OFFSET だけ (FETCH省略 = 残り全部)
// 先頭7件をスキップ → 残り3件 (中村, 小林, 加藤)
const onlyOffset = await sql`
SELECT name, score FROM STUDENTS
ORDER BY score DESC
OFFSET 7 ROWS
`
console.log('\n[3-2] OFFSET のみ (= スキップ後の残り全部)')
console.table(onlyOffset)
// ============================================================
// [4] ROWS と ROW、NEXT と FIRST は同義 (好みで書ける)
// ============================================================
// [4-1] ROW (単数形) でも動く
// "OFFSET 1 ROW" のように1のときに自然な英語にしたいときくらいに使う
const singularRow = await sql`
SELECT name, score FROM STUDENTS
ORDER BY score DESC
OFFSET 1 ROW
FETCH NEXT 1 ROW ONLY
`
console.log('\n[4-1] ROW (単数形) でも合法 (2位だけ取得)')
console.table(singularRow)
// [4-2] FETCH FIRST も FETCH NEXT と同じ意味 (SQL標準)
const fetchFirst = await sql`
SELECT name, score FROM STUDENTS
ORDER BY score DESC
FETCH FIRST 2 ROWS ONLY
`
console.log('\n[4-2] FETCH FIRST 2 ROWS ONLY (= FETCH NEXT 2 ROWS ONLY)')
console.table(fetchFirst)
// ============================================================
// [5] PostgreSQL の方言: LIMIT/OFFSET (上と同じことが書ける)
// ============================================================
// 同じ機能をより短く書ける。MySQL/SQLite/PostgreSQL でよく見る書き方。
// ただしSQL標準ではないので、移植性が必要なら [1] の書き方を使う。
// [5-1] LIMIT m OFFSET n (FETCH NEXT/OFFSET と同じ結果)
const limitOffset = await sql`
SELECT name, score FROM STUDENTS
ORDER BY score DESC
LIMIT 3 OFFSET 3
`
console.log('\n[5-1] LIMIT 3 OFFSET 3 (= [1-2] と同じ結果)')
console.table(limitOffset)
// ============================================================
// [6] ORDER BY 無しの危険な例 (反面教師)
// ============================================================
// ORDER BYが無くても文法エラーにはならないが、
// 毎回違う行が返ってくる可能性があり、ページングとして役に立たない。
// 実運用では絶対 ORDER BY とセットで使うこと。
const noOrder = await sql`
SELECT name, score FROM STUDENTS
OFFSET 3 ROWS
FETCH NEXT 3 ROWS ONLY
`
console.log('\n[6] ORDER BY 無し (★非推奨。返る行は不定)')
console.table(noOrder)
} catch (e) {
console.error('❌ エラーが発生しました:', e)
} finally {
await sql.end()
}
}
fetchMain()実行結果
sh
--- 全10件 (score DESC) ---
┌─────────┬────────┬───────┐
│ (index) │ name │ score │
├─────────┼────────┼───────┤
│ 0 │ '佐藤' │ 95 │
│ 1 │ '鈴木' │ 88 │
│ 2 │ '田中' │ 82 │
│ 3 │ '高橋' │ 78 │
│ 4 │ '山田' │ 75 │
│ 5 │ '伊藤' │ 70 │
│ 6 │ '渡辺' │ 65 │
│ 7 │ '中村' │ 60 │
│ 8 │ '小林' │ 55 │
│ 9 │ '加藤' │ 50 │
└─────────┴────────┴───────┘
[1-1] 1ページ目 (OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY)
┌─────────┬────────┬───────┐
│ (index) │ name │ score │
├─────────┼────────┼───────┤
│ 0 │ '佐藤' │ 95 │
│ 1 │ '鈴木' │ 88 │
│ 2 │ '田中' │ 82 │
└─────────┴────────┴───────┘
[1-2] 2ページ目 (OFFSET 3 ROWS FETCH NEXT 3 ROWS ONLY)
┌─────────┬────────┬───────┐
│ (index) │ name │ score │
├─────────┼────────┼───────┤
│ 0 │ '高橋' │ 78 │
│ 1 │ '山田' │ 75 │
│ 2 │ '伊藤' │ 70 │
└─────────┴────────┴───────┘
[1-3] 3ページ目 (OFFSET 6 ROWS FETCH NEXT 3 ROWS ONLY)
┌─────────┬────────┬───────┐
│ (index) │ name │ score │
├─────────┼────────┼───────┤
│ 0 │ '渡辺' │ 65 │
│ 1 │ '中村' │ 60 │
│ 2 │ '小林' │ 55 │
└─────────┴────────┴───────┘
[1-4] 4ページ目 (残り1件しか無いケース)
┌─────────┬────────┬───────┐
│ (index) │ name │ score │
├─────────┼────────┼───────┤
│ 0 │ '加藤' │ 50 │
└─────────┴────────┴───────┘
[2-1] OFFSET 100 → スキップ過多で0件
┌─────────┐
│ (index) │
├─────────┤
└─────────┘
[3-1] FETCH NEXT のみ (= OFFSET 0 と同じ。先頭3件)
┌─────────┬────────┬───────┐
│ (index) │ name │ score │
├─────────┼────────┼───────┤
│ 0 │ '佐藤' │ 95 │
│ 1 │ '鈴木' │ 88 │
│ 2 │ '田中' │ 82 │
└─────────┴────────┴───────┘
[3-2] OFFSET のみ (= スキップ後の残り全部)
┌─────────┬────────┬───────┐
│ (index) │ name │ score │
├─────────┼────────┼───────┤
│ 0 │ '中村' │ 60 │
│ 1 │ '小林' │ 55 │
│ 2 │ '加藤' │ 50 │
└─────────┴────────┴───────┘
[4-1] ROW (単数形) でも合法 (2位だけ取得)
┌─────────┬────────┬───────┐
│ (index) │ name │ score │
├─────────┼────────┼───────┤
│ 0 │ '鈴木' │ 88 │
└─────────┴────────┴───────┘
[4-2] FETCH FIRST 2 ROWS ONLY (= FETCH NEXT 2 ROWS ONLY)
┌─────────┬────────┬───────┐
│ (index) │ name │ score │
├─────────┼────────┼───────┤
│ 0 │ '佐藤' │ 95 │
│ 1 │ '鈴木' │ 88 │
└─────────┴────────┴───────┘
[5-1] LIMIT 3 OFFSET 3 (= [1-2] と同じ結果)
┌─────────┬────────┬───────┐
│ (index) │ name │ score │
├─────────┼────────┼───────┤
│ 0 │ '高橋' │ 78 │
│ 1 │ '山田' │ 75 │
│ 2 │ '伊藤' │ 70 │
└─────────┴────────┴───────┘
[6] ORDER BY 無し (★非推奨。返る行は不定)
┌─────────┬────────┬───────┐
│ (index) │ name │ score │
├─────────┼────────┼───────┤
│ 0 │ '高橋' │ 78 │
│ 1 │ '山田' │ 75 │
│ 2 │ '伊藤' │ 70 │
└─────────┴────────┴───────┘UNION
sql
SELECT 文1
UNION (ALL)
SELECT 文2例
ts
// UNION / UNION ALL を学ぶための実験用テーブル
// 2つの店舗の販売データ。一部の商品は両店で扱われている
//
// STORE_A_SALES STORE_B_SALES
// ┌────────┬──────┐ ┌────────┬──────┐
// │ item │price │ │ item │price │
// ├────────┼──────┤ ├────────┼──────┤
// │ apple │ 100 │ ←──完全一致─→│ apple │ 100 │
// │ banana │ 80 │ │ banana │ 90 │ ←priceが違う
// │ cherry │ 200 │ │ orange │ 150 │ ←Aには無い商品
// └────────┴──────┘ └────────┴──────┘
async function createSalesTables() {
await sql.unsafe(`
CREATE TABLE IF NOT EXISTS STORE_A_SALES (
item VARCHAR(20),
price INTEGER
);
`)
await sql.unsafe(`
CREATE TABLE IF NOT EXISTS STORE_B_SALES (
item VARCHAR(20),
price INTEGER
);
`)
}
async function seedSales() {
await sql`INSERT INTO STORE_A_SALES VALUES ('apple', 100)`
await sql`INSERT INTO STORE_A_SALES VALUES ('banana', 80)`
await sql`INSERT INTO STORE_A_SALES VALUES ('cherry', 200)`
await sql`INSERT INTO STORE_B_SALES VALUES ('apple', 100)` // A店と完全一致
await sql`INSERT INTO STORE_B_SALES VALUES ('banana', 90)` // itemは同じだがpriceが違う
await sql`INSERT INTO STORE_B_SALES VALUES ('orange', 150)` // A店に無い商品
}
async function unionMain() {
try {
await createSalesTables()
const [{ countA }] = await sql<{ countA: number }[]>`
SELECT COUNT(*)::int AS "countA" FROM STORE_A_SALES
`
if (countA === 0) await seedSales()
const a = await sql`SELECT * FROM STORE_A_SALES`
const b = await sql`SELECT * FROM STORE_B_SALES`
console.log('--- A店 (STORE_A_SALES) ---')
console.table(a)
console.log('--- B店 (STORE_B_SALES) ---')
console.table(b)
// ============================================================
// [1] UNION : 2つの結果を縦に連結 + 重複行を1つにまとめる
// ============================================================
// 完全に同じ行(全列が一致)だけが「重複」とみなされる
// apple/100 はA店とB店で完全一致 → 1行にまとめられる
// banana は price が違うので別行扱い → 両方残る
// 合計: 5行
const unionResult = await sql`
SELECT item, price FROM STORE_A_SALES
UNION
SELECT item, price FROM STORE_B_SALES
`
console.log('\n[1] UNION (重複排除あり) → 5件')
console.table(unionResult)
// ============================================================
// [2] UNION ALL : 2つの結果を縦に連結 + 重複も全部残す
// ============================================================
// apple/100 が2行のまま残る
// 合計: 6行 (= A店3件 + B店3件)
// 重複排除をしない分、UNIONより速い
const unionAllResult = await sql`
SELECT item, price FROM STORE_A_SALES
UNION ALL
SELECT item, price FROM STORE_B_SALES
`
console.log('\n[2] UNION ALL (重複そのまま) → 6件')
console.table(unionAllResult)
// ============================================================
// [3] 「どの店舗のデータか」を識別する列を足してUNION ALL
// ============================================================
// 2つのSELECTのSELECT句に、固定の文字列を追加できる
// → どっちのテーブル由来かを保ったまま縦結合できる
const tagged = await sql`
SELECT 'A' AS store, item, price FROM STORE_A_SALES
UNION ALL
SELECT 'B' AS store, item, price FROM STORE_B_SALES
ORDER BY item, store
`
console.log('\n[3] 識別列を足してUNION ALL (どの店舗か追跡可能)')
console.table(tagged)
// ============================================================
// [4] UNION全体に対する ORDER BY は最後に1回だけ
// ============================================================
// 各SELECT文に個別にORDER BYは書けない
// ソートしたいときは UNION した結果全体を並べる
const sorted = await sql`
SELECT item, price FROM STORE_A_SALES
UNION
SELECT item, price FROM STORE_B_SALES
ORDER BY price DESC
`
console.log('\n[4] UNION結果を価格降順でソート')
console.table(sorted)
// ============================================================
// [5] WHERE と組み合わせた部分集合のUNION
// ============================================================
// 各SELECTでWHEREをかけてから合算する
// "100円以下のA店商品" + "150円以上のB店商品" を集める例
const filtered = await sql`
SELECT item, price FROM STORE_A_SALES WHERE price <= 100
UNION
SELECT item, price FROM STORE_B_SALES WHERE price >= 150
ORDER BY price
`
console.log('\n[5] WHEREで部分集合を作ってUNION')
console.table(filtered)
// ============================================================
// [6] 列名は最初のSELECTのものが使われる
// ============================================================
// 2番目のSELECTでASをつけても結果セットの列名は1番目のものになる
// 下では "name", "yen" が結果の列名になる
const renamed = await sql`
SELECT item AS name, price AS yen FROM STORE_A_SALES
UNION
SELECT item AS xxx, price AS yyy FROM STORE_B_SALES
`
console.log('\n[6] 結果の列名は最初のSELECTのもの (name, yen)')
console.table(renamed)
// ============================================================
// [7] UNION の制約: 列の数・順番が同じ必要がある
// ============================================================
// ・列数が違うとエラー
// ・列の対応は「位置」で決まる (列名は無関係)
// 下の例ではあえて B店の列順を逆にしている
// 結果として A店の "item" と B店の "price" が同じ列に並んでしまう
// → 文法エラーにはならないが、データはぐちゃぐちゃになる注意例
// (PostgreSQLでは型が違うと暗黙変換できない場合エラーになる)
// ここでは型が共に互換性のある場合の挙動を見せるための参考
//
// 実際は型不一致でエラーになる可能性が高いのでコメントアウト
//
// const broken = await sql`
// SELECT item, price FROM STORE_A_SALES
// UNION
// SELECT price, item FROM STORE_B_SALES -- ← 列順が違う!
// `
// ============================================================
// [8] UNION vs UNION ALL の使い分け
// ============================================================
// ・重複を本当に消したい(集合演算的な使い方) → UNION
// ・重複が無いと分かっている / 重複も含めて欲しい → UNION ALL
// ・速度重視 → UNION ALL (重複排除のソートが不要)
// ・「どっちでもいいけど重複を排除したい」と思ったら、まずDISTINCTせずに
// UNION ALLしてから集計を見る、という選択もある
} catch (e) {
console.error('❌ エラーが発生しました:', e)
} finally {
await sql.end()
}
}
unionMain()実行結果
sh
┌─────────┬──────────┬───────┐
│ (index) │ item │ price │
├─────────┼──────────┼───────┤
│ 0 │ 'apple' │ 100 │
│ 1 │ 'banana' │ 80 │
│ 2 │ 'cherry' │ 200 │
└─────────┴──────────┴───────┘
--- B店 (STORE_B_SALES) ---
┌─────────┬──────────┬───────┐
│ (index) │ item │ price │
├─────────┼──────────┼───────┤
│ 0 │ 'apple' │ 100 │
│ 1 │ 'banana' │ 90 │
│ 2 │ 'orange' │ 150 │
└─────────┴──────────┴───────┘
[1] UNION (重複排除あり) → 5件
┌─────────┬──────────┬───────┐
│ (index) │ item │ price │
├─────────┼──────────┼───────┤
│ 0 │ 'cherry' │ 200 │
│ 1 │ 'banana' │ 80 │
│ 2 │ 'banana' │ 90 │
│ 3 │ 'apple' │ 100 │
│ 4 │ 'orange' │ 150 │
└─────────┴──────────┴───────┘
[2] UNION ALL (重複そのまま) → 6件
┌─────────┬──────────┬───────┐
│ (index) │ item │ price │
├─────────┼──────────┼───────┤
│ 0 │ 'apple' │ 100 │
│ 1 │ 'banana' │ 80 │
│ 2 │ 'cherry' │ 200 │
│ 3 │ 'apple' │ 100 │
│ 4 │ 'banana' │ 90 │
│ 5 │ 'orange' │ 150 │
└─────────┴──────────┴───────┘
[3] 識別列を足してUNION ALL (どの店舗か追跡可能)
┌─────────┬───────┬──────────┬───────┐
│ (index) │ store │ item │ price │
├─────────┼───────┼──────────┼───────┤
│ 0 │ 'A' │ 'apple' │ 100 │
│ 1 │ 'B' │ 'apple' │ 100 │
│ 2 │ 'A' │ 'banana' │ 80 │
│ 3 │ 'B' │ 'banana' │ 90 │
│ 4 │ 'A' │ 'cherry' │ 200 │
│ 5 │ 'B' │ 'orange' │ 150 │
└─────────┴───────┴──────────┴───────┘
[4] UNION結果を価格降順でソート
┌─────────┬──────────┬───────┐
│ (index) │ item │ price │
├─────────┼──────────┼───────┤
│ 0 │ 'cherry' │ 200 │
│ 1 │ 'orange' │ 150 │
│ 2 │ 'apple' │ 100 │
│ 3 │ 'banana' │ 90 │
│ 4 │ 'banana' │ 80 │
└─────────┴──────────┴───────┘
[5] WHEREで部分集合を作ってUNION
┌─────────┬──────────┬───────┐
│ (index) │ item │ price │
├─────────┼──────────┼───────┤
│ 0 │ 'banana' │ 80 │
│ 1 │ 'apple' │ 100 │
│ 2 │ 'orange' │ 150 │
└─────────┴──────────┴───────┘
[6] 結果の列名は最初のSELECTのもの (name, yen)
┌─────────┬──────────┬─────┐
│ (index) │ name │ yen │
├─────────┼──────────┼─────┤
│ 0 │ 'cherry' │ 200 │
│ 1 │ 'banana' │ 80 │
│ 2 │ 'banana' │ 90 │
│ 3 │ 'apple' │ 100 │
│ 4 │ 'orange' │ 150 │
└─────────┴──────────┴─────┘EXCEPT/MINUS
sql
SELECT 文1
EXCEPT (ALL)
SELECT 文2例
ts
// EXCEPT / EXCEPT ALL を学ぶための実験用テーブル
// A店には apple/100 が "2回" 登録されている (EXCEPT ALLの効果を見るため)
//
// STORE_A_SALES STORE_B_SALES
// ┌────────┬──────┐ ┌────────┬──────┐
// │ item │price │ │ item │price │
// ├────────┼──────┤ ├────────┼──────┤
// │ apple │ 100 │ ←──完全一致──→ │ apple │ 100 │
// │ apple │ 100 │ (Aに2行ある!) │ banana │ 90 │
// │ banana │ 80 │ │ orange │ 150 │
// │ cherry │ 200 │ └────────┴──────┘
// └────────┴──────┘
async function dropSalesTables() {
// 前回の例(UNIONなど)で残ったデータと混ざらないよう、毎回テーブルごと作り直す
await sql.unsafe(`DROP TABLE IF EXISTS STORE_A_SALES;`)
await sql.unsafe(`DROP TABLE IF EXISTS STORE_B_SALES;`)
}
async function createSalesTables() {
await sql.unsafe(`
CREATE TABLE STORE_A_SALES (
item VARCHAR(20),
price INTEGER
);
`)
await sql.unsafe(`
CREATE TABLE STORE_B_SALES (
item VARCHAR(20),
price INTEGER
);
`)
}
async function seedSales() {
// A店: apple/100 が2回 (EXCEPT ALLの個数引き算を見るため)
await sql`INSERT INTO STORE_A_SALES VALUES ('apple', 100)`
await sql`INSERT INTO STORE_A_SALES VALUES ('apple', 100)`
await sql`INSERT INTO STORE_A_SALES VALUES ('banana', 80)`
await sql`INSERT INTO STORE_A_SALES VALUES ('cherry', 200)`
await sql`INSERT INTO STORE_B_SALES VALUES ('apple', 100)`
await sql`INSERT INTO STORE_B_SALES VALUES ('banana', 90)`
await sql`INSERT INTO STORE_B_SALES VALUES ('orange', 150)`
}
async function exceptMain() {
try {
await dropSalesTables()
await createSalesTables()
await seedSales()
const a = await sql`SELECT * FROM STORE_A_SALES`
const b = await sql`SELECT * FROM STORE_B_SALES`
console.log('--- A店 (4件: apple/100が2回ある) ---')
console.table(a)
console.log('--- B店 (3件) ---')
console.table(b)
// ============================================================
// [1] EXCEPT : Aの結果から、Bの結果に含まれる行を取り除く + 重複排除
// ============================================================
// "AにあるけどBには無いもの" を取得する
// 完全一致(全列が一致)した行が「Bにある」とみなされる
//
// apple/100 → Bにもある → 除外
// banana/80 → Bには無い → 残る (B店のbananaは90で別行扱い)
// cherry/200 → Bには無い → 残る
// ※ Aに2回あった apple/100 も結果ではまとめて0件 (重複排除込み)
// → 結果: 2件 (banana/80, cherry/200)
const aExceptB = await sql`
SELECT item, price FROM STORE_A_SALES
EXCEPT
SELECT item, price FROM STORE_B_SALES
`
console.log('\n[1] A EXCEPT B (Aだけにある、重複排除あり) → 2件')
console.table(aExceptB)
// ============================================================
// [2] EXCEPT ALL : 個数を考慮した引き算 + 重複は残せる
// ============================================================
// 各行について「Aの登場回数 - Bの登場回数」を計算し、0未満は0扱い。
//
// apple/100 : A 2回 - B 1回 = 1回残る
// banana/80 : A 1回 - B 0回 = 1回残る
// cherry/200 : A 1回 - B 0回 = 1回残る
// → 結果: 3件
const aExceptAllB = await sql`
SELECT item, price FROM STORE_A_SALES
EXCEPT ALL
SELECT item, price FROM STORE_B_SALES
`
console.log('\n[2] A EXCEPT ALL B (個数の引き算) → 3件')
console.table(aExceptAllB)
// ============================================================
// [3] EXCEPT は順序が重要 (UNIONと違って非対称)
// ============================================================
// B EXCEPT A は "BにあるけどAには無いもの"
// apple/100 → Aにもある → 除外
// banana/90 → Aには無い → 残る (A店のbananaは80)
// orange/150 → Aには無い → 残る
// → 結果: 2件 (banana/90, orange/150)
//
// ★[1]とは結果が完全に違う★ → "AとBを入れ替えると別の結果"
const bExceptA = await sql`
SELECT item, price FROM STORE_B_SALES
EXCEPT
SELECT item, price FROM STORE_A_SALES
`
console.log('\n[3] B EXCEPT A (Bだけにある) → 2件 / [1]とは結果が違う')
console.table(bExceptA)
// ============================================================
// [4] WHERE と組み合わせて部分集合の差を取る
// ============================================================
// "A店の100円以下の商品" から "B店で扱っている商品" を引く
// apple/100 → Bにあるので除外
// banana/80 → Bには banana/90 しか無い (price違うので別行) → 残る
// → 結果: 1件 (banana/80)
const filtered = await sql`
SELECT item, price FROM STORE_A_SALES WHERE price <= 100
EXCEPT
SELECT item, price FROM STORE_B_SALES
`
console.log('\n[4] WHERE + EXCEPT (A店の100円以下のうちBに無いもの)')
console.table(filtered)
// ============================================================
// [5] 列を絞ってEXCEPT - "Aだけが扱う商品名"を抽出
// ============================================================
// item列だけで比較すると、価格の違いは無視される
// apple → Bにもある → 除外
// banana → Bにもある → 除外 (priceが違っても item は一致)
// cherry → Bには無い → 残る
// → 結果: 1件 (cherry)
const onlyAItems = await sql`
SELECT item FROM STORE_A_SALES
EXCEPT
SELECT item FROM STORE_B_SALES
`
console.log('\n[5] item列だけでEXCEPT (Aだけが扱う商品名)')
console.table(onlyAItems)
// ============================================================
// [6] ORDER BY は EXCEPT 全体に対して最後に1回だけ
// ============================================================
const sorted = await sql`
SELECT item, price FROM STORE_A_SALES
EXCEPT
SELECT item, price FROM STORE_B_SALES
ORDER BY price DESC
`
console.log('\n[6] EXCEPT結果を価格降順に')
console.table(sorted)
// ============================================================
// [7] 集合演算の対比 (UNION/EXCEPT/INTERSECT)
// ============================================================
// A UNION B : AとB のどちらかにある行 (和集合)
// A INTERSECT B : AとBの両方にある行 (積集合)
// A EXCEPT B : Aにあって Bにない行 (差集合)
//
// いずれも EXCEPT ALL と同様、ALLをつけると重複個数を保つ
//
// おまけ: INTERSECT (両店で扱う商品)
// apple/100 のみが両方にある (priceまで完全一致)
const intersectExtra = await sql`
SELECT item, price FROM STORE_A_SALES
INTERSECT
SELECT item, price FROM STORE_B_SALES
`
console.log('\n[7] (おまけ) INTERSECT - 両店に共通する行')
console.table(intersectExtra)
} catch (e) {
console.error('❌ エラーが発生しました:', e)
} finally {
await sql.end()
}
}
exceptMain()実行結果
sh
--- A店 (4件: apple/100が2回ある) ---
┌─────────┬──────────┬───────┐
│ (index) │ item │ price │
├─────────┼──────────┼───────┤
│ 0 │ 'apple' │ 100 │
│ 1 │ 'apple' │ 100 │
│ 2 │ 'banana' │ 80 │
│ 3 │ 'cherry' │ 200 │
└─────────┴──────────┴───────┘
--- B店 (3件) ---
┌─────────┬──────────┬───────┐
│ (index) │ item │ price │
├─────────┼──────────┼───────┤
│ 0 │ 'apple' │ 100 │
│ 1 │ 'banana' │ 90 │
│ 2 │ 'orange' │ 150 │
└─────────┴──────────┴───────┘
[1] A EXCEPT B (Aだけにある、重複排除あり) → 2件
┌─────────┬──────────┬───────┐
│ (index) │ item │ price │
├─────────┼──────────┼───────┤
│ 0 │ 'banana' │ 80 │
│ 1 │ 'cherry' │ 200 │
└─────────┴──────────┴───────┘
[2] A EXCEPT ALL B (個数の引き算) → 3件
┌─────────┬──────────┬───────┐
│ (index) │ item │ price │
├─────────┼──────────┼───────┤
│ 0 │ 'banana' │ 80 │
│ 1 │ 'cherry' │ 200 │
│ 2 │ 'apple' │ 100 │
└─────────┴──────────┴───────┘
[3] B EXCEPT A (Bだけにある) → 2件 / [1]とは結果が違う
┌─────────┬──────────┬───────┐
│ (index) │ item │ price │
├─────────┼──────────┼───────┤
│ 0 │ 'orange' │ 150 │
│ 1 │ 'banana' │ 90 │
└─────────┴──────────┴───────┘
[4] WHERE + EXCEPT (A店の100円以下のうちBに無いもの)
┌─────────┬──────────┬───────┐
│ (index) │ item │ price │
├─────────┼──────────┼───────┤
│ 0 │ 'banana' │ 80 │
└─────────┴──────────┴───────┘
[5] item列だけでEXCEPT (Aだけが扱う商品名)
┌─────────┬──────────┐
│ (index) │ item │
├─────────┼──────────┤
│ 0 │ 'cherry' │
└─────────┴──────────┘
[6] EXCEPT結果を価格降順に
┌─────────┬──────────┬───────┐
│ (index) │ item │ price │
├─────────┼──────────┼───────┤
│ 0 │ 'cherry' │ 200 │
│ 1 │ 'banana' │ 80 │
└─────────┴──────────┴───────┘
[7] (おまけ) INTERSECT - 両店に共通する行
┌─────────┬─────────┬───────┐
│ (index) │ item │ price │
├─────────┼─────────┼───────┤
│ 0 │ 'apple' │ 100 │
└─────────┴─────────┴───────┘問題1
| 列名 | データ型 | 備考 |
|---|---|---|
| 日付 | DATE | |
| 注文番号 | INTEGER | 注文順に振られた連番(主キー) |
| 注文枝番 | INTEGER | 注文ごとの明細番号(主キー) |
| 商品名 | VARCHAR(50) | |
| 分類 | CHAR(1) | 1:ドリンク 2:フード 3:その他 |
| サイズ | CHAR(1) | S、M、L(ドリンクのみ)、X(ドリンク以外) |
| 単価 | INTEGER | |
| 数量 | INTEGER | |
| 注文金額 | INTEGER |
sh
┌─────────┬──────────────────────────┬──────────┬───────────┬────────────────┬──────────┬───────────┬────────────┬──────────┬────────┐
│ (index) │ order_date │ order_no │ order_seq │ item_name │ category │ size_code │ unit_price │ quantity │ amount │
├─────────┼──────────────────────────┼──────────┼───────────┼────────────────┼──────────┼───────────┼────────────┼──────────┼────────┤
│ 0 │ 2024-01-05T00:00:00.000Z │ 1 │ 1 │ 'コーヒー' │ '1' │ 'M' │ 400 │ 1 │ 400 │
│ 1 │ 2024-01-05T00:00:00.000Z │ 1 │ 2 │ 'サンドイッチ' │ '2' │ 'X' │ 500 │ 1 │ 500 │
│ 2 │ 2024-01-05T00:00:00.000Z │ 1 │ 3 │ 'マグカップ' │ '3' │ 'X' │ 1500 │ 1 │ 1500 │
│ 3 │ 2024-01-10T00:00:00.000Z │ 2 │ 1 │ 'コーヒー' │ '1' │ 'L' │ 500 │ 2 │ 1000 │
│ 4 │ 2024-01-10T00:00:00.000Z │ 2 │ 2 │ 'クッキー' │ '2' │ 'X' │ 200 │ 3 │ 600 │
│ 5 │ 2024-01-10T00:00:00.000Z │ 2 │ 3 │ 'タンブラー' │ '3' │ 'X' │ 2000 │ 2 │ 4000 │
│ 6 │ 2024-01-15T00:00:00.000Z │ 3 │ 1 │ '紅茶' │ '1' │ 'S' │ 300 │ 1 │ 300 │
│ 7 │ 2024-01-15T00:00:00.000Z │ 3 │ 2 │ 'ケーキ' │ '2' │ 'X' │ 450 │ 1 │ 450 │
│ 8 │ 2024-01-15T00:00:00.000Z │ 3 │ 3 │ 'マグカップ' │ '3' │ 'X' │ 1500 │ 3 │ 4500 │
│ 9 │ 2023-12-30T00:00:00.000Z │ 4 │ 1 │ 'ジュース' │ '1' │ 'M' │ 350 │ 1 │ 350 │
│ 10 │ 2023-12-30T00:00:00.000Z │ 4 │ 2 │ 'コーヒー' │ '1' │ 'S' │ 300 │ 2 │ 600 │
│ 11 │ 2024-02-01T00:00:00.000Z │ 5 │ 1 │ 'コーヒー' │ '1' │ 'M' │ 400 │ 1 │ 400 │
└─────────┴──────────────────────────┴──────────┴───────────┴────────────────┴──────────┴───────────┴────────────┴──────────┴────────┘1.注文順かつその明細順に、すべての注文データを取得する。
ts
const result = await sql`SELECT * FROM ORDERS ORDER BY order_no ASC, order_seq ASC`実行結果
sh
┌─────────┬──────────────────────────┬──────────┬───────────┬────────────────┬──────────┬───────────┬────────────┬──────────┬────────┐
│ (index) │ order_date │ order_no │ order_seq │ item_name │ category │ size_code │ unit_price │ quantity │ amount │
├─────────┼──────────────────────────┼──────────┼───────────┼────────────────┼──────────┼───────────┼────────────┼──────────┼────────┤
│ 0 │ 2024-01-05T00:00:00.000Z │ 1 │ 1 │ 'コーヒー' │ '1' │ 'M' │ 400 │ 1 │ 400 │
│ 1 │ 2024-01-05T00:00:00.000Z │ 1 │ 2 │ 'サンドイッチ' │ '2' │ 'X' │ 500 │ 1 │ 500 │
│ 2 │ 2024-01-05T00:00:00.000Z │ 1 │ 3 │ 'マグカップ' │ '3' │ 'X' │ 1500 │ 1 │ 1500 │
│ 3 │ 2024-01-10T00:00:00.000Z │ 2 │ 1 │ 'コーヒー' │ '1' │ 'L' │ 500 │ 2 │ 1000 │
│ 4 │ 2024-01-10T00:00:00.000Z │ 2 │ 2 │ 'クッキー' │ '2' │ 'X' │ 200 │ 3 │ 600 │
│ 5 │ 2024-01-10T00:00:00.000Z │ 2 │ 3 │ 'タンブラー' │ '3' │ 'X' │ 2000 │ 2 │ 4000 │
│ 6 │ 2024-01-15T00:00:00.000Z │ 3 │ 1 │ '紅茶' │ '1' │ 'S' │ 300 │ 1 │ 300 │
│ 7 │ 2024-01-15T00:00:00.000Z │ 3 │ 2 │ 'ケーキ' │ '2' │ 'X' │ 450 │ 1 │ 450 │
│ 8 │ 2024-01-15T00:00:00.000Z │ 3 │ 3 │ 'マグカップ' │ '3' │ 'X' │ 1500 │ 3 │ 4500 │
│ 9 │ 2023-12-30T00:00:00.000Z │ 4 │ 1 │ 'ジュース' │ '1' │ 'M' │ 350 │ 1 │ 350 │
│ 10 │ 2023-12-30T00:00:00.000Z │ 4 │ 2 │ 'コーヒー' │ '1' │ 'S' │ 300 │ 2 │ 600 │
│ 11 │ 2024-02-01T00:00:00.000Z │ 5 │ 1 │ 'コーヒー' │ '1' │ 'M' │ 400 │ 1 │ 400 │
└─────────┴──────────────────────────┴──────────┴───────────┴────────────────┴──────────┴───────────┴────────────┴──────────┴────────┘2.2024年1月に注文のあった商品名の一覧を商品名順に取得する。
ts
const result =
await sql`SELECT DISTINCT item_name FROM ORDERS WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31' ORDER BY item_name`実行結果
sh
┌─────────┬────────────────┐
│ (index) │ item_name │
├─────────┼────────────────┤
│ 0 │ 'クッキー' │
│ 1 │ 'ケーキ' │
│ 2 │ 'コーヒー' │
│ 3 │ 'サンドイッチ' │
│ 4 │ 'タンブラー' │
│ 5 │ 'マグカップ' │
│ 6 │ '紅茶' │
└─────────┴────────────────┘3.ドリンクの商品について、注文番号、注文枝番、注文金額を取得する。ただし、注文金額の低い方から2~4番目に該当する注文だけを対象とする。
ts
const result = await sql`
SELECT order_no, order_seq, amount FROM ORDERS
WHERE category = '1'
ORDER BY amount ASC
OFFSET 1 ROWS
FETCH NEXT 3 ROWS ONLY
`実行結果
sh
┌─────────┬──────────┬───────────┬────────┐
│ (index) │ order_no │ order_seq │ amount │
├─────────┼──────────┼───────────┼────────┤
│ 0 │ 4 │ 1 │ 350 │
│ 1 │ 1 │ 1 │ 400 │
│ 2 │ 5 │ 1 │ 400 │
└─────────┴──────────┴───────────┴────────┘4.その他の商品について、2つ以上同時に購入された商品を取得し、日付、商品名、単価、数量、注文金額を購入日時順に表示する。ただし、同日売り上げたものは、数量の多い順に表示する。
ts
const result = await sql`
SELECT order_date, item_name, unit_price, quantity, amount FROM ORDERS
WHERE category = '3'
AND quantity >= 2
ORDER BY order_date ASC, quantity DESC
`実行結果
sh
┌─────────┬──────────────────────────┬──────────────┬────────────┬──────────┬────────┐
│ (index) │ order_date │ item_name │ unit_price │ quantity │ amount │
├─────────┼──────────────────────────┼──────────────┼────────────┼──────────┼────────┤
│ 0 │ 2024-01-10T00:00:00.000Z │ 'タンブラー' │ 2000 │ 2 │ 4000 │
│ 1 │ 2024-01-15T00:00:00.000Z │ 'マグカップ' │ 1500 │ 3 │ 4500 │
└─────────┴──────────────────────────┴──────────────┴────────────┴──────────┴────────┘5.商品の分類ごとに、分類、商品名、サイズ、単価を1つの表として取得する。また、サイズはドリンクの商品についてのみ表示し、分類と商品名順に並べること。
ts
const result = await sql`
SELECT DISTINCT category, item_name, size_code, unit_price FROM ORDERS WHERE category = '1'
UNION
SELECT DISTINCT category, item_name, NULL AS size_code, unit_price FROM ORDERS WHERE category = '2'
UNION
SELECT DISTINCT category, item_name, NULL AS size_code, unit_price FROM ORDERS WHERE category = '3'
`実行結果
sh
┌─────────┬──────────┬────────────────┬───────────┬────────────┐
│ (index) │ category │ item_name │ size_code │ unit_price │
├─────────┼──────────┼────────────────┼───────────┼────────────┤
│ 0 │ '1' │ 'コーヒー' │ 'L' │ 500 │
│ 1 │ '1' │ 'コーヒー' │ 'M' │ 400 │
│ 2 │ '1' │ 'コーヒー' │ 'S' │ 300 │
│ 3 │ '1' │ 'ジュース' │ 'M' │ 350 │
│ 4 │ '1' │ '紅茶' │ 'S' │ 300 │
│ 5 │ '2' │ 'クッキー' │ null │ 200 │
│ 6 │ '2' │ 'ケーキ' │ null │ 450 │
│ 7 │ '2' │ 'サンドイッチ' │ null │ 500 │
│ 8 │ '3' │ 'タンブラー' │ null │ 2000 │
│ 9 │ '3' │ 'マグカップ' │ null │ 1500 │
└─────────┴──────────┴────────────────┴───────────┴────────────┘問題2
-10~10の班ににある自然数、整数、奇数、偶数がそれぞれ登録されている4つのテーブルがある。
| 列名 | データ型 | 備考 |
|---|---|---|
| 値 | INTEGER | テーブル名に応じた -10~10の値 |
自然数(NATURALS)
sh
┌─────────┬───────┐
│ (index) │ value │
├─────────┼───────┤
│ 0 │ 1 │
│ 1 │ 2 │
│ 2 │ 3 │
│ 3 │ 4 │
│ 4 │ 5 │
│ 5 │ 6 │
│ 6 │ 7 │
│ 7 │ 8 │
│ 8 │ 9 │
│ 9 │ 10 │
└─────────┴───────┘整数(INTEGERS)
┌─────────┬───────┐
│ (index) │ value │
├─────────┼───────┤
│ 0 │ -10 │
│ 1 │ -9 │
│ 2 │ -8 │
│ 3 │ -7 │
│ 4 │ -6 │
│ 5 │ -5 │
│ 6 │ -4 │
│ 7 │ -3 │
│ 8 │ -2 │
│ 9 │ -1 │
│ 10 │ 0 │
│ 11 │ 1 │
│ 12 │ 2 │
│ 13 │ 3 │
│ 14 │ 4 │
│ 15 │ 5 │
│ 16 │ 6 │
│ 17 │ 7 │
│ 18 │ 8 │
│ 19 │ 9 │
│ 20 │ 10 │
└─────────┴───────┘偶数(EVENS)
sh
┌─────────┬───────┐
│ (index) │ value │
├─────────┼───────┤
│ 0 │ -10 │
│ 1 │ -8 │
│ 2 │ -6 │
│ 3 │ -4 │
│ 4 │ -2 │
│ 5 │ 0 │
│ 6 │ 2 │
│ 7 │ 4 │
│ 8 │ 6 │
│ 9 │ 8 │
│ 10 │ 10 │
└─────────┴───────┘奇数(ODDS)
sh
┌─────────┬───────┐
│ (index) │ value │
├─────────┼───────┤
│ 0 │ -9 │
│ 1 │ -7 │
│ 2 │ -5 │
│ 3 │ -3 │
│ 4 │ -1 │
│ 5 │ 1 │
│ 6 │ 3 │
│ 7 │ 5 │
│ 8 │ 7 │
│ 9 │ 9 │
└─────────┴───────┘1.和集合の結果、整数テーブルと等しくなる。
ts
const result = await sql`
SELECT value FROM EVENS
UNION
SELECT value FROM ODDS
`実行結果
sh
┌─────────┬───────┐
│ (index) │ value │
├─────────┼───────┤
│ 0 │ -10 │
│ 1 │ -9 │
│ 2 │ -8 │
│ 3 │ -7 │
│ 4 │ -6 │
│ 5 │ -5 │
│ 6 │ -4 │
│ 7 │ -3 │
│ 8 │ -2 │
│ 9 │ -1 │
│ 10 │ 0 │
│ 11 │ 1 │
│ 12 │ 2 │
│ 13 │ 3 │
│ 14 │ 4 │
│ 15 │ 5 │
│ 16 │ 6 │
│ 17 │ 7 │
│ 18 │ 8 │
│ 19 │ 9 │
│ 20 │ 10 │
└─────────┴───────┘2.差集合の結果、奇数テーブルと等しくなる。
ts
const result = await sql`
SELECT value FROM INTEGERS
EXCEPT
SELECT value FROM EVENS
`実行結果
sh
┌─────────┬───────┐
│ (index) │ value │
├─────────┼───────┤
│ 0 │ -9 │
│ 1 │ -7 │
│ 2 │ -5 │
│ 3 │ -3 │
│ 4 │ -1 │
│ 5 │ 1 │
│ 6 │ 3 │
│ 7 │ 5 │
│ 8 │ 7 │
│ 9 │ 9 │
└─────────┴───────┘3.積集合の結果、偶数テーブルと等しくなる。
ts
const result = await sql`
SELECT value FROM INTEGERS
INTERSECT
SELECT value FROM EVENS
`実行結果
sh
┌─────────┬───────┐
│ (index) │ value │
├─────────┼───────┤
│ 0 │ -10 │
│ 1 │ -8 │
│ 2 │ -6 │
│ 3 │ -4 │
│ 4 │ -2 │
│ 5 │ 0 │
│ 6 │ 2 │
│ 7 │ 4 │
│ 8 │ 6 │
│ 9 │ 8 │
│ 10 │ 10 │
└─────────┴───────┘4.検索結果なし
ts
const result = await sql`
SELECT value FROM EVENS
INTERSECT
SELECT value FROM ODDS
`sh
┌─────────┐
│ (index) │
├─────────┤
└─────────┘