4 Search Conditions

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

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 4-1 shows the values that result from logical operations. A question mark (?) represents the NULL value.

Figure 4-1 Values that result from logical operations

Description of "Figure 4-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 and BINARY_DOUBLE are compatible.

  • CHAR, VARCHAR2, BINARY, and VARBINARY are compatible, regardless of length.

  • CHAR, VARCHAR2, NCHAR, NVARCHAR2, TT_TIME, DATE and TIMESTAMP are compatible.

• See Chapter 3, "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

Description

• The ALL predicate, which returns zero or more rows, uses a comparison operator modified with the keyword ALL. 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 | (RowValueConstuctorList) | 

The syntax for RowValueConstructorList:

RowValueConstructorElement[{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructorElement:

Expression | NULL

The syntax for CompOp:

{= | <> | > | >= | < | <= }

The syntax for more than one element in the ValueList:

({Constant | ? | :DynamicParameter} [,...] )

The syntax for one element in the ValueList not enclosed in parentheses:

Constant | ? | :DynamicParameter

The syntax for an empty ValueList:

( )

The syntax for the ValueList for a list of expressions:

(({Constant | ? | :DynamicParameter} [,...]))

Parameters

Description

• If X is the value of Expression, and (a,b, ..., z) represents the elements in ValueList, and OP is a comparison operator, then the following is true:

  • X OP ALL (a,b,...,z) is equivalent to X OP a AND X OP b AND...AND X OP z.

• If X is the value of Expression and (a,b,..., z) are the elements in a Value­List, then the following is true:

  • X NOT IN (a,b,...,z) is equivalent to NOT (X IN (a,b,...,z)).

• All character data types are compared in accordance with the current value of the NLS_SORT session parameter.

• NULL cannot be specified in ValueList.

• See "Numeric data types" for information about how TimesTen compares values of different but compatible types.

• NOT IN or NOT EXISTS with ALL can be specified in an OR expression.

• IN and EXISTS with ALL can be specified in an OR expression.

• When evaluating an empty ValueList, the result of Expression 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 ();

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 | (RowValueConstuctorList) | Subquery

The syntax for RowValueConstructorList:

RowValueConstructorElement[{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructorElement:

Expression | NULL

The syntax for CompOp:

{= | <> | > | >= | < | <= }

Parameters

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 SELECTs 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 | (RowValueConstuctorList) |

The syntax for RowValueConstructorList:

RowValueConstructorElement[{, RowValueConstructorElement} ... ]

The syntax for RowValueConstructorElement:

Expression | NULL

The syntax for CompOp:

{= | <> | > | >= | < | <= }

The syntax for more than one element in the ValueList:

({Constant | ? | :DynamicParameter} [,...] )

The syntax for one element in the ValueList not enclosed in parentheses:

Constant | ? | :DynamicParameter

The syntax for an empty ValueList:

( )

The syntax for the ValueList for a list of expressions:

(({Constant | ? | :DynamicParameter} [,...]))

Parameters

Description

• If X is the value of Expression, and (a,b, ..., z) represents the elements in ValueList, and OP is a comparison operator, then the following is true:

  • X OP ANY (a,b,...,z) is equivalent to X OP a OR X OP b OR...OR X OP z.

• If X is the value of Expression and (a,b,..., z) are the elements in a Value­List, then the following is true:

  • X IN (a,b,...,z) is equivalent to X = 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.

• NULL cannot be specified in ValueList.

• See "Numeric data types" for information about how TimesTen compares values of different but compatible types.

• When evaluating an empty ValueList, the result of Expression 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);

To query an empty select list for IN condition:

SELECT * FROM t1 WHERE x1 IN ();

Illustrates the use of a list of expressions with in:

SELECT * FROM t1 WHERE (x1,y1) IN ((1,2), (3,4));

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.

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] BETWEEN Expression2 AND Expression3

Parameters

Description

• BETWEEN evaluates to FALSE and NOT BETWEEN evaluates to TRUE if the second value is greater than the third value.

• Consult the following table if either Expression2 or Expression3 is NULL for BETWEEN or NOT BETWEEN:

  Expression2	Expression3	BETWEEN	NOT BETWEEN
  <= Expression1	NULL	NULL	NULL
  > Expression1	NULL	FALSE	TRUE
  NULL	>= Expression1	NULL	NULL
  NULL	< Expression1	NULL	NULL

  
  

• Expression2 and Expression3 constitute a range of possible values for which Expression2 is the lowest possible value and Expression3 is the highest possible value within the specified range. In the BETWEEN predicate, the low value must be specified first.

  Comparisons are conducted as described in "Comparison predicate".

• The BETWEEN predicate is not supported for NCHAR 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 | (RowValueConstuctorList) | 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

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 or NULL, this implies that neither the predicate nor the negation of the predicate is TRUE.

• The NULL value itself cannot be used directly as an operand of an operator or predicate. If used, it must be cast to the correct datatype. For example, (1 = NULL) is not supported; however, (1 = CAST(NULL AS INT)) is supported. This is in compliance with the ANSI SQL-92 specification.

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.

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 and no rows for NOT EXISTS

SQL syntax

[NOT] EXISTS (Subquery)

Parameters

The EXISTS predicate has the following parameter:

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 the NOT 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 and NOT EXISTS in TimesTen;

  Query/subquery description	Not Exists	Exists
  Aggregates in subquery	Supported	Supported
  Aggregates in main query	Supported	Supported
  Subquery in OR clause	Supported	Supported
  Join ordering using the ttOptSetOrder built-in procedure	Limited 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

Description

• An IS INFINITE predicate evaluates to TRUE if the expression is positive or negative infinity.

• An IS NOT INFINITE predicate evaluates to TRUE 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, or NaN. This can occur either because the expression generated overflow or exceptional conditions or because one or more of the values in the expression was Inf, -Inf, or NaN. Inf and NaN are generated in overflow or division by 0 conditions.

• Inf, -Inf, and NaN values are not ignored in aggregate functions. NULL values are. If you want to exclude Inf and NaN from aggregates (or from any selection), use both the IS NOT NAN and IS NOT INFINITE predicates.

• Negative infinity (-INF) sorts lower than all other values. Positive infinity (INF) sorts higher than all other values, but lower than NaN ("not a number") and the NULL value.

• For more information on Inf and NaN, see "INF and NAN".

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

Description

• An IS NAN predicate evaluates to TRUE if the expression is "not a number."

• An IS NOT NAN predicate evaluates to TRUE 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, or NaN. This can occur either because the expression generated overflow or exceptional conditions or because one or more of the values in the expression was Inf, -Inf, or NaN. Inf and NaN are generated in overflow or division by 0 conditions.

• Inf, -Inf, and NaN values are not ignored in aggregate functions. NULL values are. If you want to exclude Inf and NaN from aggregates (or from any selection), use both the IS NOT NAN and IS NOT INFINITE predicates.

• NaN ("not a number") sorts higher than all other values including positive infinity, but lower than the NULL value.

• For more information on Inf and NaN, see "INF and NAN".

IS NULL predicate

An 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)} IS [NOT] NULL

