WHERE (SQL)
構文
SELECT fields
FROM table
WHERE condition-expression
引数
引数 | 説明 |
---|---|
condition-expression | どのデータ値が取得されるかを規定する 1 つまたは複数のブーリアン述語で構成される式。 |
概要
オプションの WHERE 節は、以下の目的のために使用できます。
-
どのデータ値が返されるかを制限する述語を指定するため。
-
2 つのテーブル間の明示結合を指定するため。
-
ベース・テーブルと別のテーブルのフィールドの間の暗黙結合を指定するため。
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 節は、矢印構文 (–>) 演算子を使用して、ベース・テーブルおよび別のテーブルのフィールドの間の明示的な結合を指定できます。詳細は、"暗黙結合 (矢印構文)" を参照してください。
フィールドの指定
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 として解析されます。詳細は、"数値としての文字列" を参照してください。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'
変数
WHERE 節の述語は、以下を指定できます。
%TABLENAME または %CLASSNAME 疑似フィールド変数キーワード。%TABLENAME は、現在のテーブル名を返します。%CLASSNAME は、現在のテーブルに対応するクラスの名前を返します。クエリが複数のテーブルを参照する場合には、キーワードの接頭語としてテーブルのエイリアスを指定できます。例えば、SUB_ACCESSIBLE_FILE のように指定します。
以下の 1 つ以上の ObjectScript 特殊変数 (またはそれらの省略形) : $HOROLOG、$JOB、$NAMESPACE、$TLEVEL、$USERNAME、$ZHOROLOG、$ZJOB、$ZNSPACE、$ZPI、$ZTIMESTAMP、$ZTIMEZONE、$ZVERSION。
述語のリスト
SQL の述語は以下のカテゴリに分類されます。
述語の大文字と小文字の区別
述語では、フィールドに対して定義された照合タイプが使用されます。既定では、文字列データ型フィールドは大文字と小文字が区別されない SQLUPPER 照合で定義されます。現在のネームスペースにおける既定の文字列の照合を定義し、フィールド/プロパティの定義における既定以外のフィールドの照合タイプを指定することができます。
%INLIST、包含関係演算子 ([)、%MATCHES、および %PATTERN 述語は、フィールドの既定の照合を使用しません。常に大文字と小文字を区別する EXACT 照合が使用されます。
2 つのリテラル文字列の熟語の比較は、常に大文字小文字を区別します。
述語の条件と %NOINDEX
述語の条件の前に %NOINDEX キーワードを置くと、クエリ・オプティマイザが条件でインデックスを使用することを防ぐことができます。これは、多数の行を満たす範囲条件を指定する場合に最も便利です。例えば、WHERE %NOINDEX Age >= 1 のようにします。詳細は、"%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 は、テーブルのチューニング・ユーティリティの実行により決定されます。
等値比較述語
以下は、使用できる等値比較述語です。
述語 | 処理 |
---|---|
= | 等しい |
<> | 等しくない |
!= | 等しくない |
> | より大きい |
< | より小さい |
>= | 以上 |
<= | 以下 |
以下はその例です。
SELECT Name, Age FROM Sample.Person
WHERE Age < 21
SQL は照合 (値がソートされる順番) という点から比較演算子を定義します。まったく同様の方法で照合する場合の 2 つの値は等しくなります。2 つ目の値の後に照合される場合、値は別の値よりも大きくなります。文字列フィールドの照合は、フィールドの既定の照合を利用します。InterSystems IRIS の既定の照合は大文字と小文字を区別しません。そのため、2 つの文字列フィールドの値の比較または文字列フィールド値と文字列リテラルとの比較では、既定では大文字と小文字は区別されません。例えば、Home_State フィールドの値が大文字の 2 文字の文字列の場合、以下のようになります。
式 | 値 |
---|---|
'MA' = Home_State | 値 MA に対して True |
'ma' = Home_State | 値 MA に対して True |
'VA' < Home_State | 値 VT、WA、WI、WV、WY に対して True |
'ar' >= Home_State | 値 AK、AL、AR に対して True |
ただし、2 つのリテラル文字列の比較では大文字と小文字が区別され、WHERE 'ma'='MA' は常に FALSE です。
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" 述語を参照してください。
IN および %INLIST 述語
IN 述語は、構造化されていない一連の項目に値を一致させるために使用されます。この構文は以下のとおりです。
WHERE field IN (item1,item2[,...])
等式テストと同様に、照合が IN 比較に適用されます。IN は、フィールドの既定の照合を使用します。既定では、フィールド文字列の値の比較では大文字と小文字が区別されません。
%INLIST 述語は、値を InterSystems IRIS リスト構造の要素に一致させるための InterSystems IRIS の拡張機能です。この構文は以下のとおりです。
WHERE item %INLIST listfield
%INLIST は EXACT 照合を使用します。そのため、既定では、%INLIST の文字列比較では大文字と小文字が区別されます。
どちらの述語を使用しても、等値比較やサブクエリ比較を実行できます。
部分文字列述語
以下を使用してフィールドの値と部分文字列を比較できます。
述語 | 処理 |
---|---|
%STARTSWITH | 値は、指定された部分文字列で始まる必要があります。 |
[ | 包含関係演算子。値は、指定された部分文字列を含む必要があります。 |
%STARTSWITH 述語
InterSystems IRIS %STARTSWITH 比較演算子により、文字列や数字の先頭文字列との部分的マッチングを実行できます。以下の例は、%STARTSWITH を使用して、Name の値が “S” で始まるレコードを選択します。
SELECT Name,Age FROM Sample.Person
WHERE Name %STARTSWITH 'S'
他の文字列フィールドの比較と同様に、%STARTSWITH の比較はフィールドの既定の照合を使用します。既定では、文字列フィールドでは大文字と小文字が区別されません。以下はその例です。
SELECT Name,Home_City,Home_State FROM Sample.Person
WHERE Home_City %STARTSWITH Home_State
詳細は、"%STARTSWITH" を参照してください。
包含関係演算子 ([)
包含関係演算子は開始ブラケット記号 ([) です。これを使用して、部分文字列 (文字列または数値) とフィールドの値の任意の部分とのマッチングができます。比較は常に大文字と小文字が区別されます。以下の例は、包含関係演算子を使用して、Name の値に “S” を含むレコードを選択します。
SELECT Name, Age FROM Sample.Person
WHERE Name [ 'S'
NULL 述語
定義されていない値を検出します。すべての NULL 値またはすべての NULL でない値を検出できます。NULL 述語には以下の構文があります。
WHERE field IS [NOT] NULL
NULL 述語条件は、WHERE 節のストリーム・フィールドで使用可能な述語の 1 つです。
詳細は、"NULL" を参照してください。
EXISTS 述語
サブクエリが空のセットを評価するかどうかをテストするために、サブクエリを使用して処理します。
SELECT t1.disease FROM illness_tab t1 WHERE EXISTS
(SELECT t2.disease FROM disease_registry t2
WHERE t1.disease = t2.disease
HAVING COUNT(t2.disease) > 100)
詳細は、"EXISTS" を参照してください。
FOR SOME 述語
WHERE 節で FOR SOME 述語を使用して、1 つ以上のフィールド値の条件テストに基づいてレコードを返すかどうかを判断できます。この述語の構文は以下のとおりです。
FOR SOME (table [AS t-alias]) (fieldcondition)
FOR SOME は、fieldcondition が True に評価される必要があるということを指定します。指定された条件に 1 つ以上のフィールド値が一致する必要があります。table には、単一のテーブル、またはコンマ区切りのテーブルのリストを指定可能であり、各テーブルにはオプションでテーブル・エイリアスを指定できます。fieldcondition には、指定された table 内の 1 つ以上のフィールドのために 1 つ以上の条件を指定します。table 引数と fieldcondition 引数は、どちらも括弧で区切る必要があります。
以下の例では、FOR SOME 述語を使用して、結果セットを返すかどうか決定しています。
SELECT Name,Age AS AgeWithWorkers
FROM Sample.Person
WHERE FOR SOME (Sample.Person) (Age<65)
ORDER BY Age
前述の例では、少なくとも 1 つのフィールドに、指定された年よりも小さい Age 値が含まれる場合、すべてのレコードが返されます。それ以外の場合、レコードは返されません。
詳細は、"FOR SOME" を参照してください。
FOR SOME %ELEMENT 述語
WHERE 節の FOR SOME %ELEMENT 述語には、以下の構文を使用します。
FOR SOME %ELEMENT(field) [AS e-alias] (predicate)
FOR SOME %ELEMENT 述語は、指定された predicate 節の値と field の要素を照合します。SOME キーワードは、field の要素の最低 1 つが、指定した predicate 条件を満たす必要があることを指定します。predicate には、キーワード %VALUE または %KEY を指定できます。
FOR SOME %ELEMENT 述語はコレクション述語です。
詳細は、"FOR SOME %ELEMENT" を参照してください。
LIKE、%MATCHES、および %PATTERN 述語
これらの 3 つの述語を使用してパターン・マッチングを実行できます。
-
LIKE では、リテラルとワイルドカードを使用してパターン・マッチを実行できます。リテラル文字の既知の部分文字列、または既知のシーケンス内にいくつかの既知の部分文字列を含むデータ値を返す場合は、LIKE を使用します。LIKE は、大文字と小文字の比較に、そのターゲットの照合を使用します。
-
%MATCHES では、リテラル、ワイルドカード、リスト、および範囲を使用してパターン・マッチを実行できます。リテラル文字の既知の部分文字列、指定された文字のリストまたは範囲にマッチする 1 つ以上のリテラル文字、または既知のシーケンス内にそうしたいくつかの部分文字列を含むデータ値を返す場合は、%MATCHES を使用します。 %MATCHES は、大文字と小文字の比較に EXACT 照合を使用します。
-
%PATTERN を使用すると、文字タイプのパターンを指定できます。例えば、'1U4L1",".A' (1 つの大文字、4 つの小文字、1 つのリテラル・コンマ、その後に任意数の大文字または小文字が続くパターン) などです。文字タイプの既知のシーケンスを含むデータ値を返す場合は、%PATTERN を使用します。%PATTERN には既知のリテラル文字を指定できますが、特に、データ値は重要でないけれども、その値の文字タイプ形式が重要である場合に役立ちます。
文字列の最初の文字で比較を実行するには、%STARTSWITH 述語を使用します。
述語および論理演算子
AND および OR 論理演算子で複数の述語を連結することができます。括弧を使用すると複数の熟語をグループにできます。InterSystems IRIS は、定義済みのインデックスおよび他の最適化を使用して WHERE 節の実行を最適化するため、AND および OR 論理演算子でつながれた述語が評価される順序は予測できません。このような理由から、複数の述語の指定順序によるパフォーマンスへの影響は、ほとんどないか、まったくありません。熟語を厳密に左から右に評価する必要がある場合は、CASE 文を使用できます。
OR 論理演算子は、テーブル・フィールドを参照する FOR SOME %ELEMENT コレクション述語と、別のテーブル内のフィールドを参照する述語を関連付けるためには使用できません。以下はその例です。
WHERE FOR SOME %ELEMENT(t1.FavoriteColors) (%VALUE='purple')
OR t2.Age < 65
この制限はオプティマイザがインデックスを使用する方法に依存するので、SQL はこの制限を、インデックスがテーブルに追加されるときにのみ実施できます。すべてのクエリでこのタイプの論理は使用しないことを強くお勧めします。
詳細は、"論理演算子" を参照してください。