みなさんこんにちは！

長い間Oracleについて役に立つと考えていたので、私はたくさんのトピックを試しました。 それが長すぎることが判明したたびに、それは野生に深く吹き込んだので。 したがって、私は聴衆の興味とその資料のプレゼンテーションのスタイルとの関係を評価するために、可能な限り単純なトピックから始めることにしました（IMHO、筆者は私からあまり良くありません）。 いくつかのメモ：

• このノートではOracle DBMSを参照していますが、書かれているもののほとんどは他のDBMSに関係しています。
• きれいなコードではうまくいきませんでした。ソースlang = "sql"タグでは、知らないうちに行う方法があまり強調されていません。 誰かが表示されたら、私はすぐにすべてをより読みやすい形式にします。

だから。

キーポイント

特別な値NULLは、データが存在しないことを意味します。これは、値が不明であるという事実の表明です。 デフォルトでは、 NOT NULL



制約が課されていない限り、任意のタイプの列と変数がこの値を受け入れることができます。 また、DBMSは、テーブルのプライマリキーに含まれる列にNOT NULL



を自動的に追加します。



NULLの主な機能は、何にも等しくないこと、さらに別のNULLでさえないことです。 演算子を使用して値を比較することはできません： =



、 <



、 >



、 like



...式NULL != NULL



さえも、未知のものを明確に比較できないため、真ではありません。 ちなみに、条件を計算するとき、Oracleは



と



制限されないため、この式もfalseになりません。 NULLの形式で不確実性の要素が存在するため、別の状態



ます。



したがって、Oracleは2値ではなく3値のロジックで動作します。 祖父コッドは彼のリレーショナル理論にこの特徴を置いており、リレーショナルDBMSであるOracleは彼の教訓に完全に従っています。 「奇妙な」クエリ結果を黙想しないために、開発者は3値論理の真理値表を知る必要があります。 たとえば、英語版のWikipedia： Three-valued_logicで見ることができます。



便宜上、ブールパラメータの状態を出力するプロシージャを作成します。

 create procedure test_bool( p_bool boolean ) is begin case when p_bool = true then dbms_output.put_line('TRUE'); when p_bool = false then dbms_output.put_line('FALSE'); else dbms_output.put_line('UNKNOWN'); end case; end test_bool;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

メッセージをコンソールに出力するオプションを有効にします。

 set serveroutput on
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

使い慣れた比較演算子は、NULLの前に渡されます。

 exec test_bool( null = null ); -- UNKNOWN exec test_bool( null != null ); -- UNKNOWN exec test_bool( null = 'a' ); -- UNKNOWN exec test_bool( null != 'a' ); -- UNKNOWN
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

NULLとの比較

IS NULL



との比較を可能にする特別なIS NULL



およびIS NOT NULL



演算子がIS NOT NULL



。 IS NULL



は、オペランドがNULL



場合はtrueを返し、そうでない場合はfalseをNULL



。

 select case when null is null then 'YES' else 'NO' end from dual; -- YES select case when 'a' is null then 'YES' else 'NO' end from dual; -- NO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

したがって、 IS NOT NULL



は逆の動作をしIS NOT NULL



オペランド値がNULL以外の場合はtrueを返し、NULLの場合はfalseを返します。

 select case when 'a' is NOT null then 'YES' else 'NO' end from dual; -- YES select case when null is NOT null then 'YES' else 'NO' end from dual; -- NO
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

さらに、欠損値との比較に関する規則にはいくつかの例外があります。 1つはDECODE



関数で、2つのNULLを互いに同等と見なします。 次に、これらは複合インデックスです。2つのキーに空のフィールドが含まれていても、空でないフィールドがすべて等しい場合、Oracleはこれら2つのキーを同等と見なします。



DECODE



はシステムに反します：

 select decode( null , 1, 'ONE' , null, 'EMPTY' --     , 'DEFAULT' ) from dual;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

複合インデックスの例は、インデックスセクションにあります。

論理演算とNULL

通常、



状態は



と同じ方法で処理され



。 たとえば、テーブルから行を選択し、 WHERE



で条件x = NULL



を評価する



される場合、単一の行は取得されません。 ただし、違いがあり()



式()



がtrueを返す場合、 ()



は



返します。 不明な状態を処理する場合、論理演算子AND



およびOR



も独自の特性があります。 以下の例の詳細。



ほとんどの場合、未知の結果は



として扱われ



。

 select 1 from dual where dummy = null; --    
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

未知の否定は未知を与えます：

 exec test_bool( not(null = null) ); -- UNKNOWN exec test_bool( not(null != null) ); -- UNKNOWN exec test_bool( not(null = 'a') ); -- UNKNOWN exec test_bool( not(null != 'a') ); -- UNKNOWN
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