Parameters

Examples

Vendors with no personal contact names are identified.

SELECT * FROM Purchasing.Vendors WHERE ContactName IS NULL;

LIKE predicate

A LIKE predicate determines whether a CHAR, VARCHAR2, NCHAR, or NVARCHAR2 expression contains a given pattern. The predicate evaluates to TRUE if an expression contains the pattern.

SQL syntax

Expression [NOT] LIKE
 {'PatternString'| {? | :DynamicParameter}} 
[ESCAPE {'EscapeChar' | {? | :DynamicParameter}}]

Parameters

Description

• As long as no escape character is specified, the _ or % in the pattern acts as a wild card character. If an escape character is specified, then 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.

• If the value of the expression, the pattern, or the escape character is NULL, then the LIKE predicate evaluates to NULL and the row is not operated on.

Examples

Find employees 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 employees whose last name begins with 'SM'. This query returns no results because there are no employees 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;
0 rows found.

Use a dynamic parameter denoted by ? to find employees 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 employees 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 employees 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 results in an error because the pattern references a column.

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;
 1001: Syntax error in SQL statement before or at: "E1", character position: 169
...nager_id = e2.employee_id    AND 'Smith' like e1.last_name
                                                 ^^
The command failed.

NCHAR and NVARCHAR2

The LIKE predicate can be used for pattern matching of NCHAR and NVARCHAR2 strings. The pattern matching characters are:

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 the LIKE 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'!';