概要
オプションの WHERE 節は、以下の目的のために使用できます。
WHERE 節は、1 つまたは複数の述語を指定し、SELECT クエリまたはサブクエリで検索されたデータを制限 (行をフィルタ削除) するために使用するのが最も一般的です。UPDATE コマンド、DELETE コマンド、または INSERT (あるいは INSERT OR UPDATE) コマンドの結果セット SELECT で WHERE 節を使用することもできます。
WHERE 節は、クエリ選択から特定の行を適格とするか、もしくは不適格とします。適格な行とは、condition-expression が True である行です。condition-expression は、1 つ以上の論理テスト (熟語) です。AND および OR 論理演算子で複数の述語をつなぐことができます。詳細と制約については、“述語および論理演算子” を参照してください。
述語に除算が含まれており、除数がゼロまたは NULL になりうる値がデータベースに存在する場合、評価の順序に依存してゼロによる除算を回避することはできません。その代わり、CASE 文を使用してリスクを抑制します。
WHERE 節は、サブクエリを含む condition-expression を指定できます。サブクエリは括弧で囲む必要があります。
WHERE 節は、= (内部結合) シンボル結合演算子を使用して、2 つのテーブルの間の明示的な結合を指定できます。詳細は、このドキュメントの "JOIN" のページを参照してください。
WHERE 節は、矢印構文 (–>) 演算子を使用して、ベース・テーブルおよび別のテーブルのフィールドの間の明示的な結合を指定できます。詳細は、"InterSystems SQL の使用法" の "暗黙結合" を参照してください。
フィールドの指定
WHERE Age > 21 など、WHERE 節の最も簡潔な形式で、フィールドと値を比較する述語を指定します。有効なフィールド値としては、列名 (WHERE Age > 21) や、%ID、%TABLENAME、または %CLASSNAME や、列名を指定するスカラ関数 (WHERE ROUND(Age,-1)=60)、列名を指定する照合関数 (WHERE %SQLUPPER(Name) %STARTSWITH ' AB') があります。
列番号でフィールドを指定することはできません。
テーブルを再コンパイルすると、RowID フィールドの名前が変わる可能性があるため、WHERE 節では RowID を名前で参照しないようにする必要があります (例 : WHERE ID=22)。代わりに、%ID 疑似列名を使用して RowID を参照します (例 : WHERE %ID=22)。
列エイリアスでフィールドを指定できません。これを試行すると、SQLCODE -29 エラーが生成されます。ただし、サブクエリを使用して列エイリアスを定義してから、このエイリアスを WHERE 節で使用できます。例えば以下のようになります。
SELECT Interns FROM
(SELECT Name AS Interns FROM Sample.Employee WHERE Age<21)
WHERE Interns %STARTSWITH 'A'
集約フィールドを指定できません。これを試行すると、SQLCODE -19 エラーが生成されます。ただし、サブクエリの使用によって、集約関数値を WHERE 節で指定することはできます。例えば以下のようになります。
SELECT Name,Age,AvgAge
FROM (SELECT Name,Age,AVG(Age) AS AvgAge FROM Sample.Person)
WHERE Age < AvgAge
ORDER BY Age
整数と文字列
整数データ型として定義されているフィールドを数値と比較する場合は、比較を実行する前に数値はキャノニック形式に変換されます。例えば、WHERE Age=007.00 は WHERE Age=7 として解析されます。この変換は、すべてのモードで発生します。
表示モードで、整数データ型として定義されているフィールドを文字列と比較する場合は、文字列は数値として解釈されます。例えば、空文字列 ('')は、数値以外の文字列と同様に、数値 0 と解析されます。この解析は、文字列を数値として処理するための ObjectScript 規則に従っています。例えば、WHERE Age='twenty' は WHERE Age=0、WHERE Age='20something' は WHERE Age=20 として解析されます。詳細は、"ObjectScript の使用法" の “データ型とデータ値” の章の "数値としての文字列" を参照してください。SQL は、この解析を表示モードでのみ実行します。論理モードまたは ODBC モードでは、整数を文字列値と比較すると NULL が返されます。
一重引用符を含む文字列のフィールドを比較するには、一重引用符を二重にします。例えば、WHERE Name %STARTSWITH 'O''' は、Obama でなく O’Neil および O’Connor を返します。
日付と時刻
InterSystems SQL では、日付と時刻の比較および保存には論理モードの内部表現が使用されています。日付と時刻は、論理モード、表示モード、または ODBC モードで返すことができます。例えば、1944 年 9 月 28 日は、論理モードでは 37891、表示モードでは 09/28/1944、ODBC モードでは 1944-09-28 と表現されます。condition-expression で日付または時刻を指定する場合に、SQL モードと日付形式または時刻形式が一致していなかったり、日付値または時刻値が無効であったりするとエラーが発生することがあります。
WHERE 節の condition-expression では、現在のモードに一致する日付形式または時刻形式を使用する必要があります。例えば、論理モードのときに、誕生日が 2005 年であるレコードを返すには、WHERE 節を WHERE DOB BETWEEN 59901 AND 60265 とします。表示モードの場合は、この WHERE 節を WHERE DOB BETWEEN '01/01/2005' AND '12/31/2005' とします。
condition-expression の日付形式または時刻形式が表示モードと一致していないと、エラーが発生します。
-
表示モードまたは ODBC モードで日付データを不適切な形式で指定すると、SQLCODE -146 エラーが生成されます。時刻データを不適切な形式で指定すると、SQLCODE -147 エラーが生成されます。
-
論理モードで日付データまたは時刻データを不適切な形式で指定すると、エラーは生成されませんが、データが何も返されないか、意図しないデータが返されます。これは、表示形式や ODBC 形式の日付または時刻が、論理モードでは日付値または時刻値として解析されないためです。WHERE DOB BETWEEN 37500 AND 38000 AND DOB <> '1944-09-28' という WHERE 節は、論理モードで実行すると意図しないデータを返します。ここでは、<> 述語で DOB=37891 (1944 年 9 月 28 日) の除外を指定していますが、実際にはこの日付を含む DOB 値の範囲が返されます。
無効な日付値または時刻値を指定した場合も、SQLCODE -146 または -147 エラーが返されます。無効な日付とは、表示モードや ODBC モードで指定はできても、InterSystems IRIS が同等の論理モード値に変換できない日付です。例えば、ODBC モードで WHERE DOB > '1830-01-01' を指定すると、1840 年 12 月 31 日より前の日付値は InterSystems IRIS で処理できないので SQLCODE -146 エラーが生成されます。また、ODBC モードで WHERE DOB BETWEEN '2005-01-01' AND '2005-02-29' と指定すると、2005 年はうるう年ではないので、この場合も SQLCODE -146 エラーが生成されます。
論理モードでは、表示モードや ODBC モードの値は日付値または時刻値として解析されないので、これらのモードの値は検証されません。したがって、論理モードで WHERE DOB > '1830-01-01' のような WHERE 節を指定しても、エラーは返されません。
ストリーム・フィールド
ほとんどの場合、WHERE 節の述語でストリーム・フィールドを使用することはできません。これを使用すると、SQLCODE -313 エラーが発生します。ただし、以下のストリーム・フィールドの使用は WHERE 節で許可されています。
-
ストリーム NULL テスト : streamfield IS NULL 述語または streamfield IS NOT NULL 述語を指定できます。
-
ストリームの長さのテスト : WHERE 節の述語で CHARACTER_LENGTH(streamfield)、CHAR_LENGTH(streamfield)、または DATALENGTH(streamfield) 関数を指定できます。
-
ストリーム部分文字列のテスト : WHERE 節の述語で SUBSTRING(streamfield,start,length) 関数を指定できます。
リスト構造
InterSystems IRIS は、リスト構造のデータ型 %List (データ型クラス %Library.List) をサポートしています。これは圧縮バイナリ形式であり、InterSystems SQL で対応するネイティブなデータ型にマップしません。また、データ型 VARBINARY に対応しており、その MAXLEN の既定値は 32749 です。このため、ダイナミック SQL では、WHERE 節での比較に %List データを使用できません。詳細は、このドキュメントのリファレンスの "データ型" を参照してください。
構造化されたリスト・データを参照するには、%INLIST 述語または FOR SOME %ELEMENT 述語を使用します。
condition-expression でリスト・フィールドのデータ値を使用するには、%EXTERNAL を使用して、リストの値と述語を比較できます。例えば、FavoriteColors リスト・フィールドの値が 1 つの要素 'Red' で構成されているレコードをすべて返すには、以下のようにします。
SELECT Name,FavoriteColors FROM Sample.Person
WHERE %EXTERNAL(FavoriteColors)='Red'
%EXTERNAL によってリストが DISPLAY 形式に変換されると、表示されるリスト項目は、空白スペースで区切られます。この “スペース” は実際には、CHAR(13) と CHAR(10) という 2 つの非表示文字です。condition-expression をリスト内の複数の要素に対して使用するには、これらの文字を指定する必要があります。例えば、FavoriteColors リスト・フィールドの値が 2 つの要素 'Orange' と 'Black' で (その順に) 構成されているレコードをすべて返すには、以下のようにします。
SELECT Name,FavoriteColors FROM Sample.Person
WHERE %EXTERNAL(FavoriteColors)='Orange'||CHAR(13)||CHAR(10)||'Black'
述語のリスト
SQL の述語は以下のカテゴリに分類されます。
述語の大文字と小文字の区別
述語では、フィールドに対して定義された照合タイプが使用されます。既定では、文字列データ型フィールドは大文字と小文字が区別されない SQLUPPER 照合で定義されます。現在のネームスペースにおける既定の文字列の照合の定義およびフィールド/プロパティの定義における既定以外のフィールドの照合タイプの指定の詳細は、"InterSystems IRIS の使用法" の “照合” の章を参照してください。
%INLIST、包含関係演算子 ([)、%MATCHES、および %PATTERN 述語は、フィールドの既定の照合を使用しません。常に大文字と小文字を区別する EXACT 照合が使用されます。
2 つのリテラル文字列の熟語の比較は、常に大文字小文字を区別します。
述語の条件と %NOINDEX
述語の条件の前に %NOINDEX キーワードを置くと、クエリ・オプティマイザが条件でインデックスを使用することを防ぐことができます。これは、多数の行を満たす範囲条件を指定する場合に最も便利です。例えば、WHERE %NOINDEX Age >= 1 のようにします。詳細は、"SQL 最適化ガイド" の "%ALLINDEX、%IGNOREINDEX、%NOINDEX の使用法" を参照してください。
異常値に対する述語条件
ダイナミック SQL クエリ内の WHERE 節が NULL 以外の異常値を選択する場合、異常値リテラルを二重括弧で囲むことでパフォーマンスを大幅に向上させることができます。この二重括弧により、ダイナミック SQL は最適化の際に Outlier Selectivity (異常値の選択性) を使用します。例えば、企業がマサチューセッツ (MA) に所在している場合、従業員の大半はマサチューセッツに居住していることになります。Employees テーブルの Home_State フィールドでは、'MA' が異常値です。この値を最適化して選択するには、WHERE Home_State=(('MA')) を指定する必要があります。
この構文は、埋め込み SQL やビュー定義では使用すべきではありません。埋め込み SQL またはビュー定義では、Outlier Selectivity が常に使用され、特別なコーディングは不要です。
ダイナミック SQL クエリ内の WHERE 節は、NULL の異常値に対する最適化を自動的に実行します。例えば、WHERE FavoriteColors IS NULL などの節です。NULL が異常値である場合、IS NULL 述語と IS NOT NULL 述語に対する特別なコーディングは不要です。
Outlier Selectivity は、テーブルのチューニング・ユーティリティの実行により決定されます。
BETWEEN 述語
BETWEEN 比較演算子では、構文 BETWEEN lowval AND highval で指定された範囲内にあるデータ値を選択できます。この範囲には、lowval 値と highval 値も含まれます。これは、「以上」演算子と「以下」演算子の組み合わせと同じ働きをします。この比較は、以下の例を参照してください。
SELECT Name,Age FROM Sample.Person
WHERE Age BETWEEN 18 AND 21
これにより、Sample.Person テーブルの Age 値が 18 ~ 21 の範囲にあるすべてのレコードを返します。BETWEEN 値は昇順で指定する必要があることに注意してください。BETWEEN 21 AND 18 のような述語はレコードを返しません。
ほとんどの述語と同様、以下の例に示すように、BETWEEN は NOT 論理演算子を使用して反転させることができます。
SELECT Name,Age FROM Sample.Person
WHERE Age NOT BETWEEN 20 AND 55
ORDER BY Age
これにより、Sample.Person テーブルの Age 値が 20 より 小さく 55 より大きいすべてのレコードを返します。
BETWEEN は通常、数値順に照合を行う数値の範囲に使用します。ただし、BETWEEN は、任意のデータ型の値の照合順範囲に使用できます。
BETWEEN はマッチングの対象となる列と同じ照合タイプを使用します。既定では、文字列データ型の照合は大文字と小文字が区別されません。
詳細は、このドキュメントのリファレンスの "BETWEEN" 述語の説明を参照してください。