OR



演算子：

 exec test_bool( null or true ); -- TRUE <- !!!!! exec test_bool( null or false ); -- UNKNOWN exec test_bool( null or null ); -- UNKNOWN
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

AND



演算子：

 exec test_bool( null and true ); -- UNKNOWN exec test_bool( null and false ); -- FALSE <- !!!!! exec test_bool( null and null ); -- UNKNOWN
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

演算子INおよびNOT IN

まず、いくつかの準備手順を行います。 テストのために、1つの数値列A



と4つの行を持つテーブルT



を作成します： 1, 2, 3



およびNULL

 create table t as select column_value a from table(sys.odcinumberlist(1,2,3,null));
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

リクエストトレースをPLUSTRACE



（このためには、 PLUSTRACE



ロールが必要です）。

トレースからのリストでは、要求で指定された条件が展開されていることを示すために、フィルター部分のみが残されています。

 set autotrace on
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

準備手順は完了しました。次に、オペレーターと協力しましょう。 セット(1, 2, NULL)



すべてのレコードを選択してみましょう。

 select * from t where a in(1,2,null); --  [1,2] -- Predicate Information: -- filter("A"=1 OR "A"=2 OR "A"=TO_NUMBER(NULL))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

ご覧のとおり、NULLの行は選択されていません。 これは、述語"A"=TO_NUMBER(NULL)



の計算で状態



返されたために発生しました。 クエリ結果にNULLを含めるには、これを明示的に指定する必要があります。

 select * from t where a in(1,2) or a is null; --  [1,2,NULL] -- Predicate Information: -- filter("A" IS NULL OR "A"=1 OR "A"=2)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

NOT IN



試してみましょう：

 select * from t where a not in(1,2,null); -- no rows selected -- Predicate Information: -- filter("A"<>1 AND "A"<>2 AND "A"<>TO_NUMBER(NULL))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

通常、単一のエントリではありません！ トリプルがクエリ結果に入らなかった理由を見てみましょう。 DBMSがケースA=3



使用したフィルターを手動で計算します。

  3 <> 1 AND 3 <> 2 AND 3 <> to_number（NULL）
    \ / \ / \ /
   真と真と未知
        \ / /
        真および未知
             \ /
             不明 

3値ロジックの特性により、 NOT IN



はNULLとまったく友好的ではありNOT IN



。NULLが選択条件に入るとすぐに、データを待機しないでください。

NULLおよび空の文字列

ここで、OracleはANSI SQL標準から離れ、NULLと空の文字列の等価性を宣言します。 これはおそらく最も議論の余地のある機能の1つであり、個人への移行を伴う複数ページの議論を引き起こし、お互いに糞便を注ぎ、厳しい紛争のその他の不可欠な属性をもたらします。 ドキュメントから判断すると、Oracle自体はこの状況を変更することを気にかけません（空の文字列は現在NULLとして扱われていますが、これは将来のリリースで変更される可能性があると言います）が、今日、このDBMS用に書かれた非常に大量のコードがありますシステムの動作を変更することはほとんど現実的ではありません。 さらに、彼らは少なくともDBMSの7番目のバージョン（1992-1996）でこのことについて話し始め、現在では12番目のバージョンが準備中です。



NULLと空の文字列は同等です。

 exec test_bool( '' is null ); -- TRUE
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

激しい紛争の不可欠な属性：

 select count(*) comments_count, avg(c.vote) avg_vote from habr.comments c join habr.posts p on( c.post_id = p.id ) where lower(p.title) like '%%' and lower(p.title) like '%%'; COMMENTS_COUNT AVG_VOTE --------------- --------- 100500 -100
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

クラシックの原則に従ってルートを見ると、空の文字列とNULLが同等である理由は、データブロック内のvarcharsとNULLのストレージ形式にあります。 Oracleは、ヘッダーとそれに続くデータの列で構成される構造にテーブル行を格納します。 各列は、列のデータ長（1または3バイト）と実際にはデータ自体の2つのフィールドで表されます。 varchar2



長さがゼロの場合、データフィールドに書き込むものはなく、バイトを占有せず、特別な値0xFF



が長さとともにフィールドに書き込まれ、データがないことを示します。 NULLはまったく同じ方法で表示されます。データフィールドはなく、長さのあるフィールドに0xFF



が書き込まれます。 もちろん、Oracle開発者はこれらの2つの状態を分離できますが、初期の頃からは、これらの状態が慣習でした。



