from句で指定した別名がwhere句で参照できない！？

Question

準備

drop table nums;
create table nums (
	n int not null 
);
insert into nums values(1);
insert into nums values(2);
insert into nums values(3);
insert into nums values(4);
insert into nums values(5);
insert into nums values(6);
insert into nums values(7);
insert into nums values(8);
insert into nums values(9);

実現したかったこと

select 
	* 
from 
	(select * from nums where n % 3 = 0) as t1
where 
	n > (select avg(n) from t1)
;

レコードが1件のみで、値は9
という結果を期待した。

発生している問題・エラーメッセージ

ERROR:  リレーション"t1"は存在しません
LINE 6:  n > (select avg(n) from t1)
                                 ^

試したこと

--(1)where句にも同じ条件を記載
select 
	* 
from 
	(select * from nums where n % 3 = 0) as t1
where 
	n > (select avg(n) from nums where n % 3 = 0)
;--OK 9

--(2)一旦、テーブルを作ってそれを参照（共通テーブル式)
with m3(n) as (
	select * from nums where n % 3 = 0
)
select 
	* 
from 
	m3
where 
	n > (select avg(n) from m3)
;--OK 9
--(3)更に単純な例
select * from nums
where n > (select avg(n) from nums)
;--OK 6,7,8,9
select * from nums as t1
where n > (select avg(n) from t1)
;--NG リレーション"t1"は存在しません

補足

本当にやりたかったことはもっと複雑なテーブル＆もっと複雑な条件です。
質問用に簡易的なテーブルで再現させています。

試したことの(1)は、可読性が悪く、ミスの混入しやすさ等を考慮して却下。
試したことの(2)は、いちいちテーブル作るのは後処理やトランザクションが面倒。
共通テーブル式は妥協できるレベルですが、割と新しい機能っぽいので、昔の人はどうしてたのかなと。。。
もっとスマートな方法があるはず！と思い、質問させていただきました。

Answer 1

単に条件の切り出し方だけのような気がしますけど。
共通の条件式が多いなら、(2)の方がすっきりしますよね。

--(1)where句にも同じ条件を記載
select * from nums 
where  n % 3 = 0
  and  n > (select avg(n) from nums where n % 3 = 0)
;
--(2)共通テーブル式
with m3 as (
  select * from nums where n % 3 = 0
)
select * from m3
where n > (select avg(n) from m3)
;

共通テーブル式は妥協できるレベルですが、割と新しい機能っぽいので、昔の人はどうしてたのかなと

WITH(CTE)を使用しない場合、同じサブクエリーを複数展開しなければならず、CTEを利用すると、可読性も上がるし、コストも下がる傾向にあるので使わない手は無いかと思います。
尚、WITHでは他のWITHも参照できるので、ネストしたものも分解し可読性を上げることが出来ます。

ただ、どんな場合でもコストが下がるかと言えば一概には言えないと思うので、実行計画を確認しながらの組み立てをお薦めします。

Answer 2

副問合せは先に評価されるはずなので、その時点でt1は何も定義されていません。
なので妥協案2を採用した方が適切なはずです。

Comments

[picohead]

昔の人のやり方はわからずじまいでしたが、
＞副問い合わせが先に評価される
できない理由が明確になったのでベストアンサーとしました。

ちなみに下記もダメでした。（別名は副問い合わせ内だけ有効？）
select
*
from
t1
where
n > (select avg(n) from (select * from nums where n % 3 = 0) as t1)
;

[FKM]

そうです。これだとt1というテーブルを参照することになりますので
表別名は基本カラムの所在を明示的に示すものなので、テーブルをこのように表別名だけ書くことはできません

[sazi]

> 副問い合わせが先に評価される

基本そう考えてSQLを組み立てて問題無いと思いますが、実行計画で確認した場合そうならない場合もあります。
相関副問合せなどは最たるものです。