query 式

クエリ式とテーブル式

クエリ式は、1つ以上のテーブル式です。複数のテーブル式は、セット演算子によって結合されます。次の図は、テーブル式とクエリ式の間の関係を示しています。

セット演算子

PROC SQLには、次の集合演算子が用意されています。

OUTER UNION

クエリ結果を連結します。

UNION

両方のクエリ結果から、すべて一意の行を生成します。

EXCEPT

最初のクエリ結果のみに含まれる行を生成します。

INTERSECT

両方のクエリ結果に共通する行を生成します。

セット演算子を含むクエリ式は、次のように評価されます。

• それぞれのテーブル式が評価されて、内部で中間的な結果テーブルが生成されます。
• 各中間結果テーブルは、セット演算子で結ばれたオペランドとなり、式を構成します(たとえば、A UNION B)。
• クエリ式に3つ以上のテーブル式が含まれる場合、最初の2つのテーブル式の結果は、次のセット演算子とオペランドのためのオペランドになります。たとえば、(A UNION B) EXCEPT C、((A UNION B) EXCEPT C) INTERSECT D、などです。
• クエリ式を評価することによって、1つの出力テーブルが生成されます。

式の中のセット演算子は、かっこによって無効にしない限り、次の優先順位の順序に従います。INTERSECTが最初に評価されます。OUTER UNION、UNIONおよびEXCEPTの優先順位は同じです。

PROC SQLは、テーブル式で参照されるテーブルまたはビューの列の数が異なる場合でも、集合演算を実行します。このように動作する理由は、次のとおりです。SQLのANSI規格では、集合演算に関与するテーブルまたはビューの列の数が同じである必要があり、それらの列のデータ型が一致している必要があります。連結されている1つ以上のテーブルまたはビューよりも列数が少ないテーブルまたはビューに対して集合演算を実行した場合、PROC SQLは、適切なデータ型の欠損値を含む列を作成することによって、列が足りないテーブルまたはビューを拡張します。この一時的な変更によって、集合演算を正しく実行することができます。

CORRESPONDING (CORR)キーワード

CORRESPONDINGキーワードは、セット演算子指定した場合にのみ使用します。CORRを指定すると、PROC SQLは、列の位置ではなく、名前によってテーブル式の列を照合します。名前が一致しない列は、OUTER UNION演算子の場合を除き、結果テーブルから除外されます。OUTER UNION演算子を参照してください。

たとえば、2つのテーブル式に対して集合演算を実行した場合、PROC SQLは、1つのテーブル式で最初に指定されたcolumn-name(SELECT句に記述されたcolumn-name)と、もう1つのテーブル式で最初に指定されたcolumn-nameを照合します。CORRを省略した場合、PROC SQLは、列の位置によって列を照合します。

関連項目

• UNION CORRによって異なる変数長を解決する方法

ALLキーワード

セット演算子は、出力テーブルから重複する行を自動的に削除します。オプションのALLキーワードを指定すると、重複行が維持され、実行ステップが1つ減ります。これによって、クエリ式のパフォーマンスが向上します。一意の行のみではなく、テーブル式の実行結果のすべての行を表示する場合に、このキーワードを使用します。ALLキーワードは、セット演算子も指定した場合にのみ使用します。

OUTER UNION演算子

OUTER UNIONの実行は、SETステートメントを使用したSAS DATAステップの実行と非常によく似ています。OUTER UNIONは、テーブル式の中間結果を連結します。したがって、1番目のテーブル式によって生成されたすべての行に、2番目のテーブル式によって生成されたすべての行が連結されて、クエリ式の結果テーブルに格納されます。同じ名前の列は、別の列として結果テーブルに含まれます。

たとえば、次のクエリ式は、ME1テーブルとME2テーブルを連結しますが、類似する名前を持つ列を重ね合わせません。ME1テーブルとME2テーブルのOUTER UNIONに、結果を示します。

data me1;
   input IDnum $ Jobcode $ Salary Bonus;
   datalines;
1400   ME1         29769  587
1403   ME1         28072  342
1120   ME1         28619  986
1120   ME1         28619  986
;

data me2;
   input IDnum $ Jobcode $ Salary;
   datalines;
1653   ME2         35108
1782   ME2         35345
1244   ME2         36925
;

ME1テーブルとME2テーブル

proc sql;
   title 'ME1 and ME2: OUTER UNION';
   select *
      from me1
   outer union
   select *
      from me2;
quit;

ME1テーブルとME2テーブルのOUTER UNION

OUTER UNIONセット演算子を使用したテーブルの連結は、和結合の実行に類似しています。詳細については、和結合を参照してください。

同じ名前を持つ列を重ね合わせるには、CORRESPONDINGキーワードを使用します。

proc sql;
   title 'ME1 and ME2: OUTER UNION CORRESPONDING';
   select *
      from me1
   outer union corr
   select *
      from me2;
quit;

OUTER UNION CORRESPONDING

連結によって得られたテーブルについて、次の点に注目してください。

