述語の概要
述語の使用法
述語は、True または False のブーリアン値に評価される条件式です。
述語は次のように使用できます。
-
SELECT 文の WHERE 節または HAVING 節で使用し、特定のクエリに関連する行を特定します。すべての述語が HAVING 節で使用できるわけではないことに注意してください。
-
JOIN 操作の ON 節で、結合操作に関連する行を特定します。
-
WHERE CURRENT OF 文の AND 節で使用します。
-
CREATE TRIGGER 文の WHEN 節で使用して、トリガされるアクション・コードを適用するタイミングを決定します。
-
DROP TABLE などの DROP 文で使用して、ターゲットが存在しない場合にエラーが発生しないようにします。
述語のリスト
それぞれの述語の中では、1 つ以上の比較演算子を記号またはキーワードで指定します。InterSystems SQL は以下の比較演算子をサポートします。
比較演算子 | 説明 |
---|---|
= (等しい) <> (等しくない) != (等しくない) > (より大きい) >= (以上) < (より小さい) <= (以下) |
等値比較条件。数値比較または文字列照合順序比較に使用できます。数値比較では、空文字列 ('') は 0 と評価されます。等値比較の NULL は、常に空のセットを返します。代わりに、IS NULL 述語を使用します。"関係演算子" を参照してください。 |
IS [NOT] NULL | フィールドに未定義 (NULL) の値があるかどうかをテストします。"IS NULL" を参照してください。 |
IS [NOT] JSON | 値が JSON 形式の文字列であるか、または JSON 配列や JSON オブジェクトへの oref であるかをテストします。"IS JSON" を参照してください。 |
EXISTS (subquery) | サブクエリを使用して、指定したテーブルに 1 つ以上の行が存在するかどうかをテストします。詳細は、"EXISTS" を参照してください。 |
DROP-command IF EXISTS objectname | 指定したターゲットが存在することを、DROP コマンドの実行の条件とし、これが存在しない場合にエラーにならないようにします。詳細は、"EXISTS" を参照してください。 |
BETWEEN x AND y | BETWEEN 条件は、>= と <= の比較条件を一緒に使用します。一致条件は、指定した 2 つの範囲制限値の間にあることです (2 つの値も含まれます)。"BETWEEN" を参照してください。 |
IN (item1,item2[...,itemn]) IN (subquery) |
フィールド値を、コンマ区切りリスト内のいずれかの項目、またはサブクエリにより返されるいずれかの項目と一致させる等値条件。詳細は、"IN" を参照してください。 |
%INLIST listfield | フィールド値を %List 構造リスト内のいずれかの要素と一致させる等値条件。詳細は、"%INLIST" を参照してください。 |
[ | 包含関係演算子。一致条件は、指定した文字列が含まれることです。包含関係演算子は、EXACT 照合を使用し、大文字と小文字を区別します。値は論理形式で指定する必要があります。 |
] | 後続関係演算子。一致条件は、照合順において指定された項目の後に来ることです。 値は論理形式で指定する必要があります。 |
%STARTSWITH 文字列 | 一致条件は、指定した文字列で始まることです。詳細は、"%STARTSWITH" を参照してください。 |
FOR SOME | ブーリアンの比較条件。FOR SOME 条件は、指定されたフィールドの少なくとも 1 つのデータ値に対して、True である必要があります。詳細は、"FOR SOME" を参照してください。 |
FOR SOME %ELEMENT | %VALUE または %KEY 述語節があるリスト要素比較条件。%VALUE は、リスト内の少なくとも 1 つの要素の値と一致しなければなりません。%KEY は、リスト内の要素の数以下でなければなりません。%VALUE 節と %KEY 節は、他の任意の比較演算子を使用できます。詳細は、"FOR SOME %ELEMENT" を参照してください。 |
LIKE | リテラルおよびワイルドカードを使用したパターン・マッチ条件。リテラル文字の既知の部分文字列を含むか、既知のシーケンス内にいくつかの既知の部分文字列を含むデータ値を返す場合は、LIKE を使用します。LIKE は、大文字と小文字の比較に、そのターゲットの照合を使用します。(包含関係演算子は、EXACT 照合を使用するという点で異なります。)詳細は、"LIKE" を参照してください。 |
%MATCHES | リテラル、ワイルドカード、リスト、および範囲を使用したパターン・マッチ条件。リテラル文字の既知の部分文字列を含むか、指定された文字のリストや範囲にマッチする 1 つ以上のリテラル文字を含むか、既知のシーケンス内にそうしたいくつかの部分文字列を含むデータ値を返す場合は、%MATCHES を使用します。 %MATCHES は、大文字と小文字の比較に EXACT 照合を使用します。詳細は、"%MATCHES" を参照してください。 |
%PATTERN | 文字タイプを使用したパターン・マッチ条件。例えば、'1U4L1",".A' (1 つの大文字、4 つの小文字、1 つのリテラル・コンマ、その後に任意数の大文字または小文字が続くパターン) などです。文字タイプの既知のシーケンスを含むデータ値が返されるようにするには、%PATTERN を使用します。%PATTERN には既知のリテラル文字を指定できますが、特に、データ値は重要でなくても、その値の文字タイプ形式が重要である場合に役立ちます。詳細は、"%PATTERN" を参照してください。 |
ALL ANY SOME |
限定比較条件。詳細は、"ALL"、"ANY"、および "SOME" を参照してください。 |
%INSET %FIND |
抽象的な、プログラムで指定された一時ファイルまたはビットマップ・インデックスを使用して RowId フィールドの値をフィルタ処理できるフィールド値比較条件。%INSET は、単純な比較をサポートします。%FIND は、ビットマップ・インデックスを使用する比較をサポートします。 |
NULL
NULL とは、値が存在しないことです。当然ながら、これはすべてのブーリアン・テストに失敗します。NULL に等しい値、または等しくない値、NULL より大きい値または小さい値はありません。NULL=NULL であっても、述語としては失敗します。IN 述語は一連の OR で連結された等式テストであるので、IN 値リストに NULL を指定する意味はありません。したがって、何らかの述語条件を指定すると、NULL であるそのフィールドのすべてのインスタンスは削除されます。述語条件からの結果セットに NULL フィールドを組み込む唯一の方法は、IS NULL 述語を使用することです。詳細は、以下の例を参照してください。
SELECT FavoriteColors FROM Sample.Person
WHERE FavoriteColors = $LISTBUILD('Red') OR FavoriteColors IS NULL
照合
述語では、フィールドに対して定義された照合タイプが使用されます。既定では、文字列データ型フィールドは大文字と小文字が区別されない SQLUPPER 照合で定義されます。現在のネームスペースにおける既定の文字列の照合を定義し、フィールド/プロパティの定義における既定以外のフィールドの照合タイプを指定することができます。
クエリで照合タイプを指定する場合、比較の両側でそれを指定する必要があります。照合タイプを指定すると、インデックスの使用に影響がある場合があります。詳細は "インデックス照合" を参照してください。
特定の述語比較には、文字列内に埋め込まれている部分文字列 (包含関係演算子 ([)、%MATCHES 述語、および %PATTERN 述語) が含まれます。これらの述語は、必ず EXACT 照合を使用し、そのため必ず大文字と小文字を区別します。一部の照合は空白スペースを文字列の先頭に追加するので、これらの述語はフィールドの既定の照合の後に続く場合、その関数を実行できませんでした。ただし、LIKE 述語は、文字列内に埋め込まれた部分文字列に一致させるワイルドカードを使用できます。LIKE は、フィールドの既定の照合を使用します。これは既定では大文字と小文字を区別しません。
複合述語
条件式の最も単純な形が述語です。条件式は 1 つ以上の述語で構成されます。AND および OR 論理演算子で複数の述語をつなぐことができます。述語の前に単項否定演算子を置くと、述語の意味が反対になります。単項否定演算子は、その直後の述語のみに影響します。述語は、左から右の順に評価されます。述語をまとめるには、括弧を使用します。一連の述語の意味を反転させるには、開き括弧の前に単項否定演算子を付けます。括弧の前後、および括弧と論理演算子の間には空白は不要です。
IN 述語および %INLIST 述語は、複数の OR 等価述語と機能的に同等です。以下の例は同じものです。
SET q1="SELECT Name,Home_State FROM Sample.Person "
SET q2="WHERE Home_State='MA' OR Home_State='VT' OR Home_State='NH'"
SET myquery=q1_q2
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
SET q1="SELECT Name,Home_State FROM Sample.Person "
SET q2="WHERE Home_State IN('MA','VT','NH')"
SET myquery=q1_q2
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
SET list=$LISTBUILD("MA","VT","NH")
SET q1="SELECT Name,Home_State FROM Sample.Person "
SET q2="WHERE Home_State %INLIST(?)"
SET myquery=q1_q2
SET tStatement = ##class(%SQL.Statement).%New()
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute(list)
DO rset.%Display()
FOR SOME %ELEMENT 述語には、論理演算子を含めることができ、論理演算子を使用して他の述語とつなげることもできます。詳細は、以下の例を参照してください。
SELECT Name,FavoriteColors FROM Sample.Person
WHERE FOR SOME %ELEMENT(FavoriteColors)(%VALUE='Red' OR %Value='White'
OR %Value %STARTSWITH 'B')
AND (Name BETWEEN 'A' AND 'F' OR Name %STARTSWITH 'S')
ORDER BY Name
(Name BETWEEN 'A' AND 'F' OR Name %STARTSWITH 'S') の前後の括弧に注意してください。このグループ化の括弧がないと、FOR SOME %ELEMENT 条件は Name %STARTSWITH 'S' に適用されません。
OR を使用したコレクション述語
FOR SOME %ELEMENT はコレクション述語です。OR 論理演算子と共にこの述語を使用することは、以下のように制限されます。OR 論理演算子は、テーブル・フィールドを参照するコレクション述語と、別のテーブル内のフィールドを参照する述語を関連付けるためには使用できません。以下はその例です。
WHERE FOR SOME %ELEMENT(t1.FavoriteColors) (%VALUE='purple')
OR t2.Age < 65
この制限はオプティマイザがインデックスを使用する方法に依存するので、SQL はこの制限を、インデックスがテーブルに追加されるときにのみ実施できます。このタイプの論理は、すべてのクエリで使用しないことを強くお勧めします。
述語と %SelectMode
すべての述語は、論理 (内部保存) データ値を使用してそれらの比較を実行します。ただし、一部の述語は、述語値 (1 つまたは複数) に対して形式モード変換を実行して、それを ODBC 形式または表示形式から論理形式へ変換できます。その他の述語は形式モード変換は実行できないため、必ず論理形式で述語値を指定する必要があります。
形式モード変換を実行する述語は、照合フィールドのデータ型 (DATE または %List) から変換が必要かどうかを判断し、%SelectMode 設定から変換のタイプを決定します。%SelectMode が論理形式以外の値 (%SelectMode=ODBC または %SelectMode=Display) に設定されている場合は、述語値 (1 つまたは複数) を適切な ODBC 形式または表示形式で指定する必要があります。
-
等値述語は、形式モード変換を実行します。InterSystems IRIS は、述語値を論理形式に変換してから、それをフィールド値と照合します。%SelectMode が論理形式以外のモードに設定されている場合は、表示値と論理格納値とが異なるデータ型のために、述語値 (1 つまたは複数) を %SelectMode 形式 (ODBC または表示) で指定する必要があります。例えば、日付、時刻、および %List 形式設定済み文字列などがあります。InterSystems IRIS はこの形式変換を自動的に実行するため、論理形式でこのタイプの述語値を指定すると、通常は SQLCODE エラーとなります。例えば、SQLCODE -146 “[入力された日付を妥当な日付論理値に変換できません]” です (InterSystems IRIS は、指定された論理値が ODBC 値または表示値であると見なし、論理値に変換しようとしますが、成功しません)。影響を受ける述語には、=、<、>、BETWEEN、および IN などがあります。
-
パターン述語は、形式モード変換を実行できません。これは、InterSystems IRIS が述語値を意味のある値に変換できないためです。したがって、述語値は、%SelectMode 設定に関係なく、論理形式で指定する必要があります。述語値 (1 つまたは複数) を ODBC 形式または表示形式で指定すると、通常は、データが一致しないか、意図していないデータと一致することになります。影響を受ける述語には、%INLIST、LIKE、%MATCHES、%PATTERN、%STARTSWITH、[ (包含関係演算子)、および ] (後続関係演算子) などがあります。
%INTERNAL、%EXTERNAL、または %ODBCOUT 形式変換関数を使用すると、述語による操作対象となるフィールドを変換できます。これにより、述語値を別の形式で指定できるようになります。例えば、WHERE %ODBCOut(DOB) %STARTSWITH '1955-' のようにします。ただし、照合フィールドに対して形式変換関数を指定すると、そのフィールドのためのインデックスを使用できなくなります。これは、パフォーマンスに大きな悪影響を及ぼす可能性があります。
以下のダイナミック SQL の例の BETWEEN 述語 (等値述語) では、%SelectMode=1 (ODBC) 形式で日付を指定する必要があります。
SET q1 = "SELECT Name,DOB FROM Sample.Person "
SET q2 = "WHERE DOB BETWEEN '1950-01-01' AND '1960-01-01'"
SET myquery = q1_q2
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=1
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
WRITE !,"End of data"
以下のダイナミック SQL の例では、%STARTSWITH 述語 (パターン述語) は形式モード変換を実行できません。1 つ目の例では、1950 年代の年について、%SelectMode=ODBC の形式で、日付のために %STARTSWITH を指定しようとします。しかし、$HOROLOG 195 で始まる誕生日 (1894 年内の日付) がテーブルに含まれていないため、行は選択されません。
SET q1 = "SELECT Name,DOB FROM Sample.Person "
SET q2 = "WHERE DOB %STARTSWITH '195'"
SET myquery = q1_q2
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=1
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
WRITE !,"End of data"
以下の例では、ODBC形式で 1950 年代の年を選択するために %STARTSWITH を使用できるよう、照合する DOB フィールドに対して %ODBCOut 形式変換関数を使用します。ただし、この使用法では DOB フィールドでインデックスを使用できなくなることに注意してください。
SET q1 = "SELECT Name,DOB FROM Sample.Person "
SET q2 = "WHERE %ODBCOut(DOB) %STARTSWITH '195'"
SET myquery = q1_q2
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=1
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
WRITE !,"End of data"
以下の例では、%STARTSWITH 述語は、論理 (内部) 形式で日付のために %STARTSWITH を指定します。41 で始まる DOB 論理値がある行 (1953 年 4 月 4 日 ($HOROLOG 41000) から 1955 年 12 月 28 日 ($HOROLOG 41999) までの日付) が選択されます。DOB フィールドのインデックスが使用されます。
SET q1 = "SELECT Name,DOB FROM Sample.Person "
SET q2 = "WHERE DOB %STARTSWITH '41'"
SET myquery = q1_q2
SET tStatement = ##class(%SQL.Statement).%New()
SET tStatement.%SelectMode=1
SET qStatus = tStatement.%Prepare(myquery)
IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
SET rset = tStatement.%Execute()
DO rset.%Display()
WRITE !,"End of data"
述語と PosixTime、Timestamp、および Date
等値述語比較は自動的にこれらのさまざまな日付および日付/時刻表現の変換を実行します。この変換は %SelectMode とは関係ありません。したがって、意味のあるすべての比較述語は以下のようになります。
WHERE MyPosixField = MyTimestampField
WHERE MyPosixField < CURRENT_TIMESTAMP
WHERE MyPosixField BETWEEN DATEADD('month',-1,CURRENT_TIMESTAMP) AND $HOROLOG
WHERE MyPosixField BETWEEN DATEADD('day',-1,CURRENT_DATE) AND LAST_DAY(CURRENT_DATE)
%STARTSWITH のようなパターン述語比較は、異なる日付および日付/時刻表現間の変換を実行しません。これらは実際に保存されている日付値に対して機能します。
リテラル置換の抑制
述語引数を二重の括弧で囲むことで、コンパイルの事前解析時にリテラル置換を抑制できます。例えば、LIKE(('abc%')) のようにします。これにより、全体的な選択性や添え字境界の選択性が向上し、クエリのパフォーマンスが向上する場合があります。ただし、同じクエリを別の値で複数回呼び出すことは避けてください。それぞれのクエリ呼び出しでキャッシュされたクエリが個別に作成されることになってしまいます。
例
以下の例は、クエリの WHERE 節における多様な条件の使用を示しています。
SELECT PurchaseOrder FROM MyTable
WHERE OrderTotal >= 1000
AND ItemName %STARTSWITH :partname
AND AnnualOrders BETWEEN 50000 AND 100000
AND City LIKE 'Ch%'
AND CustomerNumber IN
(SELECT CustNum FROM TheTop100
WHERE TheTop100.City='Boston')
AND :minorder > SOME
(SELECT OrderTotal FROM Orders
WHERE Orders.Customer = :cust)