 |
» |
|
|
|
An IN predicate compares an expression with a list of specified
values
or a list of values derived from a subquery. The predicate evaluates
to TRUE if the expression is equal to one of the values in the list.
If the NOT option is used, the predicate evaluates to TRUE if the
expression is not equal to any of the values in the list.
Scope | |
SQL Data Manipulation Statements
SQL Syntax | |
Expression [NOT] IN { SubQuery (ValueList) } Parameters | |
- Expression
An expression specifies a value to be obtained.
The syntax of expressions is presented in the "Expressions" chapter.
Both numeric and non-numeric expressions are allowed in quantified
predicates. The expression may not include subqueries or LONG columns.
- NOT
reverses the value of the predicate that follows it.
- SubQuery
A subquery is a nested query.
The syntax of subqueries is presented in the description of the SELECT
statement in the "SQL Statements" chapter.
- ValueList
defines a list of values to be compared against the expression's value.
The syntax for ValueList is: { USER CurrentFunction [ + - ] { Integer Float Decimal } 'CharacterString' 0xHexadecimalString :HostVariable [ [INDICATOR] :IndicatorVariable] ? :LocalVariable :ProcedureParameter ::Built-inVariable LongColumnFunction StringFunction } [,...] - USER
USER evaluates to the DBEUserID.
In ISQL, it evaluates to the DBEUserID of the ISQL user.
From an application program, it evaluates
DBEUserID of the individual running the program. USER behaves
like a CHAR(20) constant, with trailing blanks if the login name
has fewer than 20 characters.
- CurrentFunction
indicates the value of the current DATE, TIME, or DATETIME.
- Integer
indicates a value of type INTEGER or SMALLINT.
- Float
indicates a value of type FLOAT.
- Decimal
indicates a value of type DECIMAL.
- CharacterString
specifies a CHAR, VARCHAR, DATE, TIME, DATETIME, or INTERVAL value.
Whichever is shorter -- the string or the expression value --
is padded with blanks before the comparison is made.
- HexadecimalString
specifies a BINARY or VARBINARY value. If the string is shorter than the
target column, it is padded with binary zeroes; if it is longer than the
target column, the string is truncated.
- HostVariable
contains a value in an application program being
input to the expression.
- IndicatorVariable
names an indicator variable, whose value
determines whether the associated host variable contains a NULL value:
- > = 0
the value is not NULL
- < 0
the value is NULL (The value in the host variable will be ignored.)
- ?
is a place holder for a dynamic parameter in a prepared SQL statement in
an application program.
The value of the dynamic parameter is supplied at run time.
- LocalVariable
contains a value in a procedure.
- ProcedureParameter
contains a value that is passed into or out of a
procedure.
- Built-inVariable
is one of the following built-in variables
used for error handling: The first six of these have the same meaning that they have as
fields in the SQLCA in application programs.
Note that in procedures, sqlerrd2 returns the number of rows processed for
all host languages. However, in application programs,
sqlerrd3 is used in COBOL, Fortran, and Pascal, while sqlerr2 is used
in C.
::activexact indicates whether a transaction is in progress
or not. For additional information, refer to the application
programming guides and to the chapter "Constraints, Procedures,
and Rules."
- StringFunction
returns partial values or attributes of character
and binary (including LONG) string data.
- LongColumnFunction
returns information from the long column descriptor.
Description | |
If X is the value of Expression and (a,b, ..., z) represent the result of a SubQuery or the elements in a ValueList, then the following are true: X IN (a,b,...,z) is equivalent to X = ANY (a,b,...,z)
X IN (a,b,...,z) is equivalent to X = a OR X = b OR...OR X = z
X NOT IN (a,b,...,z) is equivalent to NOT (X IN (a,b,...,z))
Refer to the "Data Types" chapter for information about the type
conversions that ALLBASE/SQL performs when you compare values of
different types.
If all values in the ValueList are NULL, the predicate evaluates
to unknown.
Example | |
Get part numbers of parts whose weight is 12, 16, or 17.
SELECT P.PNO
FROM P
WHERE P.WEIGHT IN (12, 16, 17)
|
Get the names of suppliers who supply part number 'P2'.
SELECT S.SNAME
FROM S
WHERE S.SNO IN (SELECT SP.SNO FROM SP
WHERE SP.SNO = 'P2')
|
If the indicator variable is >= 0 and PartNumber is one of
'1123-P-01', '1733-AD-01', or :PartNumber, then the predicate
evaluates to true.
If the indicator variable is < 0, the rows containing the part
numbers 1123-P-01 and 1733-AD-01 are selected; but no rows will be
selected based upon the value in :PartNumber.
EXEC SQL SELECT PartNumber
FROM PurchDB.Parts
WHERE PartNumber
IN ('1123-P-01', '1733-AD-01', :PartNumber :PartInd)
|
|
