等値比較
IN 述語は、OR 演算子で複数の等値比較を結合する省略表現として使用します。以下はその例です。
SELECT Name, Home_State FROM Sample.Person
WHERE Home_State IN ('ME','NH','VT','MA','RI','CT')
上記の文は、Home_State がコンマ区切りリスト内のいずれかの値と等しい場合、True と評価します。リストされる項目には、定数または式を指定できます。
IN 比較では、個々の item の照合タイプに関係なく、scalar-expression に対して定義された照合タイプが使用されます。既定では、文字列データ型フィールドは大文字と小文字が区別されない SQLUPPER 照合で定義されます。現在のネームスペースにおける既定の文字列の照合の定義およびフィールド/プロパティの定義における既定以外のフィールドの照合タイプの指定の詳細は、"InterSystems IRIS の使用法" の “照合” の章を参照してください。
以下の 2 つの例では、照合のマッチングが scalar-expression 照合に基づいていることが示されています。Home_State フィールドは、(大文字と小文字が区別されない) SQLUPPER 照合で定義されます。 したがって、以下の例では、NH Home_State 値が返されます。
SELECT Name, Home_State FROM Sample.Person
WHERE Home_State IN ('ME','nH','VT')
以下の例では、NH Home_State 値は返されません。
SELECT Name, Home_State FROM Sample.Person
WHERE %EXACT(Home_State) IN ('ME','nH','VT')
値のリストに NULL を含めることに意味はありません。NULL は値なしであるため、すべての等式テストに失敗します。IN 述語 (または他の任意の述語) を指定すると、指定されたフィールドのインスタンスのうち NULL であるものがすべて排除されます。これについては、以下の 正しくない (が実行可能な) 例で示しています。
SELECT FavoriteColors FROM Sample.Person
WHERE FavoriteColors IN ($LISTBUILD('Red'),$LISTBUILD('Blue'),NULL)
/* NULL here is meaningless. No FavoriteColor NULL fields returned */
NULL のフィールドを述語の結果セットに含めるための唯一の方法は、以下の例のように IS NULL 述語を指定することです。
SELECT FavoriteColors FROM Sample.Person
WHERE FavoriteColors IN ($LISTBUILD('Red'),$LISTBUILD('Blue')) OR FavoriteColors IS NULL
IN 述語の等値比較に日付または時刻を使用すると、適切なデータ型変換が自動的に実行されます。WHERE フィールドが TimeStamp 型の場合、Date 型または Time 型の値は Timestamp に変換されます。WHERE フィールドが Date 型の場合、TimeStamp 型または String 型の値は Date に変換されます。WHERE フィールドが Time 型の場合、TimeStamp 型または String 型の値は Time に変換されます。
以下の 2 つの例は、同じ等値比較を実行し、同じデータを返します。DOB フィールドは Date データ型です。
SELECT Name,DOB FROM Sample.Person
WHERE DOB IN ({d '1951-02-02'},{d '1987-02-28'})
SELECT Name,DOB FROM Sample.Person
WHERE DOB IN ({ts '1951-02-02 02:37:00'},{ts '1987-02-28 16:58:10'})
詳細は、"日付/時刻文" を参照してください。
%SelectMode
論理形式ではない値に %SelectMode が設定されている場合は、IN 述語値を %SelectMode 形式 (ODBC または表示) で指定する必要があります。このことは主に、日付、時刻、および InterSystems IRIS 形式のリスト (%List) に当てはまります。論理形式で述語値を指定すると、一般に、SQLCODE エラーが発生します。例えば、SQLCODE -146 “[入力された日付を妥当な日付論理値に変換できません]” が返されます。
以下のダイナミック SQL の例の IN 述語では、%SelectMode=1 (ODBC) 形式で日付を指定する必要があります。
SET q1 = "SELECT Name,DOB FROM Sample.Person "
SET q2 = "WHERE DOB IN('1956-03-05','1956-04-08','1956-04-18','1956-12-08')"
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"
サブクエリ比較
サブクエリで IN 述語を使用し、列の値 (あるいは他の式) がサブクエリの行の値と等しいかどうかをテストできます。以下に例を示します。
SELECT Name,Home_State FROM Sample.Person
WHERE Name IN
(SELECT Name FROM Sample.Employee
HAVING Salary < 50000)
サブクエリでは、SELECT リスト内に必ず 1 つの選択項目を持ちます。
以下の例では、IN サブクエリを使用して、Vendor の状態ではない Employee の状態を返します。
SELECT Home_State
FROM Sample.Employee
WHERE Home_State NOT IN (SELECT Address_State FROM Sample.Vendor)
GROUP BY Home_State
次の例では、照合関数表現をサブクエリと共に使用される IN 述語と照合しています。
SELECT Name,Id FROM Sample.Person
WHERE %EXACT(Spouse) NOT IN
(SELECT Id FROM Sample.Person
WHERE Age < 65)
IN は、サブクエリと、リテラル値のコンマ区切りリストの両方を指定することはできません。
IN および %INLIST
IN 述語と %INLIST 述語はどちらも、OR 等値比較に使用する複数の値を指定するために使用できます。%INLIST 述語は、値を %List 構造の要素に一致させるために使用します。ダイナミック SQL では、%INLIST 述語の値は、単一のホスト変数として指定できます。IN 述語の値は、個別のホスト変数として指定する必要があります。したがって、IN 述語の値の数を変更すると、別個のクエリ・キャッシュが作成されることになります。%INLIST には、複数の要素を持つ単一の述語値 %List を指定します。%List 要素の数を変更しても、別個のクエリ・キャッシュは作成されません。%INLIST はまた、桁数の SIZE 引数も提供し、SQL はこれを使用してパフォーマンスを最適化します。このため、多くの場合は IN(val1,val2,val3,..valn) ではなく %INLIST($LISTFROMSTRING(val)) を使用した方が有利です。
%INLIST では等値比較を実行できますが、サブクエリ比較は実行できません。
詳細は、%INLIST を参照してください。