FirebirdSQL logo

Comparison Predicates

A comparison predicate consists of two expressions connected with a comparison operator.There are six traditional comparison operators:

=, >, <, >=, <=, <>

For the complete list of comparison operators with their variant forms, see Comparison Operators.

If one of the sides (left or right) of a comparison predicate has NULL in it, the value of the predicate will be UNKNOWN.

Examples
  1. Retrieve information about computers with the CPU frequency not less than 500 MHz and the price lower than $800:

    SELECT *
    FROM Pc
    WHERE speed >= 500 AND price < 800;
  2. Retrieve information about all dot matrix printers that cost less than $300:

    SELECT *
    FROM Printer
    WHERE ptrtype = 'matrix' AND price < 300;
  3. The following query will return no data, even if there are printers with no type specified for them, because a predicate that compares NULL with NULL returns NULL:

    SELECT *
    FROM Printer
    WHERE ptrtype = NULL AND price < 300;

    On the other hand, ptrtype can be tested for NULL and return a result: it is just that it is not a comparison test:

    SELECT *
    FROM Printer
    WHERE ptrtype IS NULL AND price < 300;
Note
Note about String Comparison

When CHAR and VARCHAR fields are compared for equality, trailing spaces are ignored in all cases.

Other Comparison Predicates

Other comparison predicates are marked by keyword symbols.

BETWEEN
Syntax
<value> [NOT] BETWEEN <value_1> AND <value_2>

The BETWEEN predicate tests whether a value falls within a specified range of two values.(NOT BETWEEN tests whether the value does not fall within that range.)

The operands for BETWEEN predicate are two arguments of compatible data types.The BETWEEN predicate in Firebird is asymmetrical — if the lower bound is not the first argument, the BETWEEN predicate will return FALSE.The search is inclusive (the values represented by both arguments are included in the search).In other words, the BETWEEN predicate could be rewritten:

<value> >= <value_1> AND <value> <= <value_2>

When BETWEEN is used in the search conditions of DML queries, the Firebird optimizer can use an index on the searched column, if it is available.

Example
SELECT *
FROM EMPLOYEE
WHERE HIRE_DATE BETWEEN date '1992-01-01' AND CURRENT_DATE
LIKE
Syntax
<match_value> [NOT] LIKE <pattern>
   [ESCAPE <escape character>]

<match_value>      ::= character-type expression
<pattern>          ::= search pattern
<escape character> ::= escape character

The LIKE predicate compares the character-type expression with the pattern defined in the second expression.Case- or accent-sensitivity for the comparison is determined by the collation that is in use.A collation can be specified for either operand, if required.