Syntax

[ AND | OR | NOT ]

Semantics

SQL uses a three-valued Boolean logic where the null value represents "unknown."

Notes

• The operators AND and OR are commutative, that is, you can switch the left and right operand without affecting the result. However, the order of evaluation of subexpressions is not defined. When it is essential to force evaluation order, use a CASE construct.
• Do not confuse Boolean operators with the Boolean-predicate or the Boolean data type, which can have only two values: true and false.

The special BETWEEN predicate is available as a convenience.

Syntax

a BETWEEN x AND y

Semantics

a BETWEEN x AND y is equivalent to

a >= x AND a <= y

Similarly,

a NOT BETWEEN x AND y

is equivalent to

a < x OR a > y

The Boolean predicate retrieves rows where the value of an expression is true, false, or unknown (null).

Syntax

expression IS [NOT] TRUE

expression IS [NOT] FALSE

expression IS [NOT] UNKNOWN

Semantics

A Boolean predicate always return true or false, never a null value, even when the operand is null. A null input is treated as the value UNKNOWN.

Notes

• Do not confuse the boolean-predicate with Boolean Operators or the Boolean data type, which can have only two values: true and false.

  Syntax

  [ AND | OR | NOT ]

  Semantics

  SQL uses a three-valued Boolean logic where the null value represents "unknown."

  a	b	a AND b	a OR b
  TRUE	TRUE	TRUE	TRUE
  TRUE	FALSE	FALSE	TRUE
  TRUE	NULL	NULL	TRUE
  FALSE	FALSE	FALSE	FALSE
  FALSE	NULL	FALSE	NULL
  NULL	NULL	NULL	NULL

  a	NOT a
  TRUE	FALSE
  FALSE	TRUE
  NULL	NULL

  Notes

  • The operators AND and OR are commutative, that is, you can switch the left and right operand without affecting the result. However, the order of evaluation of subexpressions is not defined. When it is essential to force evaluation order, use a CASE construct.
  • Do not confuse Boolean operators with the Boolean-predicate or the Boolean data type, which can have only two values: true and false.
• IS UNKNOWN and IS NOT UNKNOWN are effectively the same as the NULL-predicate, except that the input expression does not have to be a single column value. To check a single column value for NULL, use the NULL-predicate.

Syntax

column-name comparison-op constant-expression

Semantics

Notes

• To check a column value for NULL, use the NULL-predicate.

Examples

Dimension.column1 = 2

Dimension.column2 = 'Seafood'

Syntax

column-expression [ NOT ] IN ( list-expression )

Semantics

Examples

Vertica supports only equi-joins based on a primary key-foreign key relationship between the joined tables. See Adding Primary Key and Foreign Key Constraints in the Database Administrator's Guide for more information.

Syntax

column-reference = column-reference

Semantics

Notes

• Self-joins are not allowed.

The LIKE predicate retrieves rows where the string value of a column matches a specified pattern. The pattern can contain one or more wildcard characters, which match all valid characters. ILIKE is equivalent to LIKE except that the match is case-insensitive (non-standard extension).

Syntax

string { LIKE | ILIKE } pattern [ESCAPE escape-character]

string NOT { LIKE | ILIKE } pattern [ESCAPE escape-character]

Semantics

Notes

• LIKE requires the entire string expression to match the pattern. To match a sequence of characters anywhere within a string, the pattern must start and end with a percent sign.
• The LIKE predicate does not ignore trailing "white space" characters. If the data values that you want to match have unknown numbers of trailing spaces, tabs, etc., terminate each LIKE predicate pattern with the percent sign wildcard character.
• To use a backslash character as a literal, specify a different escape character and use two backslashes. For example, if the escape character is circumflex (^), you would use ^\\ to specify a literal backslash. (Alternative: simply use four backslashes.)
• The use of a column data type other than character or character varying (implicit string conversion) is not supported and not recommended.
• Error messages caused by the LIKE predicate may refer to it by the following symbols instead of the actual keywords:

  ~~	LIKE
  ~~*	ILIKE
  !~~	NOT LIKE
  !~~*	NOT ILIKE

Examples

'abc' LIKE 'abc' true

'abc' LIKE 'a%' true

'abc' LIKE '_b_' true

'abc' LIKE 'c' false

Syntax

column-name IS [ NOT ] NULL

Semantics

Examples

See Also

NULL Value