Boolean Expressions

Boolean expressions are allowed in  CONSTRAINT clause for more information. Boolean expressions in a WHERE clause have a highly liberal syntax; see WHERE clause, for example.

A Boolean expression can include zero or more Boolean operators.

Syntax

The following table shows the syntax for the Boolean operators

Operator Syntax
AND, OR, NOT
{
   Expression AND Expression
 | Expression OR  Expression
 | NOT Expression
}
Comparisons
Expression
{
   <
 | =
 | >
 | <=
 | >=
 | <>
}
IS NULL,
IS NOT NULL
Expression IS [ NOT ] NULL
LIKE
CharacterExpression
 [ NOT ] LIKE CharacterExpression
   WithWildCard [ ESCAPE 'escapeCharacter']
BETWEEN
Expression [ NOT ] BETWEEN Expression AND Expression
IN
{
  Expression [ NOT ] IN TableSubquery |
  Expression [ NOT ] IN
 ( Expression [, Expression ]* )
}
EXISTS
[NOT] EXISTS TableSubquery
Quantified comparison
Expression ComparisonOperator
  {
   ALL |
   ANY |
   SOME
  }
  TableSubquery

Examples

The following example presents examples of the Boolean operators.

Operator Explanation and Example
AND, OR, NOT Evaluate any operand(s) that are boolean expressions:
(orig_airport = 'SFO') OR (dest_airport = 'GRU')
	-- returns true
Comparisons <, =, >, <=, >=, <> are applicable to all of the built-in types.
DATE('1998-02-26') < DATE('1998-03-01')
	-- returns true

Splice Machine also accepts the != operator, which is not included in the SQL standard.

IS NULL,
IS NOT NULL
Test whether the result of an expression is null or not.
WHERE MiddleName IS NULL
LIKE

Attempts to match a character expression to a character pattern, which is a character string that includes one or more wildcards.

% matches any number (zero or more) of characters in the corresponding position in first character expression.

_ matches one character in the corresponding position in the character expression.

Any other character matches only that character in the corresponding position in the character expression.

city LIKE 'Sant_'

To treat % or _ as constant characters, escape the character with an optional escape character, which you specify with the ESCAPE clause.

SELECT a FROM tabA WHERE a LIKE '%=_' ESCAPE '='

When LIKE comparisons are used, Splice Machine compares one character at a time for non-metacharacters. This is different than the way Splice Machine processes = comparisons. The comparisons with the = operator compare the entire character string on left side of the = operator with the entire character string on the right side of the = operator.

BETWEEN Tests whether the first operand is between the second and third operands. The second operand must be less than the third operand. Applicable only to types to which <= and >= can be applied.
WHERE booking_date
  BETWEEN DATE('1998-02-26')
  AND DATE('1998-03-01')

Using the BETWEEN operator is logically equivalent to specifying that you want to select values that are greater than or equal to the first operand and less than or equal to the second operand: col between X and Y is equivalent to col >= X and col <= Y. Which means that the result set will be empty if your second operand is less than your first.

IN Operates on table subquery or list of values. Returns TRUE if the left expression's value is in the result of the table subquery or in the list of values. Table subquery can return multiple rows but must return a single column.
WHERE booking_date NOT IN
 (SELECT booking_date
  FROM HotelBookings
  WHERE rooms_available = 0)
EXISTS Operates on a table subquery. Returns TRUE if the table subquery returns any rows, and FALSE if it returns no rows. A table subquery can return multiple columns and rows.
WHERE EXISTS
 (SELECT *
  FROM Flights
  WHERE dest_airport = 'SFO'
  AND orig_airport = 'GRU')
Quantified comparison

A quantified comparison is a comparison operator (<, =, >, <=, >=, <>) with ALL or ANY or SOME applied.

Operates on table subqueries, which can return multiple rows but must return a single column.

If ALL is used, the comparison must be true for all values returned by the table subquery. If ANY or SOME is used, the comparison must be true for at least one value of the table subquery. ANY and SOME are equivalent.

WHERE normal_rate < ALL
  (SELECT budget/550 FROM Groups) 

See Also