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したカラムだけが結果に残る。
関連
なし。