Predicates are truth-tests. If the predicate test is true, it returns a value. Each predicate is evaluated per row, so that when the predicate is part of an entire table SELECT statement, the statement can return multiple results.
Predicates consist of a set of parameters and arguments. For example, in the following WHERE clause:
WHERE name = 'Smith'
name = 'Smith' is the predicate
'Smith' is an expression
1 - ANY and ALL
ANY and ALL are logical operators that let you make comparisons on subqueries that return one or more rows.
ANY and ALL are logical operators that let you make comparisons on subqueries that return one or more rows. Both operators must be preceded by a comparison operator and followed by a subquery:
expressioncomparison-operator { ANY | ALL } (subquery)
ANY returns true if the comparison between expression and any value returned by subquery evaluates to true.
ALL returns true only if the comparison between expression and all values returned by subquery evaluates to true.
Equivalent operators
You can use the following operators instead of ANY or ALL:
This operator...
Is equivalent to:
SOME
ANY
IN
= ANY
NOT IN
<> ALL
NULL handling
Vertica supports multicolumn <> ALL subqueries where the columns are not marked NOT NULL. If any column contains a NULL value, Vertica returns a run-time error.
Vertica does not support ANY subqueries that are nested in another expression if any column values are NULL.
A subquery that uses the ALL keyword returns true when all values retrieved by the subquery match the left-hand expression, otherwise it returns false.
Checks whether an expression is within the range of two other expressions, inclusive.
Checks whether an expression is within the range of two other expressions, inclusive. All expressions must be of the same or compatible data types.
Syntax
WHERE a BETWEEN x AND y
Equivalent predicates
The following BETWEEN predicates can be rewritten in conventional SQL with logical operators AND and OR.
This BETWEEN predicate...
Is equivalent to...
WHERE aBETWEENxANDy
WHERE a>=xANDa<=y
WHERE aNOT BETWEENxANDy
WHERE a<xORa>y
Examples
The BETWEEN predicate can be especially useful for querying date ranges, as shown in the following examples:
=> SELECT NOW()::DATE;
NOW
------------
2022-12-15
(1 row)
=> CREATE TABLE t1 (a INT, b varchar(12), c DATE);
CREATE TABLE
=> INSERT INTO t1 VALUES
(0,'today',NOW()),
(1,'today+1',NOW()+1),
(2,'today+2',NOW()+2),
(3,'today+3',NOW()+3),
(4,'today+4',NOW()+4),
(5,'today+5',NOW()+5),
(6,'today+6',NOW()+6);
OUTPUT
--------
7
(1 row)
=> COMMIT;
COMMIT
=> SELECT * FROM t1;
a | b | c
---+---------+------------
0 | today | 2022-12-15
1 | today+1 | 2022-12-16
2 | today+2 | 2022-12-17
3 | today+3 | 2022-12-18
4 | today+4 | 2022-12-19
5 | today+5 | 2022-12-20
6 | today+6 | 2022-12-21
(7 rows)
=> SELECT * FROM t1 WHERE c BETWEEN '2022-12-17' AND '2022-12-20';
a | b | c
---+---------+------------
2 | today+2 | 2022-12-17
3 | today+3 | 2022-12-18
4 | today+4 | 2022-12-19
5 | today+5 | 2022-12-20
(4 rows)
Use the NOW and INTERVAL keywords to query a date range:
=> SELECT * FROM t1 WHERE c BETWEEN NOW()::DATE AND NOW()::DATE + INTERVAL '2 days';
a | b | c
---+---------+------------
0 | today | 2022-12-15
1 | today+1 | 2022-12-16
2 | today+2 | 2022-12-17
(3 rows)
3 - Boolean
Retrieves rows where the value of an expression is true, false, or unknown (NULL).
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
Notes
NULL input is treated as the value UNKNOWN.
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.
Do not confuse the Boolean predicate with Boolean operators or the Boolean data type, which can have only two values: true and false.
4 - EXISTS
EXISTS and NOT EXISTS predicates compare an expression against a subquery:.
EXISTS and NOT EXISTS predicates compare an expression against a subquery:
EXISTS returns true if the subquery returns one or more rows.
NOT EXISTS returns true if the subquery returns no rows.
Syntax
expression [ NOT ] EXISTS ( subquery )
Usage
EXISTS results only depend on whether any or no records are returned, and not on the contents of those records. Because the subquery output is usually of no interest, EXISTS tests are commonly written in one of the following ways:
In the first case, the subquery returns 1 for every record found by the subquery. For example, the following query retrieves a list of all customers whose store purchases were greater than 550 dollars:
Whether you use EXISTS or IN subqueries depends on which predicates you select in outer and inner query blocks. For example, the following query gets a list of all the orders placed by all stores on January 2, 2007 for vendors with records in the vendor table:
The above query looks for existence of the vendor and date ordered. To return a particular value, rather than simple existence, the query looks for orders placed by the vendor who got the best deal on January 2, 2007:
Checks whether a single value is found (or not found) within a set of values.
Checks whether a single value is found (or not found) within a set of values.
Syntax
(column-list) [ NOT ] IN ( values-list )
Arguments
column-list
One or more comma-delimited columns in the queried tables.
values-list
Comma-delimited list of constant values to find in the column-list columns. Each values-list value maps to a column-list column according to their order in values-list and column-list, respectively. Column/value pairs must have compatible data types.
You can specify multiple sets of values as follows:
( (values-list), (values-list)[,...] )
Null handling
Vertica supports multicolumn NOT IN subqueries where the columns are not marked NOT NULL. If one of the columns is found to contain a NULL value during query execution, Vertica returns a run-time error.
Similarly, IN subqueries nested within another expression are not supported if any column values are NULL. For example, if in the following statement column x from either table contains a NULL value, Vertica returns a run-time error:
Whether you use EXISTS or IN subqueries depends on which predicates you select in outer and inner query blocks. For example, the following query gets a list of all the orders placed by all stores on January 2, 2007 for vendors with records in the vendor table:
The above query looks for existence of the vendor and date ordered. To return a particular value, rather than simple existence, the query looks for orders placed by the vendor who got the best deal on January 2, 2007:
Joins two event series using some ordered attribute. Event series joins let you compare values from two series directly, rather than having to normalize the series to the same measurement interval.
An event series join is an extension of a regular outer join. The difference between expressing a regular outer join and an event series join is the INTERPOLATE predicate, which is used in the ON clause (see Examples below). Instead of padding the non-preserved side with null values when there is no match, the event series join pads the non-preserved side with the previous/next values from the table.
Interpolated values come from the table that contains the null, not from the other table.Vertica does not guarantee that the output contains no null values. If there is no previous/next value for a mismatched row, that row is padded with nulls.
Syntax
expression1 INTERPOLATE { PREVIOUS | NEXT } VALUE expression2
The columns can be of any data type. Because event series are time-based, the type is typically DATE/TIMEor TIMESTAMP.
{ PREVIOUS | NEXT } VALUE
Pads the non-preserved side with the previous/next values when there is no match. If previous is called on the first row (or next on the last row), will pad with null values.
Input rows are sorted in ascending logical order of the join column.
Note
An ORDER BY clause, if used, does not determine the input order but only determines query output order.
Notes
Data is logically partitioned on the table in which it resides, based on other ON clause equality predicates.
Event series join requires that the joined tables are both sorted on columns in equality predicates, in any order, followed by the INTERPOLATED column. If data is already sorted in this order, then an explicit sort is avoided, which can improve query performance. For example, given the following tables:
ask is sorted on exchange, stock (or the reverse), ts
bid is sorted on exchange, stock (or the reverse), ts
SELECT ask.price - bid.price, ask.ts, ask.stock, ask.exchange
FROM ask FULL OUTER JOIN bid
ON ask.stock = bid.stock AND ask.exchange =
bid.exchange AND ask.ts INTERPOLATE PREVIOUS
VALUE bid.ts;
Restrictions
Only one INTERPOLATE expression is allowed per join.
INTERPOLATE expressions are used only with ANSI SQL-99 syntax (the ON clause), which is already true for full outer joins.
INTERPOLATE can be used with equality predicates only.
The AND operator is supported but not the OR and NOT operators.
Expressions and implicit or explicit casts are not supported, but subqueries are allowed.
Semantics
When you write an event series join in place of normal join, values are evaluated as follows (using the schema in the examples below):
t is the outer, preserved table.
t1 is the inner, non-preserved table.
For each row in outer table t, the ON clause predicates are evaluated for each combination of each row in the inner table t1.
If the ON clause predicates evaluate to true for any combination of rows, those combination rows are produced at the output.
If the ON clause is false for all combinations, a single output row is produced with the values of the row from t along with the columns of t1 chosen from the row in t1 with the greatest t1.y value such that t1.y < t.x; If no such row is found, pad with nulls.
Note
t LEFT OUTER JOIN t1 is equivalent to t1 RIGHT OUTER JOIN t.
In the case of a full outer join, all values from both tables are preserved.
Examples
The examples that follow use this simple schema.
CREATE TABLE t(x TIME);
CREATE TABLE t1(y TIME);
INSERT INTO t VALUES('12:40:23');
INSERT INTO t VALUES('13:40:25');
INSERT INTO t VALUES('13:45:00');
INSERT INTO t VALUES('14:49:55');
INSERT INTO t1 VALUES('12:40:23');
INSERT INTO t1 VALUES('14:00:00');
COMMIT;
Normal full outer join
=> SELECT * FROM t FULL OUTER JOIN t1 ON t.x = t1.y;
Notice the null rows from the non-preserved table:
x | y
----------+----------
12:40:23 | 12:40:23
13:40:25 |
13:45:00 |
14:49:55 |
| 14:00:00
(5 rows)
Full outer join with interpolation
=> SELECT * FROM t FULL OUTER JOIN t1 ON t.x INTERPOLATE PREVIOUS VALUE t1.y;
In this case, the rows with no entry point are padded with values from the previous row.
=> SELECT * FROM t FULL OUTER JOIN t1 ON t.x INTERPOLATE NEXT VALUE t1.y;
In this case, the rows with no entry point are padded with values from the next row.
x | y
----------+----------
12:40:23 | 12:40:23
13:40:25 | 14:00:00
13:45:00 | 14:00:00
14:49:55 |
14:49:55 | 14:00:00
(5 rows)
Normal left outer join
=> SELECT * FROM t LEFT OUTER JOIN t1 ON t.x = t1.y;
Again, there are nulls in the non-preserved table
x | y
----------+----------
12:40:23 | 12:40:23
13:40:25 |
13:45:00 |
14:49:55 |
(4 rows)
Left outer join with interpolation
=> SELECT * FROM t LEFT OUTER JOIN t1 ON t.x INTERPOLATE PREVIOUS VALUE t1.y;
Nulls have been padded with interpolated values.
x | y
----------+----------
12:40:23 | 12:40:23
13:40:25 | 12:40:23
13:45:00 | 12:40:23
14:49:55 | 14:00:00
(4 rows)
Likewise, interpolate next is also supported:
=> SELECT * FROM t LEFT OUTER JOIN t1 ON t.x INTERPOLATE NEXT VALUE t1.y;
Nulls have been padded with interpolated values here as well.
x | y
----------+----------
12:40:23 | 12:40:23
13:40:25 | 14:00:00
13:45:00 | 14:00:00
14:49:55 |
(4 rows)
Inner joins
For inner joins, there is no difference between a regular inner join and an event series inner join. Since null values are eliminated from the result set, there is nothing to interpolate.
A regular inner join returns only the single matching row at 12:40:23:
=> SELECT * FROM t INNER JOIN t1 ON t.x = t1.y;
x | y
----------+----------
12:40:23 | 12:40:23
(1 row)
An event series inner join finds the same single-matching row at 12:40:23:
=> SELECT * FROM t INNER JOIN t1 ON t.x INTERPOLATE PREVIOUS VALUE t1.y;
x | y
----------+----------
12:40:23 | 12:40:23
(1 row)
Specifies the columns on which records from two or more tables are joined.
Specifies the columns on which records from two or more tables are joined. You can connect multiple join predicates with logical operators AND, OR, and NOT.
Syntax
ON column-ref = column-ref [ {AND | OR | NOT } column-ref = column-ref ]...
Retrieves rows where a string expression—typically a column—matches the specified pattern or, if qualified by ANY or ALL, set of patterns.
Retrieves rows where a string expression—typically a column—matches the specified pattern or, if qualified by ANY or ALL, set of patterns. Patterns can contain one or more wildcard characters.
If an ANY or ALL pattern is qualified with NOT, the negation is pushed down to each clause. NOT LIKE ANY (a, b) is equivalent to NOT LIKE a OR NOT LIKE b. See the examples.
Syntax
string-expression [ NOT ] { LIKE | ILIKE | LIKEB | ILIKEB }
{ pattern | { ANY | SOME | ALL } ( pattern,... ) } [ ESCAPE 'char' ]
Arguments
string-expression
String expression, typically a column, to test for instances of the specified pattern or patterns.
NOT
Returns true if the LIKE predicate returns false and vice-versa. When used with ANY or ALL, applies to each value individually.
LIKE | ILIKE | LIKEB | ILIKEB
Type of comparison:
LIKE: Complies with the SQL standard, case-sensitive, operates on UTF-8 character strings, exact behavior depends on collation parameters such as strength. LIKE is stable for character strings, but immutable for binary strings
ILIKE: Same as LIKE but case-insensitive.
LIKEB: Performs case-sensitive byte-at-a-time ASCII comparisons, immutable for character and binary strings.
ILIKEB: Same as LIKEB but case-insensitive.
pattern
A pattern to test against the expression. Pattern strings can contain the following wildcard characters:
_ (underscore): Match any single character.
% (percent): Match any string of zero or more characters.
ANY | SOME | ALL
Apply a comma-delimited list of patterns, where:
ANY and SOME return true if any pattern matches, equivalent to logical OR. These options are synonyms.
ALL returns true only if all patterns match, equivalent to logical AND.
ESCAPE char
Escape character, by default backslash (\), used to escape reserved characters: wildcard characters (underscore and percent), and the escape character itself.
This option is enforced only for non-default collations; it is currently unsupported with ANY/ALL pattern matching.
Note
Backslash is not valid for binary data type characters. To embed an escape character for binary data types, use ESCAPE to specify a valid binary character.
Substitute symbols
You can substitute the following symbols for LIKE and its variants:
Note
ESCAPE usage is not valid for these symbols.
Symbol
Eqivalent to:
~~
LIKE
~#
LIKEB
~~*
ILIKE
~#*
ILIKEB
!~~
NOT LIKE
!~#
NOT LIKEB
!~~*
NOT ILIKE
!~#*
NOT ILIKEB
Pattern matching
LIKE and its variants require that the entire string expression match the specified patterns. To match a sequence of characters anywhere within a string, the pattern must start and end with a percent sign.
LIKE does not ignore trailing white space characters. If the data values to match end with an indeterminate amount of white space, append the wildcard character % to pattern.
Locale dependencies
In the default locale, LIKE and ILIKE handle UTF-8 character-at-a-time, locale-insensitive comparisons. ILIKE handles language-independent case-folding.
In non-default locales, LIKE and ILIKE perform locale-sensitive string comparisons, including some automatic normalization, using the same algorithm as the = operator on VARCHAR types.
ESCAPE expressions evaluate to exactly one octet—or one UTF-8 character for non-default locales.
Examples
Basic pattern matching
The following query searches for names with a common prefix:
LIKE operators support the keywords ANY and ALL, which let you specify multiple patterns to test against a string expression. For example, the following query finds all names that begin or end with the letter 'A':
In the following query, NOT LIKE ANY ('A%', '%a') is equivalent to NOT LIKE 'A%' OR NOT LIKE '%a', so the only result that is eliminated is Anna, which matches both patterns:
=>SELECTnameFROMpeopleWHEREnameNOTLIKEANY('A%','%a');name-----------AliceAnnAnnMarieRichardRobRobertRoberta(7rows)--- same results:=>SELECTnameFROMpeopleWHEREnameNOTLIKE'A%'ORnameNOTLIKE'%a';
NOT LIKE ALL eliminates results that satisfy any pattern:
=>SELECTnameFROMpeopleWHEREnameNOTLIKEALL('A%','%a');name---------RichardRobRobert(3rows)--- same results:=>SELECTnameFROMpeopleWHEREnameNOTLIKE'A%'ANDnameNOTLIKE'%a';
Pattern matching in locales
The following example illustrates pattern matching in locales.
=>\localedefaultINFO2567:Canonicallocale:'en_US'Standardcollation:'LEN_KBINARY'English(UnitedStates)=>CREATETABLEsrc(c1VARCHAR(100));=>INSERTINTOsrcVALUES(U&'\00DF');--The sharp s (ß)=>INSERTINTOsrcVALUES('ss');=>COMMIT;
Querying the src table in the default locale returns both ss and sharp s.
=>SELECT*FROMsrc;c1----ßss(2rows)
The following query combines pattern-matching predicates to return the results from column c1: