KDOC 557: UNIONはSELECTしたフィールドでないとORDER BYできない
この文書のステータス
- 作成
- 2026-05-12 貴島
- レビュー
- 2026-05-13 貴島
概要
JOINはSELECTしてなくてもORDER BYできる。
CREATE TABLE products (
id INT PRIMARY KEY,
name TEXT,
price INT
);
CREATE TABLE reviews (
id INT PRIMARY KEY,
product_id INT REFERENCES products(id),
score INT,
created_at DATE
);
INSERT INTO products VALUES
(1, 'Widget', 500),
(2, 'Gadget', 1200),
(3, 'Gizmo', 800);
INSERT INTO reviews VALUES
(1, 1, 4, '2026-01-10'),
(2, 2, 5, '2026-02-15'),
(3, 3, 3, '2026-03-01');
-- JOINではSELECTしていないカラムでORDER BYできる
-- reviews.created_at はSELECTにないが、JOINの結果テーブルに存在するのでORDER BYできる
SELECT p.name, r.score
FROM products p
JOIN reviews r ON p.id = r.product_id
ORDER BY r.created_at;
| | |
|---------------------------------------------+-------|
| > CREATE TABLE products ( | |
| > id INT PRIMARY KEY, | |
| > name TEXT, | |
| > price INT | |
| > ); | |
| CREATE TABLE | |
| | |
| > CREATE TABLE reviews ( | |
| > id INT PRIMARY KEY, | |
| > product_id INT REFERENCES products(id), | |
| > score INT, | |
| > created_at DATE | |
| > ); | |
| CREATE TABLE | |
| | |
| > INSERT INTO products VALUES | |
| > (1, 'Widget', 500), | |
| > (2, 'Gadget', 1200), | |
| > (3, 'Gizmo', 800); | |
| INSERT 0 3 | |
| | |
| > INSERT INTO reviews VALUES | |
| > (1, 1, 4, '2026-01-10'), | |
| > (2, 2, 5, '2026-02-15'), | |
| > (3, 3, 3, '2026-03-01'); | |
| INSERT 0 3 | |
| | |
| > SELECT p.name, r.score | |
| > FROM products p | |
| > JOIN reviews r ON p.id = r.product_id | |
| > ORDER BY r.created_at; | |
| name | score |
| Widget | 4 |
| Gadget | 5 |
| Gizmo | 3 |
UNIONではSELECTしたカラムでしかORDER BYできない。
CREATE TABLE products (
id INT PRIMARY KEY,
name TEXT,
price INT
);
CREATE TABLE reviews (
id INT PRIMARY KEY,
product_id INT REFERENCES products(id),
score INT,
created_at DATE
);
INSERT INTO products VALUES
(1, 'Widget', 500),
(2, 'Gadget', 1200),
(3, 'Gizmo', 800);
INSERT INTO reviews VALUES
(1, 1, 4, '2026-01-10'),
(2, 2, 5, '2026-02-15'),
(3, 3, 3, '2026-03-01');
-- OK: name はSELECTされたカラムなのでORDER BYできる
SELECT name, price FROM products WHERE price >= 1000
UNION ALL
SELECT name, price FROM products WHERE price < 1000
ORDER BY name;
-- エラー: id はSELECTされていないのでORDER BYできない。column "id" not found になる
SELECT name, price FROM products WHERE price >= 1000
UNION ALL
SELECT name, price FROM products WHERE price < 1000
ORDER BY id;
| | |
|--------------------------------------------------------+-------|
| > CREATE TABLE products ( | |
| > id INT PRIMARY KEY, | |
| > name TEXT, | |
| > price INT | |
| > ); | |
| CREATE TABLE | |
| | |
| > CREATE TABLE reviews ( | |
| > id INT PRIMARY KEY, | |
| > product_id INT REFERENCES products(id), | |
| > score INT, | |
| > created_at DATE | |
| > ); | |
| CREATE TABLE | |
| | |
| > INSERT INTO products VALUES | |
| > (1, 'Widget', 500), | |
| > (2, 'Gadget', 1200), | |
| > (3, 'Gizmo', 800); | |
| INSERT 0 3 | |
| | |
| > INSERT INTO reviews VALUES | |
| > (1, 1, 4, '2026-01-10'), | |
| > (2, 2, 5, '2026-02-15'), | |
| > (3, 3, 3, '2026-03-01'); | |
| INSERT 0 3 | |
| | |
| > SELECT name, price FROM products WHERE price >= 1000 | |
| > UNION ALL | |
| > SELECT name, price FROM products WHERE price < 1000 | |
| > ORDER BY name; | |
| name | price |
| Gadget | 1200 |
| Gizmo | 800 |
| Widget | 500 |
| | |
| > SELECT name, price FROM products WHERE price >= 1000 | |
| > UNION ALL | |
| > SELECT name, price FROM products WHERE price < 1000 | |
| > ORDER BY id; | |
JOINは行を横に結合するので元テーブルの全カラムが結果に残る。UNIONは行を縦に積むのでSELECTしたカラムだけが結果に残る。
関連
抽象。
- 横につなぐと元の項目がすべて残り、縦に積むと選んだ項目だけが残るので、後で参照できるものが違う
関連。
なし。