Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
Release 11.2.1

Part Number E13070-09
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

4 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, UPDATE and CREATE VIEW and 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
NOT, AND, OR Logical operators with the following functions:
  • NOT negates the value of the predicate that follows it.

  • AND evaluates to TRUE if both the predicates it joins evaluate to TRUE.

  • OR evaluates to TRUE if either predicate it joins evaluates to TRUE, and to FALSE if both predicates evaluates to FALSE.

  • See "Description" for a description of how these operators work when predicates evaluate to NULL.

BetweenPredicate Determines whether an expression is within a certain range of values. For example: A BETWEEN B AND C is equivalent to A >= B AND A<= C.
ComparisonPredicate Compares two expressions or list of two expressions using one of the operators <, <=, >, >=, =, <>.
InPredicate Determines whether an expression or list of expressions matches an element within a specified set.
ExistsPredicate Determines whether a subquery returns any row.
LikePredicate Determines whether an expression contains a particular character string pattern.
NullPredicate Determines whether a value is NULL.
InfinitePredicate Determines whether an expression is infinite (positive or negative infinity).
NaNPredicate Determines whether an expression is the undefined result of an operation ("not a number").
QuantifiedPredicate Determines whether an expression or list of expressions bears a particular relationship to a specified set.
(SearchCondition) One of the above predicates, enclosed in parentheses.

Description

Figure 4-1 Values that result from logical operations

Description of Figure 4-1 follows
Description of "Figure 4-1 Values that result from logical operations"


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
Expression The syntax of expressions is defined under "Expression specification". Both numeric and non-numeric expressions are allowed for ALL predicates, but both expression types must be compatible with each other.
= 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.
Subquery The syntax of subqueries is defined under "Subqueries"

Description

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

Component Description
Expression Specifies a value to be obtained. The values in ValueList must be compatible with the expression. For information on the syntax of expressions, see "Expression specification".
= 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.
ALL The predicate is TRUE if all the values in the ValueList relate to the expression or list of expressions as indicated by the comparison operator.
ValueList A list of values that are compared against the expression's or list of expression's value. The ValueList cannot contain a column reference or a subquery. The ValueList can be nested if the left operand of the ValueList is a list.

Elements of the ValueList:

  • Constant—Indicates a specific value. See "Constants".

  • ?,:DynamicParameter—Placeholder for a dynamic parameter in a prepared SQL statement. The value of the dynamic parameter is supplied when the statement is executed.

  • Empty list, which are sometimes generated by SQL generation tools.


Description

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

Component Description
Expression The syntax of expressions is defined under "Expression specification". Both numeric and non-numeric expressions are allowed for ANY predicates, but both expression types must be compatible with each other.
= 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.
Subquery The syntax of subqueries is defined under "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 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

Component Description
Expression Specifies a value to be obtained. The values in ValueList must be compatible with the expression. For information on the syntax of expressions, see "Expression specification".
= 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.
{ANY|

SOME}

The predicate is 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. SOME is a synonym for ANY.
ValueList A list of values that are compared against the expression's or list of expression's value. The ValueList cannot contain a column reference or a subquery. The ValueList can be nested if the left operand of the ValueList is a list.

Elements of the ValueList:

  • Constant—Indicates a specific value. See "Constants".

  • ?,:DynamicParameter—Placeholder for a dynamic parameter in a prepared SQL statement. The value of the dynamic parameter is supplied when the statement is executed.

  • Empty list, which are sometimes generated by SQL generation tools.


Description

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.

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:

The predicate evaluates to TRUE if a value falls within the specified range.

SQL syntax

Expression1 [NOT] BETWEEN Expression2 AND Expression3

Parameters

Parameter Description
Expression1, Expression2, Expression3 The syntax for expressions is defined in "Expression specification". Both numeric and non-numeric expressions are allowed in BETWEEN predicates, but all expressions must be compatible with each other.

Description

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

Component Description
Expression The syntax for expressions is defined under "Expression specification". Both numeric and non-numeric expressions are allowed in comparison predicates, but both expressions must be compatible with each other.
ScalarSubquery A subquery that returns a single value. Scalar subqueries and their restrictions are defined under "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

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

SQL syntax

[NOT] EXISTS (Subquery)

Parameters

The EXISTS predicate has the following parameter:

Parameter Description
Subquery The syntax of subqueries is defined under "Subqueries"

Description

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 Expression to test.

Description


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 Expression to test.

Description


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

Parameter Description
ColumnName The name of a column from which a value is to be taken. Column names are discussed in Chapter 2, "Names, Namespace and Parameters".
Constant A specific value. See "Constants".
(Expression) Expression to test.

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

Parameter Description
Expression The syntax of expressions is presented in Chapter 3, "Expressions".
PatternString Describes what you are searching for in the expression. The pattern may consist of characters only (including digits and special characters). For example, NAME LIKE 'Annie' evaluates to TRUE only for a name of Annie with no spaces. Upper case and lower case are significant.

You can also use the predicate to test for a partial match by using the following symbols in the pattern:

_  Represents any single character.

For example:

BOB and TOM both satisfy the predicate NAME LIKE '_O_'.

% Represents any string of zero or more characters.

For example, MARIE and RENATE both satisfy the predicate NAME LIKE '%A%'.

You can use the _ and % symbols multiple times and in any combination in a pattern. You cannot use these symbols literally within a pattern unless you use the ESCAPE clause and precede the symbols with the escape character, described by the EscapeChar parameter.

EscapeChar Describes an optional escape character which can be used to interpret the symbols _ and % literally in the pattern.

The escape character must be a single character. When it appears in the pattern, it must be followed by the escape character itself, the _ symbol or the % symbol. Each such pair represents a single literal occurrence of the second character in the pattern. The escape character is always case sensitive. The escape character cannot be _ or %.

?

DynamicParameter

Indicates a dynamic parameter in a prepared SQL statement. The parameter value is supplied when the statement is executed.

Description

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:

Character Description
U+005F SPACING UNDERSCORE Represents any single Unicode character.
U+0025 PERCENT SIGN Represents any string of zero or more Unicode characters.

Description

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