ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

SQL で ORDER BY をクエリの最後にしか記述できず、結果セット全体にしか適用できない理由

はじめに

SQL で ORDER BY の指定はクエリの最後に記述する必要があります。例えば、2つの SELECT で ORDER BY した結果を UNION して和の結果を得ることはできません。

また、ORDER BY は結果セット全体に対して行われます。

-- 準備
CREATE TABLE employee (id integer, name varchar(20));
INSERT INTO employee VALUES (1, 'Mike');
INSERT INTO employee VALUES (2, 'Ken');
-- ORDER BY せずに UNION のみ実行したケース
SELECT id, name FROM employee
UNION ALL
SELECT id, name FROM employee;
id name
1 Mike
2 Ken
1 Mike
2 Ken
-- ORDER BY した結果を UNION の入力とすると syntax error になる
SELECT id, name FROM employee ORDER BY id ASC
UNION ALL
SELECT id, name FROM employee ORDER BY id DESC;
-- クエリの最後に ORDER BY を指定すると全体がソートされる
SELECT id, name FROM employee
UNION ALL
SELECT id, name FROM employee ORDER BY id DESC;
id name
2 Ken
2 Ken
1 Mike
1 Mike

この理由を理論面(リレーショナルモデル)と設計面 (SQL) からみていきます。

リレーショナルモデルからの考察

「データベース実践講義」から主に以下の節を参考にしました。
1.3.3 操作
1.5 関係の特性
5.9 ORDER BY 演算子

リレーショナルデータベースはリレーショナルモデルをベースにしています。

リレーショナルモデルの操作は「リレーショナル演算子」と「リレーショナル代入演算子」から構成されています。

リレーショナル演算子の例は「制限 (restrict)」「射影 (project)」「和 (union)」などです。これらは「リレーション(関係)を1つ以上入力し、リレーションを1つだけ出力する」ものです。

リレーションのタプルには順序がありません。これはリレーションがタプルの集合であり、数学における集合では要素が順序付けされていないことに由来します。

ORDER BY はリレーショナル演算子ではありません。ORDER BY はリレーションを入力に、順序付けされたタプルを生成するものです。順序付けされたタプルはリレーションとは言えません。となると、「リレーションを1つ以上入力し、リレーションを1つだけ出力する」リレーショナル演算子と ORDER BY は異なるものであることが分かります。

ORDER BY した結果がリレーションでないのであれば、リレーショナル演算子(例えば UNION)の入力とできないことは明白です。なぜなら、リレーショナル演算子の入力はリレーションのみだからです。

このことから、リレーショナルモデルでの操作の途中に ORDER BY をすることはできず、最終的なリレーションに対してのみ ORDER BY を適用できることが分かります。

SQL からの考察

プログラマのためのSQL 第4版」から主に以下の節を参考にしました。
7.3 カーソル

カーソルの役割は、SQL の結果セットを、ファイルのようなシーケンシャルなデータ構造(レコードが順序を持つ)に変換して、手続き型のホスト言語がデータを扱えるようにすることである。

このように、タプルに順序がないリレーションのままでは手続き型言語で扱いづらいため、カーソルが順序付けのあるデータ構造にする橋渡し役をしています。

標準 SQL では DECLARE CURSOR 文に ORDER BY 句が含まれています。SELECT 文の ORDER BY 句は DECLARE CURSOR 文の ORDER BY 句に対応しています。つまり、SELECT 文の ORDER BY 句はカーソルの一部といえます。

カーソルはクエリの結果セットを受け渡す方法です。つまり、カーソルの処理対象がクエリの最終的な結果に対してであるということを考えると、カーソルの一部である ORDER BY はクエリの途中結果に適用できないことが分かります。

まとめ

SQL で ORDER BY をクエリの最後にしか記述できず、結果セット全体の適用しかできない理由は、リレーショナルモデルの操作に起因していました。また、SQL の観点からも同様の結論が導き出せました。

リレーショナルデータベースの世界では順序を付けず、外の世界(例えば手続き型言語)へ受け渡すときに初めて順序を付ける。この順序付けの為に ORDER BY があるといえます。