SAND CDBMS SQL Reference Guide
Selection Criteria for WHERE and HAVING Clauses

 

Previous Topic:
Boolean Value Expressions (Search Conditions)
Chapter Index
 x

 

Predicates


Simple Comparison Predicates

The examples given below use Boolean value expressions in the context of SELECT statements. The same expressions may be used in any SQL element that can employ Boolean value expressions in a search condition (for example, an UPDATE statement).

Predicates using the following comparison operators in their selection conditions are called simple predicates:

= equal to
<> not equal to
!= not equal to
> greater than
>= greater than or equal to
< less than
<= less than or equal to


Example

The following query displays all rows in the supplier table containing a value of 20 in their status column:

SELECT *
  FROM supplier
    WHERE status = 20;

The query results appear below:

sno sname status city
S1 SMITH 20 LONDON
S4 CLARK 20 LONDON


The WHERE clause in the next example compares a character string 'PARIS' with the city column. The query below retrieves sno (supplier number) and status column data for all suppliers in Paris:

SELECT sno, status
  FROM supplier
    WHERE city = 'PARIS';

Note that character strings must be enclosed by single quotation marks ( ' ' ). The displayed result appears below:

sno status
S2 10
S3 30


When one column value is compared against another column value in a query and the column data types are different, SAND CDBMS tries to convert one of the data values to a data type compatible with the other data value before performing the comparison. For example, if integer and float column values are compared, the INT data value is converted to a FLOAT value before SAND CDBMS performs the comparison.

Note that BLOB columns cannot be compared with other BLOB columns, or with columns of any other data type.

Spaces in data values are considered when evaluating retrieval conditions. Right-justified data with leading spaces does not equal left-justified data with trailing spaces. For example, the string 'Jones' is read as a completely different value, depending on the number of preceding or trailing blank spaces attached.

The LIKE predicate operator allows string pattern matching without strict adherence to character string content and order.

Both the < > and  ! =  comparison operators specify records for retrieval that do not meet the specified condition. For example, the following query retrieves the pno (part number) column values of all Part table records with a color column value other than 'RED'.

SELECT pno
  FROM part
    WHERE color != 'RED';

 

Compound Predicates

Compound predicates use the operators OR and AND. For example, to display the sno column values for all suppliers in "PARIS" with a status column value greater than 20, use the following query:

SELECT sno
  FROM supplier
    WHERE city = 'PARIS'
      AND status > 20;

This SELECT command statement retrieves pno column values for those parts that are either 'RED', or weigh more than 15 pounds:

SELECT pno
  FROM part
    WHERE color = 'RED'
      OR weight > 15;

In complex queries, parentheses can be used to indicate the order of evaluation. The condition(s) surrounded by the innermost pair of parentheses are applied first.

SELECT pno, pname
  FROM part
    WHERE color = 'green'
      OR (city = 'LONDON'
      AND weight < 15);

This last query retrieves pno and pname column values of all parts that are either green, or are both made in London and have a weight less than 15.

The query result is displayed below:

pno pname
P1 NUT
P2 BOLT
P4 SCREW

 

Other Predicates:

 

Previous Topic:
Boolean Value Expressions (Search Conditions)
Chapter Index
 x