IN (SQL)
Synopsis
scalar-expression IN (item1,item2[,...])
scalar-expression IN (subquery)
Arguments
Argument | Description |
---|---|
scalar-expression | A scalar expression (most commonly a data column) whose values are being compared with a comma-separated list of values or the result set generated by the subquery. |
item | One or more literal values, input host variables, or expressions that resolve to a literal value. List in any order, separate with commas. |
subquery | A subquery, enclosed in parentheses, which returns a result set from a single column that is used for the comparison with scalar-expression. |
Description
The IN predicate is used for matching a value to an unstructured series of items. Typically, it compares column data values to a comma-separated list of values. IN can perform equality comparisons and subquery comparisons.
Like most predicates, IN can be inverted using the NOT logical operator. Neither IN nor NOT IN can be used to return NULL fields. To return NULL fields use IS NULL.
IN can be used wherever a predicate condition can be specified, as described in Overview of Predicates.
Equality Comparison
The IN predicate can serve as shorthand for the use of multiple equality comparisons linked together with the OR operator. For instance:
SELECT Name, Home_State FROM Sample.Person
WHERE Home_State IN ('ME','NH','VT','MA','RI','CT')
evaluates true if Home_State equals any of the values in the comma-separated list. The listed items can be constants or expressions.
IN comparisons use the collation type defined for the scalar-expression, regardless of the collation type of the individual items. By default, string data type fields are defined with SQLUPPER collation, which is not case-sensitive. You can define the string collation default for the current namespace and specify a non-default field collation type when defining a field/property.
The following two examples show that collation matching is based on the scalar-expression collation. The Home_State field is defined with SQLUPPER (not case-sensitive) collation. Therefore, the following example returns NH Home_State values:
SELECT Name, Home_State FROM Sample.Person
WHERE Home_State IN ('ME','nH','VT')
The following example does not return NH Home_State values:
SELECT Name, Home_State FROM Sample.Person
WHERE %EXACT(Home_State) IN ('ME','nH','VT')
It is not meaningful to include NULL in the list of values. NULL is the absence of a value, and therefore fails all equality tests. Specifying an IN predicate (or any other predicate) eliminates any instances of the specified field that are NULL. This is shown in the following incorrect (but executable) example:
SELECT FavoriteColors FROM Sample.Person
WHERE FavoriteColors IN ($LISTBUILD('Red'),$LISTBUILD('Blue'),NULL)
/* NULL here is meaningless. No FavoriteColor NULL fields returned */
The only way to include a field with NULL in the predicate result set is to specify the IS NULL predicate, as shown in the following example:
SELECT FavoriteColors FROM Sample.Person
WHERE FavoriteColors IN ($LISTBUILD('Red'),$LISTBUILD('Blue')) OR FavoriteColors IS NULL
When dates or times are used for IN predicate equality comparisons, the appropriate data type conversions are automatically performed. If the WHERE field is type TimeStamp, values of type Date or Time are converted to Timestamp. If the WHERE field is type Date, values of type TimeStamp or String are converted to Date. If the WHERE field is type Time, values of type TimeStamp or String are converted to Time.
The following examples both perform the same equality comparisons and return the same data. The DOB field is of data type 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'})
For further details refer to Date and Time Constructs.
%SelectMode
If %SelectMode is set to a value other than Logical format, the IN predicate values must be specified in the %SelectMode format (ODBC or Display). This applies mainly to dates, times, and InterSystems IRIS format lists (%List). Specifying predicate values in Logical format commonly results in an SQLCODE error. For example, SQLCODE -146 “Unable to convert date input to a valid logical date value”.
In the following Dynamic SQL example, the IN predicate must specify dates in %SelectMode=1 (ODBC) format:
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"
Subquery Comparison
You can use the IN predicate with a subquery to test whether a column value (or any other expression) equals any of the subquery row values. For example:
SELECT Name,Home_State FROM Sample.Person
WHERE Name IN
(SELECT Name FROM Sample.Employee
HAVING Salary < 50000)
Note that the subquery must have exactly one select-item in the SELECT list.
The following example uses an IN subquery to return the Employee states that are not Vendor states:
SELECT Home_State
FROM Sample.Employee
WHERE Home_State NOT IN (SELECT Address_State FROM Sample.Vendor)
GROUP BY Home_State
The following example matches a collation function expression to an IN predicate with a subquery:
SELECT Name,Id FROM Sample.Person
WHERE %EXACT(Spouse) NOT IN
(SELECT Id FROM Sample.Person
WHERE Age < 65)
An IN cannot specify both a subquery and a comma-separated list of literal values.
Literal Substitution Override
You can override literal substitution during compile pre-parsing by enclosing each IN predicate argument with parentheses. For example, WHERE Home_State IN (('ME'),('NH'),('VT'),('MA'),('RI'),('CT')). This may improve query performance by improving overall selectivity and/or subscript bounding selectivity. However, it should be avoided when the same query is called multiple times with different values, as it will result in the creation of a separate cached query for each query call.
IN and %INLIST
Both the IN and %INLIST predicates can be used to supply multiple values to use for OR equality comparisons. The %INLIST predicate is used for matching a value to the elements of a %List structure. In Dynamic SQL you can supply the %INLIST predicate values as a single host variable. You must supply the IN predicate values as individual host variables. Therefore, changing the number of IN predicate values results in the creation of a separate cached query. %INLIST takes a single predicate value, a %List with multiple elements; changing the number of %List elements does not result in the creation of a separate cached query. %INLIST also provides an order-of-magnitude SIZE argument that SQL uses to optimize performance. For these reasons it is often advantageous to use %INLIST($LISTFROMSTRING(val)) rather than IN(val1,val2,val3,..valn).
%INLIST can perform equality comparisons; it cannot perform subquery comparisons.
For further details, refer to %INLIST.
Item Limits
When a database driver, such as JDBC, the IN predicate can support 32,000 items. However, you may encounter maximum string size limits earlier than that. There are no specific limits when using the IN predicate within InterSystems IRIS.
If you are using statements with such lengthy IN predicates, consider passing a comma-separated list as a single argument to %INLIST $LISTFROMSTRING(...).
See Also
-
%INLIST predicate