SQL副問い合わせ内のエイリアス

質問

DEPARTMENTとEMPLOYEESのテーブルがあり、そこから社員のいない部署を取得しようとしているのですが、わたしの書いたSQLがうまく動きません。作動しない理由をご教授くだされば助かります。オラクル8.1.7.4.1で動作させなければならず、left outer joinが使えないところからの質問です。

データはこういう構造です(*がプライマリーキィを表す)。
DEPARTMENT: *DEPT__T, DEPT_NAME, ....
EMPLOYEES: *EMPLOYEES__T, DEPT_NAME, ...

これに対して、
select dept__t,
(select count(*) from employee where department.dept__t = employees.dept__t) counter
from department
where counter = 0
というSQLで社員のいない部署を取ろうとしたのですが、ORA-00904: "COUNTER": invalid identifier.が返されてしまいます。

WHERE文にcounterというエイリアスではなくカラムそのものを書くと問合せが正常に作動するのですが、なぜでしょうか。オラクルのバージョンが古いことが原因でしょうか。

回答No.5

MZ-80B

ただ純粋なエラーの原因を探しているのでしたら

SQL リファレンスマニュアルのSELECT文に別名に関する記述があります。
--- 引用 ---
c_alias
列式の別名を指定します。
この別名は、結果セットの列のヘッダーで使用されます。
...中略...
問合せにおいて、別名はorder_by_clause で使用できますが、
他の句では使用できません。
---
これは 10g の仕様です。標準SQLについてはわかりません。

補足

＞ただ純粋なエラーの原因を探している・・・

質問の趣旨はその通りです。

ご示唆いただいたSQL リファレンスマニュアルを読みこんだところ、SQL文は

FROM
WHERE
GROUP BY
HAVING
SELECT
UNION
ORDER BY
の順で解釈されると読み取れる記述を見つけました。わたしのSQLではSELECT句で宣言されたエイリアスをWHERE句で比較するよう要求しているので、データベースがまだ定義されていないエイリアスは解釈できないとしてオラクルエラーを出しているのではないかというのがわたしの独自にたどり着いた結論です。

この理解でよいのかだけ最後に確認させてください。

回答No.4

X-trail_00

別名は、データの抽出後に使用します。
データベースからwhereの条件に合うものを
メモリ上におきます。
そこで項目に名前をつけるときに
別名指定があれば、別名をつけます。
データ抽出時点で別名は認識していません。
そのあとにorderby指定があればソートします。
ですので、orderbyでは別名を使用できます。

大まかには、こういった感じのようです。
（細かい部分は間違ってるかも・・・）

回答No.3

MZ-80B

exists ( select ～) や in ( select ～)
を使えない理由でもあるのですか？

補足

アドバイスありがとうございます。

回答No.2

X-trail_00

select department.dept__t,
count(employees.dept__t)
from department,employee
where department.dept__t = employees.dept__t(+)
group by department.dept__t
having count(employees.dept__t) =0

上記のように書き換えれば動きます。
こちらのほうがスマートではないでしょうか？
また上記のエイリアスでのwhere文(where counter = 0)は
オラクルの後のバージョンでもエラーになります。

お礼

(+)がouter joinと同じ意味なのですね。お教えくださりありがとうございます。さらにエイリアスがエラーになる理由をお願いできませんでしょうか。これまでにもなんどとなくエイリアスでエラーを出しては、別のSQLを書いて切り抜けてきたのですが、原因を理解するところまでは力が及ばずにいます。

回答No.1

k_o_r_o_c_h_a_n

select dept__t,
(select count(*) from employee where department.dept__t = employees.dept__t) "counter"
from department
where counter = 0

という具合に、ダブルコ－ティーションで囲ってください。
( counter => "counter" )

補足

エイリアスの定義を"counter"としても同じエラーが出ます。 as "counter", as counterでもトライしましたが同じ結果です。お手数をかけますが、このオラクルエラーの意味を教えていただけませんでしょうか。