(1)~(10)に示す名前付きの導出表を,問合せ指定のFROM句に指定した場合,内部導出表を作成する条件は,次のようになります。
名前付きの導出表が副問合せ中に含まれる。又は,名前付きの導出表をFROM句に指定した問合せ指定が次のどれかを直接含んでいる。
例1:
SELECT * FROM T2 WHERE EXISTS(SELECT * FROM V1)
例2:
SELECT VC1,VC2 FROM V1 GROUP BY VC1,VC2
例3:
WITH Q1(QC1,QC2) AS (SELECT DISTINCT C1,C2 FROM T1)
SELECT DISTINCT * FROM V1
例4:
SELECT X.VC1,Y.C1 FROM V1 X,T2 Y WHERE X.VC1=Y.C2
例5:
SELECT V1.VC1,T2.C2 FROM V1 LEFT JOIN T2 ON T2.C2=V1.VC2
例6:
WITH Q1(QC1,QC2) AS (SELECT DISTINCT C1,C2 FROM T1)
SELECT QC1+100,CURRENT_DATE FROM Q1
例7:
SELECT VC1,(SELECT C1 FROM T2) FROM V1
例8:
SELECT VC1 FROM V1
例9:
SELECT VC1,VC2 FROM V1 ORDER BY 1
例10:
INSERT INTO T2 SELECT NEXT VALUE FOR SEQ1,VC1,VC2 FROM V1
名前付きの導出表をFROM句に指定した問合せ指定が,次のどれかを直接含んでいる。
例1:
WITH Q1(QC1,QC2) AS (SELECT C1,C2 FROM T1
GROUP BY C1,C2) SELECT AVG(QC1),QC2 FROM Q1
GROUP BY QC2
例2:
SELECT V1.VC1,V2.VC1 FROM V1,V2 WHERE V1.VC1=V2.VC1
例3:
WITH Q1(QC1,QC2) AS (SELECT C1,C2 FROM T1
GROUP BY C1,C2)
SELECT Q1.QC1,V1.VC1 FROM Q1 INNER JOIN V1 ON V1.VC2=Q1.QC2
例4:
SELECT COUNT(*) OVER(),C1 FROM V1
例5:
INSERT INTO T2 SELECT NEXT VALUE FOR SEQ1,VC1,VC2 FROM V1
名前付きの導出表をFROM句に指定した問合せ指定が,次のどれかを直接含んでいる。
例1:
SELECT AVG(VC1),VC2 FROM V1 GROUP BY VC2
例2:
SELECT * FROM V1 LEFT JOIN T2 ON T2.C2=V1.VC2
例3:
WITH Q1(QC1,QC2) AS (SELECT C1+100,C2||C2 FROM T1)
SELECT QC1,QC2 FROM Q1 GROUP BY QC1,QC2 HAVING QC1<=100
例4:
SELECT COUNT(*) OVER(),VC1 FROM V1
名前付きの導出表をFROM句に指定した問合せ指定が,次のどれかを直接含んでいる。
SELECT DISTINCT VC1 FROM V1
WITH Q1(C1) AS (SELECT AVG(DISTINCT C1) FROM T1)
SELECT COUNT(*) OVER(),C1 FROM Q1
名前付きの導出表が,外結合,又は内結合の表参照に指定されている。
例1:
SELECT V1.* FROM V1 LEFT JOIN T3 ON T3.C1=V1.VC1
例2:
WITH Q1(QC1,QC2) AS (SELECT T1.C1,T2.C1 FROM T1,T2)
SELECT * FROM Q1 INNER JOIN T3 ON Q1.QC1=T3.C1
名前付きの導出表をFROM句に指定した問合せ指定が,表の結合を直接含んでいる。
例1:
SELECT V1.VC1,T3.C1 FROM V1 LEFT JOIN T3 ON T3.C2=V1.VC2
例2:
WITH Q1(QC1,QC2) AS (SELECT T1.C1,T2.C1 FROM T1 LEFT JOIN T2
ON T1.C2=T2.C2) SELECT Q1.QC1 FROM Q1 INNER JOIN T3 ON T3.C2=Q1.QC2
名前付きの導出表をFROM句に指定した問合せ指定が,次のどれかを直接含んでいる。
例1:
SELECT VC1,VC2 FROM V1 WHERE VC1>0
例2:
WITH Q1(QC1,QC2) AS (SELECT (SELECT C1 FROM T2),C1 FROM T1)
SELECT QC1,QC2 FROM Q1 WHERE QC1>0
次のどれかの条件を満たす。
例1:
SELECT * FROM V1
例2:
WITH Q1(QC1,QC2) AS (
SELECT (SELECT C1 FROM T2),C2 FROM T1 UNION SELECT C1,C2 FROM T3)
SELECT * FROM Q1
次のどれかを満たす。
例1:
SELECT C1,C2 FROM V1 GROUP BY C1,C2
例2:
WITH Q1(QC1,QC2) AS (SELECT C1,C2 FROM T1 UNION SELECT C1,C2 FROM T2)
SELECT QC1,QC2 FROM Q1,T3 WHERE QC1=T3.C1
例3:
INSERT INTO T2 SELECT NEXT VALUE FOR SEQ1, VC1, VC2 FROM V1
次のどれかを満たす。
例1:
SELECT C1,C2 FROM V1 GROUP BY C1,C2
例2:
WITH Q1(QC1,QC2) AS (SELECT C1,C2 FROM T1 UNION ALL SELECT C1,C2 FROM T2)
SELECT QC1,QC2 FROM Q1,T3 WHERE QC1=T3.C1
例3:
SELECT * FROM T1 WHERE EXISTS(SELECT * FROM V1 WHERE V1.C1=T1.C1)
例4:
INSERT INTO T3 SELECT * FROM V1 WHERE C1>'C001'