3.3 PREDICATES

For such a diverse set of types (DATE, TIME, TIMESTAMP, TIME WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, and two variants of INTERVAL: year-month and day-time), SQL-92 supports only four classes of temporal predicates: equality, lessthan, is null, and overlaps.

There are several variants of the equality predicate; these variants apply to all the data types. When applied to two expressions, ' =' determines whether the values of these expressions are identical. When applied to a value and a set of values (of the same type), =ANY determines if the left-hand value is identical to at least one of the values in the right-hand set. =SOME and IN are nonorthogonal equivalents. MATCH also relies on equality testing. The queries in CF-3.1 are identical in meaning.

SELECT * FROM EmployeeWHERE BirthDate = DATE '1970-01-01'SELECT * FROM EmployeeWHERE BirthDate =ANY (VALUES ((DATE '1970-01-01')))SELECT * FROM EmployeeWHERE BirthDate =ALL (VALUES ((DATE '1970-01-01')))SELECT * FROM EmployeeWHERE BirthDate =SOME (VALUES ((DATE '1970-01-01')))<a name="100"></a><a name="IDX-34"></a>SELECT * FROM EmployeeWHERE BirthDate IN (VALUES ((DATE '1970-01-01')))SELECT * FROM EmployeeWHERE NOT BirthDate NOT IN (VALUES ((DATE '1970-01-01')))SELECT * FROM EmployeeWHERE BirthDate MATCH (VALUES ((DATE '1970-01-01')))

Here, VALUES constructs a table with one row consisting of one column. More variations are possible using the UNIQUE and PARTIAL reserved words available with MATCH. (We mention MATCH for completeness. This construct, particularly with its options, is intended for determining whether or...