A search condition specifies a condition that is true or false about a given row or group of rows. Query expressions and UPDATE statements can specify a search condition. The search condition restricts the number of rows in the result table for the query expression or UPDATE statement.
Search conditions contain one or more predicates. The predicates that can be part of a search condition are described in the following subsections.
Syntax
search_condition ::
[NOT] predicate
[ { AND | OR } { predicate | ( search_condition ) } ]
predicate ::
basic_predicate
| quantified_predicate
| between_predicate
| null_predicate
| like_predicate
| contains_predicate
| exists_predicate
| in_predicate
| outer_join_predicate
| fulltext_predicate
Logical Operators: OR, AND, NOT
Logical operators combine multiple search conditions. FairCom DB SQL evaluates multiple search conditions in this order:
- Search conditions enclosed in parentheses. (For nested search conditions in parentheses, FairCom DB SQL evaluates the innermost search condition first.)
- Search conditions preceded by NOT
- Search conditions combined by AND
- Search conditions combined by OR
Examples
SELECT *
FROM customer
WHERE name = 'LEVIEN' OR name = 'SMITH' ;
SELECT *
FROM customer
WHERE city = 'PRINCETON' AND state = 'NJ' ;
SELECT *
FROM customer
WHERE NOT (name = 'LEVIEN' OR name = 'SMITH') ;
Relational Operators
Relational operators specify how FairCom DB SQL compares expressions in basic and quantified predicates.
Syntax
relop ::
=
| <> | != | ^=
| <
| <=
| >
| >=
Relational Operator |
Predicate |
|---|---|
= |
True if the two expressions are equal. |
<> | != | ^= |
True if the two expressions are not equal. The operators != and ^= are equivalent to <>. |
< |
True if the first expression is less than the second expression. |
<= |
True if the first expression is less than or equal to the second expression |
> |
True if the first expression is greater than the second expression. |
>= |
True if the first expression is greater than or equal to the second expression. |
Refer to Basic Predicate and Quantified Predicate for more information.
Basic Predicate
Description
A basic predicate compares two values using a relational operator (see Relational Operators for information). If a basic predicate specifies a query expression, then the query expression must return a single value. Basic predicates often specify an inner join. See Inner Joins for more detail.
If the value of any expression is null or the query_expression does not return any value, then the result of the predicate is set to false.
Syntax
basic_predicate ::
expr relop { expr | (query_expression) }
Quantified Predicate
Description
The quantified predicate compares a value with a collection of values using a relational operator (see Relational Operators for more detail). A quantified predicate has the same form as a basic predicate with the query_expression being preceded by ALL, ANY, or SOME keyword. The result table returned by query_expression can contain only a single column. A quantified predicate can be used inside of a WHERE, HAVING, or JOIN statement.
When ALL is specified the predicate evaluates to true if the query_expression returns no values or the specified relationship is true for all the values returned.
When SOME or ANY is specified the predicate evaluates to true if the specified relationship is true for at least one value returned by the query_expression. There is no difference between the SOME and ANY keywords. The predicate evaluates to false if the query_expression returns no values or the specified relationship is false for all the values returned.
Syntax
quantified_predicate ::
expr relop { ALL | ANY | SOME } (query_expression)
Example
10 < ANY ( SELECT COUNT(*)
FROM order_tbl
GROUP BY custid )
See also:
BETWEEN Predicate
Description
The BETWEEN predicate can be used to determine if a value is within a specified value range or not. The first expression specifies the lower bound of the range and the second expression specifies the upper bound of the range. This predicate can be used inside of a WHERE, HAVING, or JOIN statement.
The predicate evaluates to true if the value is greater than or equal to the lower bound of the range, or less than or equal to the upper bound of the range.
Syntax
between_predicate ::
expr [ NOT ] BETWEEN expr AND expr
Example
salary BETWEEN 2000.00 AND 10000.00
See also:
NULL Predicate
Description
The NULL predicate can be used for testing null values of database table columns. This predicate can be used inside of a WHERE, HAVING, or JOIN statement.
Syntax
null_predicate ::
column_name IS [ NOT ] NULL
Example
contact_name IS NOT NULL
See also:
LIKE Predicate
Description
The LIKE predicate searches for strings that have a certain pattern. The pattern is specified after the LIKE keyword in a scalar expression which can be as simple as a string constant or a complex expression containing concatenation operators, scalar functions, etc. The pattern can be specified by a string in which the underscore ( _ ) and percent sign ( % ) characters have special semantics.
This predicate can be used inside of a WHERE, HAVING, or JOIN statement.
The ESCAPE clause can be used to disable the special semantics given to characters ‘ _ ’ and ‘ % ’. The escape character specified must precede the special characters to disable their special semantics.
Syntax
like_predicate ::
column_name [ NOT ] LIKE expr
[ ESCAPE escape-character ]
Notes
- The column name specified in the LIKE predicate must refer to a character string column.
- A percent sign in the pattern matches zero or more characters of the column string.
- A underscore sign in the pattern matches any single character of the column string.
Examples
cust_name LIKE '%Computer%'
cust_name LIKE '___'
item_name LIKE '%\_%' ESCAPE '\'
item_name LIKE left(part_name, 5) + '%'
In the first example, for all strings with the substring Computer, the predicate will evaluate to true. In the second example, for all strings which are exactly three characters long, the predicate will evaluate to true. In the third example, the backslash character ‘ \ ’ has been specified as the escape character, which means that the special interpretation given to the character ‘ _ ’ is disabled. The pattern will evaluate to TRUE if the column item_name has embedded underscore characters.
In the fourth example, the LIKE predicate evaluates to true if the first 5 characters of item_name match the first 5 characters of part_name.
See also:
CONTAINS Predicate
Description
The FairCom DB SQL CONTAINS predicate is an extension to the SQL standard providing search capabilities for LONG character and binary data. This predicate can be used inside of a WHERE, HAVING, or JOIN statement.
Syntax
column_name [ NOT ] CONTAINS 'string'
Limitations
- column_name must be one of the following data types: Fixed or varying-length character type, LVARCHAR, BINARY, VARBINARY, or LVARBINARY.
- Unlike the LIKE clause, the wildcard characters ‘%’ and ‘_’ are not allowed with CONTAINS.
- CONTAINS does not take advantage of indexes to perform the search.
- The CONTAINS predicate is an extension to the SQL standard providing search capabilities for LVARBINARY and LVARCHAR data types.
See also:
EXISTS Predicate
Description
The EXISTS predicate can be used to check for the existence of specific rows. The query_expression returns rows rather than values. The predicate evaluates to true if the number of rows returned by the query_expression is non-zero. The EXISTS predicate can be used inside of a WHERE, HAVING, or JOIN statement.
See also Search Conditions.
Syntax
exists_predicate ::
EXISTS ( query_expression )
Example
SELECT cm_custname FROM custmast WHERE EXISTS (SELECT co_ordrdate FROM custordr WHERE cm_custnumb = custordr.co_custnumb)
In this example, the predicate will evaluate to true if the specified customer has any orders.
IN Predicate
Description
The IN predicate can be used to compare a value with a set of values. If an IN predicate specifies a query expression, then the result table it returns can contain only a single column. This predicate can be used inside of a WHERE, HAVING, or JOIN statement.
Syntax
in_predicate ::
expr [ NOT ] IN { ( query_expression ) |
( constant , constant [ , ... ] ) }
Example
address.state IN ('MA', 'NH')
See also:
Outer Join Predicate
Description
An outer join predicate specifies two tables and returns a result table that contains all of the rows from one of the tables, even if there is no matching row in the other table. See Outer Joins for more information.
Syntax
outer_join_predicate ::
[ table_name. ] column = [ table_name. ] column (+)
| [ table_name. ] column (+) = [ table_name. ] column
Full Text Predicate
Description
The full-text predicate can be used to search text based on a full-text index. If an IN predicate specifies a query expression, the result table it returns can contain only a single column. This predicate can be used inside a WHERE, HAVING, or JOIN statement.
Syntax
fulltext_predicate::
[ table_name. ] column MATCH phrase
phrase ::
token [ operator token [ operator token...] ... ]
operator ::
[ ^ | near | near/x | and | or | - ]
^ search from beginning of column (only one ^ allowed per query)
near multiple near terms allowed
near/x specify number of tokens to be within the target search token. Multiple near terms allowed.
and logical AND operator
NOTE: See Planned Features for which operators are implemented.