[DB] SQLメモ・疑問とか

チョイスタのOracleをしていての疑問や知らなかった機能とかのメモ
時々答えがあったりなかったり。。

■　MAX関数の疑問

　　①　同じ値が複数列にある場合、どの値を返すのか？

　　　　　名前　　 |　　　得点
　　　　　--------+---------
　　　　　Aさん　 　|　　　　100
　　　　　Bさん　　 |　　　　 90
　　　　　Cさん　　 |　　　　100
　　　　　Dさん　　 |　　　　 85
　
　 select 名前 , MAX(得点)　from　成績表　group by 名前
　　　　　→？
　　　　　　　MAXは複数の値を返す？

　　② NULLをMAX関数はどのように扱う？(①の疑問とややかぶっているが)


　　　　　名前　　 |　　　得点
　　　　　--------+---------
　　　　　Aさん　 　|　　　　NULL
　　　　　Bさん　　 |　　　　NULL
　　　　　Cさん　　 |　　　　NULL
　　　　　Dさん　　 |　　　　NULL
　
　 select 名前 , MAX(得点)　from　成績表　group by 名前
　　　　　→？

■　副問い合わせ

　　①　副問合せが NULL を戻すと主問合せは1行も結果を戻さない
　　　　　　→　nvlとかで置き換えをすると大丈夫なのでは？

　　②　INとNOT　INの思わぬ違い

　　　　　"IN" を用いた場合、副問合せが返す複数の値のうち、一つだけがNULLでも主問合せは結果を返す
　　　　　"NOT IN" を用いた場合、副問合せが返す複数の値のうち、一つでもNULLがあれば主問合せは結果を返さない
　　　　
■　NULLIF　と NVL2

　　　NULLIF( 式1, 式2)は式1と式2を比較し、等しい場合はNULLを戻す
　　　NVL2(式, 値1, 値2) は、式の値がNULL以外 の場合、値1 を戻し、NULL の場合、値2を戻す

■　切捨てとか
　　　　CEIL(n)はn以上の最も小さい整数を戻します。
　　　　FLOOR(n)はn以下の最も大きい整数を戻します。
　　　　MOD(m,n)はmをnで割ったあまりを戻します。
　　　　POWER(m,n)はmをn乗した値を戻します
　　　　SQRT(n)はnの平方根を戻します。

■　速度向上？
　　　order byをサブクエリと両方でする。早い、ような？

select a , b , c
from
　　(select a , b , c from DD order by a , b , c)
order by a , b , c

■　distinct と order by

　　一緒にするとエラーが出る（ORA-01791: SELECT式が無効です）

　　例）
　　select distinct A_1 , A_2
　　from TblA left outer join TblC on (A_2 = TblC.Code)
　　order by A_1 , C_order , A_2

　　selectの中に、C_orderが無いと上記エラー
　
　　なんで？
　　distinctをする場合、order byの項目はselectに含める必要がある、と明記されている
　　http://msdn.microsoft.com/ja-jp/library/Cc350739

　　どうも、order byとdistinctは両方とも並べ替えをするらしい。
　　両方あると、並べ替えが競合するのでエラーになる・・・というDBもある。
　　http://support.microsoft.com/kb/96895/ja

　　よくわからんが、同じ問題で悩んでいる人は多い。
　　原理がわからなくてモヤっとするが、それほど困る問題でもない。例の場合は、C_orderをselectに入れとけばいい。
　　（selectに含められないなら仕方ないが）

　　この辺が仕様の答えになるの・・・かもしれない？
　　http://oraclesqlpuzzle.hp.infoseek.co.jp/10-211.html
　　http://oraclesqlpuzzle.hp.infoseek.co.jp/1-6.html

　　ちなみに、この問題が起こったSQLは、最終的にこんな感じにした。
　　(※　欲しい答えを求めた結果であって、問題解決そのものではないので注意)

　　select A_1 , A_2
　　from(
　　　　select ditinct A_1 , 0 as A_2
　　　　from TblA
　　　　
　　　　union all
　　　　
　　　　select ditinct 0 as A_1 , A_2
　　　　from TblA
　　) left outer join TblC on (TblA_2 = TblC.Code)
　　order by A_1 , C_order , A_2

　　たいそうなSQLになってしまったなぁ・・・。


■　union all と order byを併用するとエラーになる
　　
　　なるらしい。
　　回避するには、全部のunion allの後に、order byをする。
　　
　　参考
　　http://www.atmarkit.co.jp/fnetwork/rensai/sql09/sql1.html
　　
　　なんで？

■　今さらオラクルSQLの基礎
　　
　　システム日付を取得する
　　select SYSDATE from dual;