個人的には、空の文字列とNULLの等価性は非常に自然で論理的なもののようです。 「空の文字列」という名前自体は、意味の欠如、空、ドーナツの穴を意味します。 一般に、NULLは同じことを意味します。 しかし、不快な結果があります。空の文字列がゼロであると自信を持って言える場合、NULLの長さは定義されません。 したがって、当然のことながら、式のlength('')



はゼロではなくNULLを返します。 別の問題：空の文字列と比較することはできません。 式val = ''



は、本質的にはval = NULL



と同等であるため、状態



を返しval = NULL



。



空の文字列の長さは定義されていません。

 select length('') from dual; -- NULL
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

空の文字列との比較はできません。

 exec test_bool( 'a' != '' ); -- UNKNOWN
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

オラクルが提案したアプローチの批評家は、空の行が必ずしもあいまいさを意味するとは言いません。 たとえば、営業部長は顧客カードに記入します。 彼は自分の連絡先電話番号（555-123456）を示すことができ、彼が不明（NULL）であることを示すことができ、また連絡先電話番号がないこと（空の行）を示すこともできます。 空の行を格納するオラクルの方法では、最後のオプションを実装するのに問題があります。 セマンティクスの観点からは、引数は正しいのですが、完全な答えを受け取ったことがない質問が常にあります。「電話」フィールドに空の行を入力する方法と、NULLとさらに区別する方法を教えてください。 もちろん、オプションがありますが、それでも...



実際、PL / SQLに関しては、エンジンの内部のどこかで、空の文字列とNULLは異なります。 これを確認する1つの方法は、連想コレクションを使用すると、インデックス''



（空の文字列）を持つ要素を保存できるが、インデックスNULLを持つ要素を保存できないという事実によるものです。

 declare procedure empty_or_null( p_val varchar2 ) is type tt is table of varchar2(1) index by varchar2(10); t tt; begin if p_val is not null then dbms_output.put_line(' '); else --      p_val t(p_val) := 'x'; -- ! ,   dbms_output.put_line(' '); end if; exception --     p_val  . , NULL when others then dbms_output.put_line('NULL'); end; begin empty_or_null( 'qwe' ); --   empty_or_null( '' ); --   empty_or_null( NULL ); -- NULL end;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

このようなフェイントを実際に耳で使用することは価値がありません。 問題を回避するには、ドックからルールを学習することをお勧めします。空の文字列と区別できないもののNULLです。

数学ヌル

^{この小さなパラグラフは、金曜日のREN-TV映画を背景に、金曜日の夜にビールのために書かれました。 彼の怠inessを書き直して、すみません。}



チャレンジ。 マーシャは、コリャと結婚する前に未知の数の恋人がいました。 コリャは、結婚後、マーシャがサーシャとヴィティヤとセックスをしたことを知っています。 Kolyaの恋人マーシャの正確な数を見つけるのに役立ちます。



明らかに、Kolyaを支援することはできません。Mashaの恋人の数が不明であるため、結婚によってすべての計算が1つの値に減少します-不明です。 オラクルは、オラクルと呼ばれていましたが、この質問では霊魂の戦いの参加者よりも先に進むことはありません。明白な質問に対してのみ明白な答えを与えます。 しかし、オラクルはもっと正直であることを認めなければなりません。コリヤの場合、彼は精神分析に従事せず、すぐに「わからない」と言うでしょう。

 select decode( null + 10, null, '', '') a from dual; --  select decode( null * 10, null, '', '') a from dual; --  select decode( abs(null), null, '', '') a from dual; --  select decode( sign(null), null, '', '') a from dual; -- 
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

連結は異なります。文字列にNULLを追加できますが、これは変更しません。 これが二重基準の方針です。

 select null ||'AA'|| null ||'BB'|| null from dual; -- AABB
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

NULLおよび集約関数

COUNT



除くほとんどすべての集計関数（常にそうであるとは限りません）は、計算の空の値を無視します。 彼らがこれを行わなかった場合、最初にNULLが点滅すると、関数の結果が不明な値になります。 たとえば、 SUM



関数を使用します。これは、系列(1, 3, null, 2)



を合計する必要があります。 彼女が空の値を考慮すると、次の一連のアクションが得られます。

1 + 3 = 4; 4 + null = null; null + 2 = null



1 + 3 = 4; 4 + null = null; null + 2 = null



。

おそらくこれを取得したくないため、集計を計算するときにこのような計算に満足することはまずありません。 そして、データウェアハウスの建設に伴うhemo核はどうなるのでしょうか... Brrrrr ...



