- PR -

副問い合わせの評価順について

1
投稿者投稿内容
ぱう
会議室デビュー日: 2008/01/22
投稿数: 5
投稿日時: 2008-04-24 18:18
社内研修で副問い合わせについて以下のような例を教えていました。

＜例＞全員の平均出勤日数より多い人の氏名を取得

SELECT ename FROM emp e
INNER JOIN (SELECT empno, COUNT(*) d FROM kintai GROUP BY empno) k
ON e.empno = k.empno
WHERE k.d > (SELECT AVG(d) FROM
(SELECT empno, COUNT(*) d FROM kintai GROUP BY empno) t)

とここで、「主問い合わせの"k"をなぜ副問い合わせで使えないのか」、つまり最後のWHERE句を、

WHERE k.d > (SELECT AVG(d) FROM k)

となぜできないのか、という質問を受けました。SQLの評価順序はFROM→WHEREと教えてましたし、実際WHEREの左辺では"k"を使用しているわけです。

SQL Serverで実行プランを表示してみると、上記SQLでは副問い合わせが最初に実行されています。「副問い合わせは最初」というルールがあるとすると、じゃあ相関副問い合わせはどうなるんだろうという疑問もあります。エンジンが相関副問い合わせかどうかを判断して、主／副どちらを最初に実行するか決めているのでしょうか。
よっしー
大ベテラン
会議室デビュー日: 2007/05/17
投稿数: 143
投稿日時: 2008-04-25 12:49
ぱうさんこんにちは。

共通テーブル式を使うのはどうでしょう？．．．という話ではないんですよね。

主／副どちらを最初に実行するかなんて実行プランを作成するまでは決まってないと思います。ご自身もおっしゃられている通り、相関副問い合わせもあるんだし。
ぱう
会議室デビュー日: 2008/01/22
投稿数: 5
投稿日時: 2008-04-25 20:24
よっしーさんありがとうございます。

私も実際は書いて実行してを繰り返すわけですが、研修なので何かしらのルールがあれば説明したいと思いまして…。

> 主／副どちらを最初に実行するかなんて実行プランを作成するまでは決まってないと思います。

ということは、実行プランの作られ方によっては、

　　WHERE k.d > (SELECT AVG(d) FROM k)

と書いても実行できることがある、ということでしょうか？
べる
ぬし
会議室デビュー日: 2003/09/20
投稿数: 1093
投稿日時: 2008-04-26 03:59
引用:
　　WHERE k.d > (SELECT AVG(d) FROM k)

と書いても実行できることがある、ということでしょうか？
実行できるときと出来ないときがある、なんてことはないでしょう。
相関副問い合わせでも FROMに kはかけないですよね。

ところで最初のクエリ、
コード:
SELECT          e.ename

FROM            emp AS e INNER JOIN

                      kintai AS k ON e.empno = k.empno

GROUP BY     e.empno, e.ename

HAVING        (COUNT(*) >

            (SELECT          AVG(d) AS Expr1

            FROM     (SELECT          empno, COUNT(*) AS d

                     FROM             kintai

                     GROUP BY     empno) AS t))
ではだめですかね。

↓これはすっきりしてますね。

[ メッセージ編集済み 編集者: べる 編集日時 2008-04-26 11:31 ]
よっしー
大ベテラン
会議室デビュー日: 2007/05/17
投稿数: 143
投稿日時: 2008-04-26 10:59
引用:

ぱうさんの書き込み (2008-04-25 20:24) より:
　　WHERE k.d > (SELECT AVG(d) FROM k)

と書いても実行できることがある、ということでしょうか？


そういう意味ではありません。構文エラーになりますよね？
実行順について書いただけです。

で、共通テーブル式を使うとこう。
WITH k AS
(
SELECT empno, COUNT(*) d FROM kintai GROUP BY empno
)
SELECT ename FROM emp e
INNER JOIN k
ON e.empno = k.empno
WHERE k.d > (SELECT AVG(d) FROM k)


カーニー
ぬし
会議室デビュー日: 2003/09/04
投稿数: 358
お住まい・勤務地: 東京
投稿日時: 2008-04-28 11:27
ただ単に、そういう文法はRDBMSによってサポートされていないから、ではダメなんですか?

そもそも、SQLにおいて「実行順序」というのは気にする必要のないものです。SQLは「宣言的」なのです。
実際にはチューニングのために、実行プランを含めて気にしないといけないこともよくあるのは確かですが、それでも実行順序を明示的に指定できる範囲には限りがありますし、そうしなくて済むのが理想です。

副問い合わせに関しても、副問い合わせだから必ず先に評価されるとも限らず、主問い合わせにマージするような実行プランが選択される可能性もあるわけです。(今回のAVG使う副問い合わせでそれは起きないでしょうが)
ぱう
会議室デビュー日: 2008/01/22
投稿数: 5
投稿日時: 2008-04-28 14:01
実際のプロジェクトであれば、よっしーさんのようにWITHを使うか、あらかじめビューを作っておくかするところだと思います。ただ研修なのでだめな理由を説明したいと思っていました。

そこで今さらなんですがT-SQLのFROM句をマニュアルで見たところ、カーニーさんご指摘の通り、FROMの後に指定できるものの中に「テーブル別名」は含まれていないんですね。

http://msdn2.microsoft.com/ja-jp/library/ms177634.aspx

やはり言語のマニュアルは一通り目を通さければいけないと反省です。
皆様いろいろとありがとうございました。
1

スキルアップ／キャリアアップ（JOB@IT）