• OUTER UNION CORRESPONDINGは、一致しないすべての列を維持します。
• 同じ名前を持つ列については、1番目のテーブル式の結果から値が欠損している場合、2番目のテーブル式の同じ名前の列の値が挿入されます。
• 結果テーブルにすべての行を含めることがOUTER UNION演算子のデフォルトのアクションであるため、この演算子ではALLキーワードは使用されません。そのため、IDnumが1120であるテーブルME1の両方の行が出力に表示されます。

UNION演算子

UNION演算子は、両方のテーブル式から得られた、すべて一意の行を含むテーブルを生成します。つまり、出力テーブルには、1番目のテーブル式、2番目のテーブル式、または両方のテーブル式から生成された行が含まれます。

列は、列名とは無関係に、テーブル内の位置によって追加されます。ただし、対応する列のデータ型は一致している必要があります。そうでない場合、結合は実行されません。PROC SQLは、警告メッセージを発行して実行を停止します。

出力テーブルの列の名前は、1番目のテーブル式の列に名前がない場合(式などの場合)を除き、1番目のテーブル式の列の名前になります。1番目のテーブル式の列に名前がない場合、出力テーブルの列名は、2番目のテーブル式のそれぞれの列の名前になります。

次の例では、PROC SQLによって2つのテーブルを結合しています。

proc sql;
   title 'ME1 and ME2: UNION';
   select *
      from me1
   union
   select *
      from me2;
quit;

ME1テーブルとME2テーブルのUNION

次の例では、ALLを指定して、ME1の重複行を含めています。さらに、ALLは、PROC SQLに1つのパスのみを実行するよう指定することによって、順序を変更します。そのため、ME2の値は、ME1の値に単純に追加されます。

proc sql;
   title 'ME1 and ME2: UNION ALL';
   select *
      from me1
   union all
   select *
      from me2;
quit;

UNION ALL

その他の例については、2つのテーブルを組み合わせるを参照してください。

UNION CORRによって異なる変数長を解決する方法

同じ名前の1つ以上の文字変数の長さが異なるソース上で、UNIONまたはOUTER UNIONと共にCORRが使用される場合、出力テーブル内の変数の長さは、寄与するソースの種類に基づいて決定されます。説明のために、2つのソースが使用され、各ソースに異なる長さの文字変数VAR1が含まれているとします。ここで、次のSQLプロシジャステップについて考えます。

proc sql;
   create table NewTable as
      select * from Source_A
      outer union corr
      select * from Source_B;
quit;

source_AおよびSource_Bには、データセット、DATAステップビューまたはSQLビューを指定できます。VAR1などの文字変数にはデフォルトの長さが使用されます。次のテーブルに、さまざまなソースを使用した場合のテーブルNewTableのVAR1の長さの決定方法を示します。

次の表に、例をいくつか示します。

SQLプロシジャステップでは、いずれかまたは両方のSELECT句でLENGTH=を使用してVAR1の長さを明示的に指定した場合、テーブルNewTableのVAR1の長さは次のように決定されます。

• 特定のSELECT句でVAR1のLENGTH=が指定されている場合、NewTableでのVAR1の長さは、そのSELECT句で指定された長さになります。
• 両方のSELECT句でVAR1のLENGTH=が指定されている場合、NewTableのVAR1の長さは、最初のSELECT句で指定された長さとなります。

これらのルールは、ソースの種類に関係なく適用されます。たとえば、次のSQLプロシジャステップについて考えてみます。

proc sql;
   create table NewTable as
      select * from Source_A
      outer union corr
      select VAR1 length=18, ... from Source_B;
quit;

この場合、Source_AのVAR1の長さがより長い場合でも、NewTableのVAR1の長さは18です。Source_AのVAR1の長さが18より大きい場合、Source_AのVAR1値は18に切り捨てられます。

EXCEPT演算子

1番目のテーブル式のEXCEPT演算子は、2番目のテーブル式の結果を除く、一意の行を含む出力テーブルを生成します。1番目のテーブル式の中間結果に、2番目のテーブル式の中間結果に含まれない行が少なくとも1つ出現した場合、1番目のテーブル式のその行が結果テーブルに含まれます。

次の例のIn_USAテーブルには、アメリカ内外の都市への航空便が含まれています。Out_USAテーブルには、アメリカ以外の都市への航空便のみが含まれています。

data in_usa;
   input Flight $ Dest $;
   datalines;
145 ORD
156 WAS
188 LAX
193 FRA
207 LON
;
data out_USA;
   input Flight $ Dest $;
   datalines;
193 FRA
207 LON
311 SJA
;

EXCEPTの例のソーステーブル

次の例では、Out_USAに含まれていないIn_USAの行のみが返されます。

proc sql;
    title 'Flights from In_USA Only';
    select * from in_usa
    except
    select * from out_usa;
quit;

In_USAのみの航空便

INTERSECT演算子

INTERSECT演算子は、両方のテーブルに共通する行を含む出力テーブルを生成します。たとえば、次の例では、上で示したIn_USAテーブルとOut_USAテーブルを使用して、両方のテーブルに含まれる行を返しています。

proc sql;
    title 'Flights from Both In_USA and Out_USA';
    select * from in_usa
    intersect
    select * from out_usa;
quit;

In_USAとOut_USAの両方の航空便