データ付きのテーブル。 以下で何度も使用されます：

 create table agg( id int, n int ); insert into agg values( 1, 1 ); insert into agg values( 2, 3 ); insert into agg values( 3, null ); insert into agg values( 4, 2 ); commit;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

NULL値は集約によって無視されます。

 select sum(n) from agg; -- 6
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

COUNT



行カウント関数は、 COUNT(*)



またはCOUNT()



の形式で使用される場合、空の値を考慮します。 ただし、 COUNT()



形式で使用される場合、空の値は無視されます。



定数付き：

 select count(*) from agg; -- 4 select count(1+1) from agg; -- 4 select count(user) from agg; -- 4
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

式あり：

 select count(n) from agg; -- 3 select count(id) from agg; -- 4 select count(abs(n)) from agg; -- 3
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

また、 AVG



などの機能にも注意してください。 空の値を無視するため、フィールドN



結果は(1+3+2)/3



であり、 (1+3+2)/4



はありません。 おそらく、このような平均の計算は必要ありません。 このような問題を解決するには、標準的な解決策がありますNVL



関数を使用します：

 select avg(n) from agg; -- (1 + 3 + 2) / 3 = 2 select avg(nvl(n,0)) from agg; -- (1 + 3 + 0 + 2) / 4 = 1.5
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

集計関数は、空のデータセットに適用される場合、またはNULLのみで構成される場合、



返します。 例外は、行数をカウントするためのREGR_COUNT



およびCOUNT()



関数REGR_COUNT



。 上記の場合、ゼロを返します。



NULLのみのデータセット：

 select sum(n) from agg where n is null; --  select avg(n) from agg where n is null; --  select regr_count(n,n) from agg where n is null; -- 0 select count(n) from agg where n is null; -- 0
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

空のデータセット：

 select sum(n) from agg where 1 = 0; --  select avg(n) from agg where 1 = 0; --  select regr_count(n,n) from agg where 1 = 0; -- 0 select count(n) from agg where 1 = 0; -- 0
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

OLAPのNULL

ユニットに関連付けられている別の機能について簡単に説明します。 クエリの結果としての多次元NULLキューブでは、データの欠如とディメンションによるグループ化の兆候の両方を意味する場合があります。 最もうんざりすることは、これらの2つの低血圧症を視覚で区別できないことです。 幸いなことに、より鋭い目を持つ特別なGROUPING



およびGROUPING_ID



関数があります。 GROUPING()



は、ディメンション列のNULLがこの列によるグループ化の記号を意味する場合は1を返し、特定の値（特にNULL）が含まれる場合は0を返します。 GROUPING_ID



関数はGROUPING_ID



からのビットベクトルです。このノートでは、これは間違いなく不要です。



一般に、多変量解析におけるNULL二元性に関するこのような簡潔で無秩序な情報。 以下は、 GROUPING



の使用例であり、Wellcome Data Warehousing Guideの第21章の詳細です。



便利な機能sqlplus：データを出力するとき、指定された文字列でNULLを置き換えます：

 set null [NULL]
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

多次元キューブのNULL双対性の確認：

 with t as ( --   1  select 'IVAN' customer, 'KEFIR' product, 1 qty from dual union all --    2  select NULL customer, 'MOLOKO' product, 2 qty from dual union all --   2  select 'IVAN' customer, 'MOLOKO' product, 2 qty from dual ) select customer cust, grouping(customer) grp_c , product prod, grouping(product) grp_p , sum(qty) qty from t group by cube(customer, product) order by grp_c, grp_p; CUST GRP_C PROD GRP_P QTY ---- ----- ------ ----- ---- [NULL] 0 MOLOKO 0 2 --    2   IVAN 0 KEFIR 0 1 --   1   IVAN 0 MOLOKO 0 2 --   2   IVAN 0 [NULL] 1 3 --   3    [NULL] 0 [NULL] 1 2 --    2    [NULL] 1 MOLOKO 0 4 --    4   [NULL] 1 KEFIR 0 1 --    1   [NULL] 1 [NULL] 1 5 --    5   
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

舞台裏に残っているもの

それはかなり長いことが判明したため、情報の一部を切り取らなければなりませんでした。 すなわち：

• インデックスのNULL（通常、複合、バイナリ、機能）。 そこでは、テキストよりも多くのリストがあります。
• NULLを操作するための関数の分析：文書化されており、あまりそうではありません。
• リクエストでNULLを処理します（あらゆる種類のケース）
• 他のポイントに言及するのを忘れたのかもしれません。コメントを書いて追加します。

著者の無能に対する批判、追加、およびほのめかしを（文書を参照して）歓迎します。 実際、このためにすべてが開始されました。