WHERE Clause

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.

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

column-name

is a single column of one the tables specified in the FROM clause.

comparison-op

is one of the comparison operators.

constant-expression

is a constant value of the same data type as the column-name

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

column-expression	is a single column of one the tables specified in the FROM clause.
list-expression	is a comma-separated list of constant values matching the data type of the column-expression

Examples

x IN (5, 6, 7)
x in (select a from table)	not allowed
(x, y) in ((5,6), (7,8))	not allowed

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

column-reference

refers to a column of one the tables specified in the FROM clause.

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

string	(CHAR or VARCHAR) is the column value to be compared to the pattern.
NOT	returns true if LIKE returns false, and vice versa (equivalent to `NOT` string `LIKE` pattern).
pattern	specifies a string containing wildcard characters. underscore (_) matches any single character. percent sign (%) matches any string of zero or more characters.
ESCAPE	specifies an escape-character. A null escape character ('') disables the escape mechanism. To match the escape character itself, use two consecutive escape characters.
escape-character	when preceding an underscore or percent sign character in the pattern, causes that character to be treated as a literal rather than a wildcard. The default escape character is the backslash (/) character.

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

column-name

is a single column of one the tables specified in the FROM clause.

Examples

a IS NULL

b IS NOT NULL