5 Search Conditions
A search condition specifies criteria for choosing rows to select, update, or delete. Search conditions are parameters that can exist in clauses and expressions of any DML statements, such as INSERT...SELECT
and UPDATE
, and in some DDL statements, such as CREATE VIEW
.
Search condition general syntax
A search condition is a single predicate or several predicates connected by the logical operators AND
or OR
. A predicate is an operation on expressions that evaluates to TRUE
, FALSE
, or UNKNOWN
. If a predicate evaluates to TRUE
for a row, the row qualifies for further processing. If the predicate evaluates to FALSE
or NULL
for a row, the row is not available for operations.
SQL syntax
[NOT] {BetweenPredicate
|ComparisonPredicate
|InPredicate
|LikePredicate
|NullPredicate
|InfinitePredicate
|NaNPredicate
|QuantifiedPredicate
|(SearchCondition
)} [{AND | OR} [NOT] {BetweenPredicate
|ComparisonPredicate
|InPredicate
|LikePredicate
|NullPredicate
|QuantifiedPredicate
| (SearchCondition
)} ] [...]
Parameters
Component | Description |
---|---|
|
Logical operators with the following functions:
|
|
Determines whether an expression is within a certain range of values. For example: |
|
Compares two expressions or list of two expressions using one of the operators |
|
Determines whether an expression or list of expressions matches an element within a specified set. |
|
Determines whether a subquery returns any row. |
|
Determines whether an expression contains a particular character string pattern. |
|
Determines whether a value is |
|
Determines whether an expression is infinite (positive or negative infinity). |
|
Determines whether an expression is the undefined result of an operation ("not a number"). |
|
Determines whether an expression or list of expressions bears a particular relationship to a specified set. |
( |
One of the above predicates, enclosed in parentheses. |
Description
-
Predicates in a search condition are evaluated as follows:
-
Predicates in parentheses are evaluated first.
-
NOT
is applied to each predicate. -
AND
is applied next, left to right. -
OR
is applied last, left to right.
Figure 5-1 shows the values that result from logical operations. A question mark (
?
) represents theNULL
value. -
Figure 5-1 Values that result from logical operations
![Description of Figure 5-1 follows Description of Figure 5-1 follows](img/truthtable.gif)
Description of "Figure 5-1 Values that result from logical operations"
-
When the search condition for a row evaluates to
NULL
, the row does not satisfy the search condition and the row is not operated on. -
You can compare only compatible data types.
-
TT_TINYINT
,TT_SMALLINT
,TT_INTEGER
,TT_BIGINT
,NUMBER
,BINARY_FLOAT
andBINARY_DOUBLE
are compatible. -
CHAR
,VARCHAR2
,BINARY
, andVARBINARY
are compatible, regardless of length. -
CHAR
,VARCHAR2
,NCHAR
,NVARCHAR2
,TT_TIME
,DATE
andTIMESTAMP
are compatible.
-
-
See "Expressions" for information on value extensions during comparison operations.
-
See "Numeric data types" for information about how TimesTen compares values of different but compatible types.
ALL / NOT IN predicate (subquery)
The ALL
or NOT IN
predicate indicates that the operands on the left side of the comparison must compare in the same way with all of the values that the subquery returns. The ALL
predicate evaluates to TRUE
if the expression or list of expressions relates to all rows returned by the subquery as specified by the comparison operator. Similarly, the NOT IN
predicate evaluates to TRUE
if the expression or list of expressions does not equal the value returned by the subquery.
SQL syntax
RowValueConstructor
{CompOp
ALL | NOT IN} (Subquery
)
The syntax for RowValueConstructor
:
RowValueConstructorElement
| (RowValueConstuctorList
) |Subquery
The syntax for RowValueConstructorList
:
RowValueConstructorElement
[{,RowValueConstructorElement
} ... ]
The syntax for RowValueConstructorElement
:
Expression
| NULL
The syntax for CompOp
:
{= | <> | > | >= | < | <= }
Parameters
Component | Description |
---|---|
|
See "Expression specification" for the syntax. Both numeric and non-numeric expressions are allowed for |
|
Is equal to. |
|
Is not equal to. |
|
Is greater than. |
|
Is greater than or equal to. |
|
Is less than. |
|
Is less than or equal to. |
|
See "Subqueries" for the syntax. |
Description
-
The
ALL
predicate, which returns zero or more rows, uses a comparison operator modified with the keywordALL
. See "Numeric data types" for information about how TimesTen compares values of different but compatible types. -
If
RowValueConstructorList
is specified only the operators=
and<>
are allowed.
Examples
Examples of NOT IN
with subqueries:
SELECT * FROM customers WHERE cid NOT IN (SELECT cust_id FROM returns) AND cid > 5000; SELECT * FROM customers WHERE cid NOT IN (SELECT cust_id FROM returns) AND cid NOT IN (SELECT cust_id FROM complaints); SELECT COUNT(*) From customers WHERE cid NOT IN (SELECT cust_id FROM returns) AND cid NOT IN (SELECT cust_id FROM complaints);
Select all books that are not from exclBookList
or if the price of the book is higher than $20.
SELECT * FROM books WHERE id NOT IN (SELECT id FROM exclBookList) OR books.price>20;
The following query returns the employee_id
and job_id
from the job_history table. It illustrates use of expression list and subquery with the NOT IN
predicate.
Command> SELECT employee_id, job_id FROM job_history WHERE (employee_id, job_id) NOT IN (SELECT employee_id, job_id FROM employees); < 101, AC_ACCOUNT > < 101, AC_MGR > < 102, IT_PROG > < 114, ST_CLERK > < 122, ST_CLERK > < 176, SA_MAN > < 200, AC_ACCOUNT > < 201, MK_REP > 8 rows found.
ALL / NOT IN predicate (value list)
The ALL
/ NOT IN
quantified predicate compares an expression or list of expressions with a list of specified values. The ALL
predicate evaluates to TRUE
if all the values in the ValueList
relate to the expression or list of expressions as indicated by the comparison operator. Similarly, the NOT IN
predicate evaluates to TRUE
if the expression or list of expressions does not equal one of the values in the list.
SQL syntax
RowValueConstructor
{CompOp
ALL | NOT IN}ValueList
The syntax for RowValueConstructor
:
RowValueConstructorElement
| (RowValueConstructorList
) |
The syntax for RowValueConstructorList
:
RowValueConstructorElement
[{,RowValueConstructorElement
} ... ]
The syntax for RowValueConstructorElement
:
Expression
| NULL
The syntax for CompOp
:
{= | <> | > | >= | < | <= }
The syntax for one element in the ValueList
(no parentheses necessary):
ConstantExpression
The syntax for more than one element in the ValueList
:
({ConstantExpression
} [,...] )
The syntax for an empty ValueList
:
( )
Parameters
Component | Description |
---|---|
|
Specifies a value to be obtained. The values in |
|
Specifies a constant value or an expression that evaluates to a constant value (such as a number, character string, or date). This includes support for bound values ( |
|
Is equal to. |
|
Is not equal to. |
|
Is greater than. |
|
Is greater than or equal to. |
|
Is less than. |
|
Is less than or equal to. |
|
The predicate is |
|
A list of values that are compared against the The The The |
Description
-
If
X
is the value ofExpression
, and(a,b, ..., z)
represents the elements inValueList
, andOP
is a comparison operator, then the following is true:-
X OP ALL (a,b,...,z)
is equivalent toX OP a AND X OP b AND...AND X OP z
.
-
-
If
X
is the value ofExpression
and(a,b,..., z)
are the elements in aValueList
, then the following is true:-
X NOT IN (a,b,...,z)
is equivalent toNOT (X IN (a,b,...,z))
.
-
-
All character data types are compared in accordance with the current value of the
NLS_SORT
session parameter. -
See "Numeric data types" for information about how TimesTen compares values of different but compatible types.
-
NOT IN
orNOT EXISTS
withALL
can be specified in anOR
expression. -
IN
andEXISTS
withALL
can be specified in anOR
expression. -
When evaluating an empty
ValueList
, the result ofExpression
NOT IN
is true. -
If
RowValueConstructorList
is specified only the operators=
and<>
are allowed.
Examples
To query an empty select list for a NOT IN
condition:
SELECT * FROM t1 WHERE x1 NOT IN ();
For ValueList
examples, see the Examples section in "ANY / IN predicate (value list)".
ANY / IN predicate (subquery)
An ANY
predicate compares two expressions using a comparison operator. The predicate evaluates to TRUE
if the first expression relates to anyrow returned by the subquery as specified by the comparison operator. Similarly, the IN
predicate compares an expression or list of expressions with a table subquery. The IN
predicate evaluates to TRUE
if the expression or list of expressions is equal to a value returned by a subquery.
SQL syntax
RowValueConstructor
{CompOp
ANY | IN} (Subquery
)
The syntax for RowValueConstructor
:
RowValueConstructorElement
| (RowValueConstructorList
) |Subquery
The syntax for RowValueConstructorList
:
RowValueConstructorElement
[{,RowValueConstructorElement
} ... ]
The syntax for RowValueConstructorElement
:
Expression
| NULL
The syntax for CompOp
:
{= | <> | > | >= | < | <= }
Parameters
Component | Description |
---|---|
|
See "Expression specification" for information on syntax. Both numeric and non-numeric expressions are allowed for |
|
Is equal to. |
|
Is not equal to. |
|
Is greater than. |
|
Is greater than or equal to. |
|
Is less than. |
|
Is less than or equal to. |
|
See "Subqueries" for information on the syntax for subqueries. |
Description
The ANY
predicate, which returns zero or more rows, uses a comparison operator modified with the keyword ANY
. See "Numeric data types" for information about how TimesTen compares values of different but compatible types.
Examples
This example retrieves a list of customers having at least one unshipped order:
SELECT customers.name FROM customers WHERE customers.id = ANY (SELECT orders.custid FROM orders WHERE orders.status = 'unshipped');
This is an example of an IN
predicate with subquery. It SELECT
s customers having at least one unshipped order:
SELECT customers.name FROM customers WHERE customers.id IN (SELECT orders.custid FROM orders WHERE orders.status = 'unshipped');
This example uses an aggregate query that specifies a subquery with IN
to find the maximum price of a book in the exclBookList
:
SELECT MAX(price) FROM books WHERE id IN (SELECT id FROM exclBookList);
This example illustrates the use of a list of expressions with the IN
predicate and a subquery.
SELECT * FROM t1 WHERE (x1,y1) IN (SELECT x2,y2 FROM t2);
This example illustrates the use of a list of expressions with the ANY
predicate and a subquery.
SELECT * FROM t1 WHERE (x1,y1) < ANY (SELECT x2,y2 FROM t2);
The following example illustrates the use of a list of expressions with the ANY
predicate.
Command> columnlabels on; Command> SELECT * FROM t1; X1, Y1 < 1, 2 > < 3, 4 > 2 rows found. Command> SELECT * FROM t2; X2, Y2 < 3, 4 > < 1, 2 > 2 rows found.
ANY / IN predicate (value list)
The ANY
/ IN
quantified predicate compares an expression or list of expressions with a list of specified values. The ANY
predicate evaluates to TRUE
if one or more of the values in the ValueList
relate to the expression or list of expressions as indicated by the comparison operator. Similarly, the IN
predicate evaluates to TRUE
if the expression or list of expressions is equal to one of the values in the list.
SQL syntax
RowValueConstructor {CompOp {ANY | SOME} | IN} ValueList
The syntax for RowValueConstructor
:
RowValueConstructorElement | (RowValueConstructorList) |
The syntax for RowValueConstructorList
:
RowValueConstructorElement[{, RowValueConstructorElement} ... ]
The syntax for RowValueConstructorElement
:
Expression | NULL
The syntax for CompOp
:
{= | <> | > | >= | < | <= }
The syntax for one element in the ValueList
(no parentheses necessary):
ConstantExpression
The syntax for more than one element in the ValueList
:
({ConstantExpression
} [,...] )
The syntax for an empty ValueList
:
( )
Parameters
Component | Description |
---|---|
|
Specifies a value to be obtained. The values in |
|
Specifies a constant value or an expression that evaluates to a constant value (such as a number, character string, or date). This includes support for bound values ( |
|
Is equal to. |
|
Is not equal to. |
|
Is greater than. |
|
Is greater than or equal to. |
|
Is less than. |
|
Is less than or equal to. |
{
|
The predicate is |
|
A list of values that are compared against the The The The |
Description
-
If
X
is the value ofExpression
, and(a,b, ..., z)
represents the elements inValueList
, andOP
is a comparison operator, then the following is true:-
X OP ANY (a,b,...,z)
is equivalent toX OP a OR X OP b OR...OR X OP z
.
-
-
If
X
is the value ofExpression
and(a,b,..., z)
are the elements in aValueList
, then the following is true:-
X IN (a,b,...,z)
is equivalent toX = a OR X = b OR...OR X = z
.
-
-
All character data types are compared in accordance with the current value of the
NLS_SORT
session parameter. -
See "Numeric data types" for information about how TimesTen compares values of different but compatible types.
-
When evaluating an empty
ValueList
, the result ofExpression
IN
is false.
Examples
Select all item numbers containing orders of 100, 200, or 300 items.
SELECT DISTINCT OrderItems.ItemNumber FROM OrderItems WHERE OrderItems.Quantity = ANY (100, 200, 300)
Get part numbers of parts whose weight is 12, 16, or 17.
SELECT Parts.PartNumber FROM Parts WHERE Parts.Weight IN (12, 16, 17);
Get part number of parts whose serial number is '1123-P-01'
, '1733-AD-01'
, :SerialNumber
or :SerialInd
, where :SerialNumber
and :SerialInd
are dynamic parameters whose values are supplied at runtime.
SELECT PartNumber FROM Purchasing.Parts WHERE SerialNumber IN ('1123-P-01', '1733-AD-01',:SerialNumber, :SerialInd);
The following example queries an empty select list for IN
condition.
SELECT * FROM t1 WHERE x1 IN ();
The following example uses a list of expressions with IN
.
SELECT * FROM t1 WHERE (x1,y1) IN ((1,2), (3,4));
The next three examples, using ttIsql
, show the use of constant expressions. Assume a table T
with a single column named X
of type NUMBER
, with the following data:
Command> SELECT * FROM t; < 1 > < 2 > < 3 > < 4 > < 5 > 5 rows found.
This first example uses constant expressions in a query:
Command> SELECT x FROM t WHERE x in (abs(1-2), TO_NUMBER('1')+2, 3); < 1 > < 3 > 2 rows found.
This second example also uses dynamic parameters:
Command> SELECT x FROM t WHERE x = ANY (1+?, 1+3, ?); Type '?' for help on entering parameter values. Type '*' to end prompting and abort the command. Type '-' to leave the parameter unbound. Type '/;' to leave the remaining parameters unbound and execute the command. Enter Parameter 1 '_QMARK_1' (NUMBER) > 1 Enter Parameter 2 '_QMARK_2' (NUMBER) > 5 < 2 > < 4 > < 5 > 3 rows found.
This third example also uses NULL
:
Command> SELECT x FROM t WHERE x IN (null, 1, 3+1, 2); < 1 > < 2 > < 4 > 3 rows found.
For the next example, consider a table T_DATE
with a single column named MYDATE
of type DATE
, with the following data:
Command> SELECT * FROM t_date; < 2013-08-13 00:00:00 > < 2013-08-14 00:00:00 > < 2013-08-15 00:00:00 > 3 rows found.
The example uses constant expressions for dates:
Command> SELECT mydate FROM t_date WHERE mydate IN (DATE '2013-08-12'+1, DATE '2013-08-12'+2); < 2013-08-13 00:00:00 > < 2013-08-14 00:00:00 > 2 rows found.
For the next example, consider a table MYCHARS
with a single column named COL1
of type VARCHAR2(32)
, with the following data:
Command> SELECT * FROM mychars; < abc > < def > < ghi > 3 rows found.
The example uses a function call that returns a constant expression:
Command> SELECT col1 FROM mychars WHERE col1 IN (ltrim('abcdef', 'abc')); < def > 1 row found.
The following example illustrates the use of a list of expressions for the IN
predicate. The query returns the DEPARTMENT_NAME
for departments with DEPARTMENT_ID = 240
and LOCATION_ID = 1700
.
Note:
The expression on the right side of the IN
predicate must be enclosed in double parentheses (( ))
.
Command> SELECT department_name FROM departments WHERE (department_id, location_id) IN ((240,1700)); < Government Sales > 1 row found.
BETWEEN predicate
A BETWEEN
predicate determines whether a value is:
-
Greater than or equal to a second value
and:
-
Less than or equal to a third value
The predicate evaluates to TRUE
if a value falls within the specified range.
SQL syntax
Expression1
[NOT] BETWEENExpression2
ANDExpression3
Parameters
Parameter | Description |
---|---|
|
See "Expression specification" for information on the syntax. Both numeric and non-numeric expressions are allowed in |
Description
-
BETWEEN
evaluates toFALSE
andNOT BETWEEN
evaluates toTRUE
if the second value is greater than the third value. -
Consult the following table if either
Expression2
orExpression3
isNULL
forBETWEEN
orNOT BETWEEN
:Expression2 Expression3 BETWEEN NOT BETWEEN <=
Expression1
NULL
NULL
NULL
>
Expression1
NULL
FALSE
TRUE
NULL
>=
Expression1
NULL
NULL
NULL
<
Expression1
NULL
NULL
-
Expression2
andExpression3
constitute a range of possible values for whichExpression2
is the lowest possible value andExpression3
is the highest possible value within the specified range. In theBETWEEN
predicate, the low value must be specified first.See "Comparison predicate" for information on comparisons.
-
The
BETWEEN
predicate is not supported forNCHAR
types.
Examples
Parts sold for under $250.00 and over $1500.00 are discounted 25 percent.
UPDATE Purchasing.Parts SET SalesPrice = SalesPrice * 0.75 WHERE SalesPrice NOT BETWEEN 250.00 AND 1500.00;
Comparison predicate
A comparison predicate compares two expressions using a comparison operator. The predicate evaluates to TRUE
if the first expression relates to the second expression as specified by the comparison operator.
SQL syntax
RowValueConstructor
CompOp
RowValueConstructor2
The syntax for RowValueConstructor
:
RowValueConstructorElement
| (RowValueConstructorList
) |ScalarSubquery
The syntax for RowValueConstructorList
:
RowValueConstructorElement
[{,RowValueConstructorElement
} ... ]
The syntax for RowValueConstructor2
(one expression)
Expression
The syntax for RowValueConstructor2
(list of expressions)
((Expression
[,...]))
The syntax for CompOp
:
{= | <> | > | >= | < | <= }
Parameters
Component | Description |
---|---|
|
See "Expression specification" for information on syntax. Both numeric and non-numeric expressions are allowed in comparison predicates, but both expressions must be compatible with each other. |
|
A subquery that returns a single value. See "Subqueries" for information on scalar subqueries. |
|
Is equal to. |
|
Is not equal to. |
|
Is greater than. |
|
Is greater than or equal to. |
|
Is less than. |
|
Is less than or equal to. |
Description
-
All character data types are compared in accordance with the current value of the
NLS_SORT
session parameter. -
If
RowValueConstructorList
is specified only the operators=
and<>
are allowed. -
See "Numeric data types" for information about how TimesTen compares values of different but compatible types.
-
If either side of a comparison predicate evaluates to
UNKNOWN
orNULL
, this implies that neither the predicate nor the negation of the predicate isTRUE
. -
The NULL value itself can be used directly as an operand of an operator or predicate. For example, the
(1 = NULL)
comparison is supported. This is the same as if you cast NULL to the appropriate data type, as follows:(1 = CAST(NULL AS INT))
. Both methods are supported and return the same results.
Examples
Retrieve part numbers of parts requiring fewer than 20 delivery days:
SELECT PartNumber FROM Purchasing.SupplyPrice WHERE DeliveryDays < 20;
The query returns the last_name
of employees where salary=9500
and commission_pct=.25
.
Note:
The expression on the right side of the equal sign must be enclosed in double parentheses (( ))
.
Command> SELECT last_name FROM employees WHERE(salary,commission_pct) = ((9500,.25)); < Bernstein > 1 row found.
The query returns the last_name
of the employee whose manager_id = 205
. The employee's department_id
and manager_id
is stored in both the employees
and departments
tables. A subquery is used to extract the information from the departments
table.
Command> SELECT last_name FROM employees WHERE (department_id, manager_id) = (SELECT department_id, manager_id FROM departments WHERE manager_id = 205); < Gietz > 1 row found.
EXISTS predicate
An EXISTS
predicate checks for the existence or nonexistence of a table subquery. The predicate evaluates to TRUE
if the subquery returns at least one row for EXISTS
or returns no rows for NOT EXISTS
.
SQL syntax
[NOT] EXISTS (Subquery
)
Parameters
The EXISTS
predicate has the following parameter:
Parameter | Description |
---|---|
|
See "Subqueries" for information on syntax. |
Description
-
When a subquery is introduced with
EXISTS
, the subquery functions as an existence test.EXISTS
tests for the presence or absence of an empty set of rows. If the subquery returns at least one row, the subquery evaluates to true. -
When a subquery is introduced with
NOT EXISTS
, the subquery functions as an absence test.NOT EXISTS
tests for the presence or absence of an empty set of rows. If the subquery returns no rows, the subquery evaluates to true. -
If join order is issued using the
ttOptSetOrder
built-in procedure that conflicts with the join ordering requirements of theNOT EXISTS
subquery, the specified join order is ignored, TimesTen issues a warning and the query is executed. -
The following table describes supported and unsupported usages of
EXISTS
andNOT EXISTS
in TimesTen.Query/subquery description Not Exists Exists Aggregates in subquery
Supported
Supported
Aggregates in main query
Supported
Supported
Subquery in
OR
clauseSupported
Supported
Join ordering using the
ttOptSetOrder
built-in procedureLimited support
Supported
Examples
Get a list of customers having at least one unshipped order.
SELECT customers.name FROM customers WHERE EXISTS (SELECT 1 FROM orders WHERE customers.id = orders.custid AND orders.status = 'unshipped');
Get a list of customers having no unshipped orders.
SELECT customers.name FROM customers WHERE NOT EXISTS (SELECT 1 FROM orders WHERE customers.id = orders.custid AND orders.status = 'unshipped');
IS INFINITE predicate
An IS INFINITE
predicate determines whether an expression is infinite (positive infinity (INF
) or negative infinity (-INF
)).
SQL syntax
Expression
IS [NOT] INFINITE
Parameters
Parameter | Description |
---|---|
|
Expression to test. |
Description
-
An
IS INFINITE
predicate evaluates toTRUE
if the expression is positive or negative infinity. -
An
IS NOT INFINITE
predicate evaluates toTRUE
if expression is neither positive nor negative infinity. -
The expression must either resolve to a numeric data type or to a data type that can be implicitly converted to a numeric data type.
-
Two positive infinity values are equal to each other. Two negative infinity values are equal to each other.
-
Expressions containing floating-point values may generate
Inf
,-Inf
, orNaN
. This can occur either because the expression generated overflow or exceptional conditions or because one or more of the values in the expression wasInf
,-Inf
, orNaN
. Inf andNaN
are generated in overflow or division by 0 conditions. -
Inf
,-Inf
, andNaN
values are not ignored in aggregate functions.NULL
values are. If you want to excludeInf
andNaN
from aggregates (or from any selection), use both theIS NOT NAN
andIS NOT INFINITE
predicates. -
Negative infinity (
-INF
) sorts lower than all other values. Positive infinity (INF
) sorts higher than all other values, but lower thanNaN
("not a number") and theNULL
value. -
See "INF and NAN" for more information on
Inf
andNaN
.
IS NAN predicate
An IS NAN
predicate determines whether an expression is the undefined result of an operation (that is, is "not a number" or NaN
).
SQL syntax
Expression
IS [NOT] NAN
Parameters
Parameter | Description |
---|---|
|
Expression to test. |
Description
-
An
IS NAN
predicate evaluates toTRUE
if the expression is "not a number." -
An
IS NOT NAN
predicate evaluates toTRUE
if expression is not "not a number." -
The expression must either resolve to a numeric data type or to a data type that can be implicitly converted to a numeric data type.
-
Two
NaN
("not a number") values are equal to each other. -
Expressions containing floating-point values may generate
Inf
,-Inf
, orNaN
. This can occur either because the expression generated overflow or exceptional conditions or because one or more of the values in the expression wasInf
,-Inf
, orNaN
.Inf
andNaN
are generated in overflow or division by 0 conditions. -
Inf
,-Inf
, andNaN
values are not ignored in aggregate functions.NULL
values are. If you want to excludeInf
andNaN
from aggregates (or from any selection), use both theIS NOT NAN
andIS NOT INFINITE
predicates. -
NaN
("not a number") sorts higher than all other values including positive infinity, but lower than theNULL
value. -
See "INF and NAN" for more information on
Inf
andNaN
.
IS NULL predicate
The IS NULL
predicate determines whether an expression has the value NULL
. The predicate evaluates to TRUE
if the expression is NULL
. If the NOT
option is used, the predicate evaluates to TRUE
if the expression is NOT NULL
.
SQL syntax
{ColumnName | Constant | Expression | LOBDataType} IS [NOT] NULL
Parameters
Parameter | Description |
---|---|
|
The name of a column from which a value is to be taken. See "Names, Namespace and Parameters" for information on column names. |
|
A specific value. See "Constants" for information on constants. |
|
Expression to test. |
|
Value to test that is in a |
Examples
Use IS NULL
to identify the president of the company, who is the only person without a manager.
Command> SELECT * FROM employees WHERE manager_id IS NULL; < 100, Steven, King, SKING, 515.123.4567, 1987-06-17 00:00:00, AD_PRES, 24000, <NULL>, <NULL>, 90 > 1 row found.
The following statement uses IS NULL
to identify all locations without a state or province.
Command> SELECT * FROM locations WHERE state_province IS NULL; < 1000, 1297 Via Cola di Rie, 00989, Roma, <NULL>, IT > < 1100, 93091 Calle della Testa, 10934, Venice, <NULL>, IT > < 1300, 9450 Kamiya-cho, 6823, Hiroshima, <NULL>, JP > < 2000, 40-5-12 Laogianggen, 190518, Beijing, <NULL>, CN > < 2300, 198 Clementi North, 540198, Singapore, <NULL>, SG > < 2400, 8204 Arthur St, <NULL>, London, <NULL>, UK > 6 rows found.
LIKE predicate
A LIKE
predicate evaluates to TRUE
if the source contains a given pattern. The LIKE
predicate matches a portion of one character value to another by searching the source for the pattern specified.
SQL syntax
Source [NOT] LIKE Pattern [ESCAPE {'EscapeChar' | {? | :DynamicParameter} }]
The syntax for Pattern
is as follows:
Expression [ || Expression ] [ ... ]
Parameters
Parameter | Description |
---|---|
|
This source is searched for all occurrences of the pattern. The source may be an expression, column, character string resulting from a function, or any combination of these that results in a character string used for the source on which the pattern is matched. The source can be a |
|
Describes a character pattern that you are searching for in the source with one or more expressions. The data type of the pattern should be a character string data type, such as Multiple expressions may be concatenated to form the character string used for the pattern. The pattern consists of characters including digits and special characters. For example, You can also use the predicate to test for a partial match by using one or more of the following symbols:
You can use the |
|
Any expression included in the pattern may be a column, a dynamic parameter, or the result of a function that evaluates to a character string. See "Expression specification" for information on expressions. |
|
Describes an optional escape character which can be used to interpret the symbols The escape character must be a single character. When it appears in the pattern, it must be followed by the escape character itself, the |
?
|
Indicates a dynamic parameter in a prepared SQL statement. The parameter value is supplied when the statement is executed. |
Description
-
As long as no escape character is specified, the
_
or%
symbols in the pattern act as wild card characters. If an escape character is specified, the wild card or escape character that follows is treated literally. If the character following an escape character is not a wild card or the escape character, an error results. -
When providing a combination of expressions, columns, character strings, dynamic parameters, or function results to form the pattern, you can concatenate items together using the || operator to form the final pattern.
-
Case is significant in all conditions comparing character expressions that use the
LIKE
predicate. -
If the value of the expression, the pattern, or the escape character is
NULL
, theLIKE
predicate evaluates toNULL
. -
The
LIKE
predicate may be slower when used on a multibyte character set. -
See "Pattern matching for strings of NCHAR, NVARCHAR2, and NCLOB data types" for more information on searching within a national character string within
NCHAR
,NVARCHAR
, orNCLOB
.
Examples
Find each employee whose last name begins with 'Sm'
.
Command> SELECT employee_id, last_name,first_name FROM employees WHERE last_name LIKE 'Sm%' ORDER BY employee_id,last_name,first_name; < 159, Smith, Lindsey > < 171, Smith, William > 2 rows found.
Find each employee whose last name begins with 'SM'
. This query returns no results because there are no employees whose last_name
begins with upper case 'SM'
.
Command> SELECT employee_id, last_name,first_name from employees WHERE last_name LIKE 'SM%' ORDER BY employee_id,last_name,first_name; 0 rows found.
However, by upper casing the source value of the last name column, you can find all names that begin with 'SM
'.
Command> SELECT employee_id, last_name, first_name FROM employees WHERE UPPER(last_name) LIKE ('SM%'); < 159, Smith, Lindsey > < 171, Smith, William > 2 rows found.
Use a dynamic parameter denoted by ?
to find each employee whose last name begins with 'Sm'
at execution time.
Command> SELECT employee_id, last_name,first_name FROM employees WHERE last_name like ? ORDER BY employee_id,last_name,first_name; Type '?' for help on entering parameter values. Type '*' to end prompting and abort the command. Type '-' to leave the parameter unbound. Type '/;' to leave the remaining parameters unbound and execute the command. Enter Parameter 1 '_QMARK_1' (VARCHAR2) > 'Sm%' < 159, Smith, Lindsey > < 171, Smith, William > 2 rows found.
Use a bind variable denoted by :a
to find each employee whose last name begins with 'Sm'
at execution time.
Command> SELECT employee_id, last_name,first_name FROM employees WHERE last_name LIKE :a ORDER BY employee_id,last_name,first_name; Type '?' for help on entering parameter values. Type '*' to end prompting and abort the command. Type '-' to leave the parameter unbound. Type '/;' to leave the remaining parameters unbound and execute the command. Enter Parameter 1 'A' (VARCHAR2) > 'Sm%' < 159, Smith, Lindsey > < 171, Smith, William > 2 rows found.
For each employee whose last name begins with 'Smit'
, find the last name of the manager. Display the first name and last name of the employee and the last name of the manager.
Command> SELECT e1.first_name || ' ' || e1.last_name||' works for '||e2.last_name FROM employees e1, employees e2 WHERE e1.manager_id = e2.employee_id AND e1.last_name like 'Smit'; < Lindsey Smith works for Partners > < William Smith works for Cambrault > 2 rows found.
This query pattern references the last_name
column as the pattern for which to search:
Command> SELECT e1.first_name || ' ' || e1.last_name||' works for ' || e2.last_name FROM employees e1, employees e2 WHERE e1.manager_id = e2.employee_id AND 'Smith' like e1.last_name; < Lindsey Smith works for Partners > < William Smith works for Cambrault > 2 rows found.
The pattern can be a column or the result of a function. The following uses the UPPER
function on both the source last_name
column as well as the 'ma'
search string for which you are searching:
Command> SELECT last_name, first_name FROM employees WHERE UPPER(last_name) LIKE UPPER('ma%'); < Markle, Steven > < Marlow, James > < Mallin, Jason > < Matos, Randall > < Marvins, Mattea > < Mavris, Susan > 6 rows found.
The following query demonstrates using a dynamic parameter to request the pattern.
Command> SELECT first_name || ' ' || last_name FROM employees WHERE last_name like ?; Type '?' for help on entering parameter values. Type '*' to end prompting and abort the command. Type '-' to leave the parameter unbound. Type '/;' to leave the remaining parameters unbound and execute the command. Enter Parameter 1 '_QMARK_1' (VARCHAR2) > 'W%' < Matthew Weiss > < Alana Walsh > < Jennifer Whalen > 3 rows found.
The following query demonstrates combining a character string with a dynamic parameter in the pattern.
Command> SELECT first_name || ' ' || last_name FROM employees WHERE last_name like 'W' || ?; Type '?' for help on entering parameter values. Type '*' to end prompting and abort the command. Type '-' to leave the parameter unbound. Type '/;' to leave the remaining parameters unbound and execute the command. Enter Parameter 1 '_QMARK_1' (VARCHAR2) > '%' < Matthew Weiss > < Alana Walsh > < Jennifer Whalen > 3 rows found.
Pattern matching for strings of NCHAR, NVARCHAR2, and NCLOB data types
The LIKE
predicate can be used for pattern matching for strings of type NCHAR
, NVARCHAR2
, and NCLOB
. The pattern matching characters are:
Character | Description |
---|---|
|
Represents any single Unicode character. |
|
Represents any string of zero or more Unicode characters. |
Description
-
The escape character is similarly supported as a single Unicode character or parameter.
-
The types of the
LIKE
operands can be any combination of character types. -
Case-insensitive and accent-insensitive
NLS_SORT
is supported with theLIKE
predicate.
Examples
In these examples, the Unicode character U+0021 EXCLAMATION MARK
is being used to escape the Unicode character U+005F SPACING UNDERSCORE
. Unicode character U+0025 PERCENT SIGN
is not escaped, and assumes its pattern matching meaning.
VendorName
is an NCHAR
or NVARCHAR2
column.
SELECT VendorName FROM Purchasing.Vendors WHERE VendorName LIKE N'ACME!_%' ESCAPE N'!';
This example is equivalent:
SELECT VendorName FROM Purchasing.Vendors WHERE VendorName LIKE N'ACME!\u005F\u0025' ESCAPE N'!';