This is the multi-page printable view of this section. Click here to print.
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 example WHERE clause:
WHERE name = 'Smith';
name = 'Smith' is the predicate
'Smith' is an expression
The special BETWEEN predicate is available as a convenience.
WHERE a BETWEEN x AND y
WHERE a BETWEEN x AND y
is equivalent to:
WHERE a >= x AND a <= y
Similarly:
WHERE a NOT BETWEEN x AND y
is equivalent to:
WHERE a < x OR a > y
You can use the BETWEEN predicate for date ranges:
=> CREATE TABLE t1 (c1 INT, c2 INT, c3 DATE);
=> COPY t1 FROM stdin DELIMITER '|';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1 | 2 | 2014-07-26
>> 2 | 3 | 2014-07-27
>> 3 | 4 | 2014-07-28
>> 4 | 5 | 2014-07-29
>> 5 | 6 | 2014-07-30
>> 6 | 7 | 2014-07-31
>> 7 | 8 | 2014-08-01
>> 8 | 9 | 2014-08-02
>> \.
=> SELECT* FROM t1 WHERE c3 BETWEEN DATE('2014-07-26') AND DATE('2014-07-30');
c1 | c2 | c3
----+----+------------
1 | 2 | 2014-07-26
2 | 3 | 2014-07-27
3 | 4 | 2014-07-28
4 | 5 | 2014-07-29
5 | 6 | 2014-07-30
(5 rows)
You can also use the NOW and INTERVAL keywords to select from a date range:
=> SELECT * FROM t1 WHERE c3 BETWEEN NOW()-INTERVAL '1 week' AND NOW();
c1 | c2 | c3
----+----+------------
7 | 8 | 2014-08-01
1 | 2 | 2014-07-26
2 | 3 | 2014-07-27
3 | 4 | 2014-07-28
4 | 5 | 2014-07-29
5 | 6 | 2014-07-30
6 | 7 | 2014-07-31
(7 rows)
Retrieves rows where the value of an expression is true, false, or unknown (null).
expression IS [NOT] TRUE
expression IS [NOT] FALSE
expression IS [NOT] UNKNOWN
A 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.
column-name comparison-op constant-expression
column-name |
A single column of one the tables specified in the FROM clause. |
comparison-op |
A Comparison operators. |
constant-expression |
A constant value of the same data type as the column-name. |
To check a column value for NULL, use the NULL predicate.
table.column1 = 2
table.column2 = 'Seafood'
table.column3 IS NULL
(column-list) [ NOT ] IN ( values-list )
column-list |
One or more comma-delimited columns in the queried tables. |
values-list |
Comma-delimited list of constant values to find in the You can specify multiple sets of values as follows:
|
The following SELECT statement queries all data in table t11.
=> SELECT * FROM t11 ORDER BY pk;
pk | col1 | col2 | SKIP_ME_FLAG
----+------+------+--------------
1 | 2 | 3 | t
2 | 3 | 4 | t
3 | 4 | 5 | f
4 | 5 | 6 | f
5 | 6 | 7 | t
6 | | 8 | f
7 | 8 | | t
(7 rows)
The following query specifies an IN predicate, to find all rows in t11 where columns col1 and col2 contain values of (2,3) or (6,7):
=> SELECT * FROM t11 WHERE (col1, col2) IN ((2,3), (6,7)) ORDER BY pk;
pk | col1 | col2 | SKIP_ME_FLAG
----+------+------+--------------
1 | 2 | 3 | t
5 | 6 | 7 | t
(2 rows)
Used to join two event series together 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.
expression1 INTERPOLATE PREVIOUS VALUE expression2
expression1expression2 |
A column reference from one the tables specified in the FROM clause. The referenced columns are typically a |
PREVIOUS VALUE |
Pads the non-preserved side with the previous values from relation when there is no match. Input rows are sorted in ascending logical order of the join column. NoteAn |
An event series join is an extension of a regular outer join. 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 values from the table.
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 the Examples section below Notes and Restrictions. See also Event series joins.
Data is logically partitioned on the table in which it resides, based on other ON clause equality predicates.
Interpolated values come from the table that contains the null, not from the other table.
Vertica does not guarantee that there will be no null values in the output. If there is no previous value for a mismatched row, that row will be padded with nulls.
Event series join requires that both tables be 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: exchange, stock, ts, pricebid: exchange,
stock, ts, price
In the query that follows
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;
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.
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('14:40:25');
INSERT INTO t VALUES('14:45:00');
INSERT INTO t VALUES('14:49:55');
INSERT INTO t1 VALUES('12:40:23');
INSERT INTO t1 VALUES('14:00:00');
COMMIT;
=> 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
14:40:25 |
14:45:00 |
14:49:55 |
| 14:00:00
(5 rows)
=> 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.
x | y
----------+----------
12:40:23 | 12:40:23
12:40:23 | 14:00:00
14:40:25 | 14:00:00
14:45:00 | 14:00:00
14:49:55 | 14:00:00
(5 rows)
=> 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
14:40:25 |
14:45:00 |
14:49:55 |
(4 rows)
=> SELECT * FROM t LEFT OUTER JOIN t1 ON t.x INTERPOLATE
PREVIOUS VALUE t1.y;
Nulls padded with interpolated values.
x | y
----------+----------
12:40:23 | 12:40:23
14:40:25 | 14:00:00
14:45:00 | 14:00:00
14:49:55 | 14:00:00
(4 rows)
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)
When you write an event series join in place of normal join, values are evaluated as follows (using the schema in the above examples):
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.
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.
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.
ON column-ref = column-ref [ {AND | OR | NOT } column-ref = column-ref ]...
| column-ref |
Specifies a column in a queried table. For best performance, do not join on LONG VARBINARY and LONG VARCHAR columns. |
Retrieves rows where the string value of a column matches a specified pattern. The pattern can contain one or more wildcard characters.
string-expression [ NOT ] { LIKE | ILIKE | LIKEB | ILIKEB } 'pattern' [ESCAPE 'escape-character' ]
string-expression |
The column values to search for pattern. |
NOT |
Returns true if LIKE returns false, and the reverse; equivalent to NOT string LIKE pattern. |
pattern |
Specifies what strings to match, where
|
ESCAPE escape-character |
Specifies an escape character, used in the to escape reserved characters underscore ( If you omit this parameter, you can use Vertica's default escape character, backslash (), which is valid for NoteBackslash is not valid for binary data types character. To embed an escape character for binary data types, use |
You can substitute the following symbols for LIKE and its variants:
~~ |
LIKE |
~# |
LIKEB |
~~* |
ILIKE |
~#* |
ILIKEB |
!~~ |
NOT LIKE |
!~# |
NOT LIKEB |
!~~* |
NOT ILIKE |
!~#* |
NOT ILIKEB |
ESCAPE is not valid for the above symbols.
LIKE requires that the entire string expression match the pattern. 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.
The LIKE predicate is compliant with the SQL standard. Vertica also supports several non-standard variants, notably ILIKE , which is equivalent to LIKE except it performs case-insensitive searches. The following differences pertain to LIKE and its variants:
LIKE operates on UTF-8 character strings. Exact behavior depends on collation parameters such as strength. In particular, ILIKE works by setting S=2 (ignore case) in the current session locale.
LIKE and ILIKE are stable for character strings, but immutable for binary strings, while LIKEB and ILIKEB are immutable for both cases.
LIKEB and ILIKEB predicates do byte-at-a-time ASCII comparisons.
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.
The following example illustrates pattern matching in locales.
\locale default=> CREATE TABLE src(c1 VARCHAR(100));
=> INSERT INTO src VALUES (U&'\00DF'); --The sharp s (ß)
=> INSERT INTO src VALUES ('ss');
=> COMMIT;
Querying the src table in the default locale returns both ss and sharp s.
=> SELECT * FROM src;
c1
----
ß
ss
(2 rows)
The following query combines pattern-matching predicates to return the results from column c1:
=> SELECT c1, c1 = 'ss' AS equality, c1 LIKE 'ss'
AS LIKE, c1 ILIKE 'ss' AS ILIKE FROM src;
c1 | equality | LIKE | ILIKE
----+----------+------+-------
ß | f | f | f
ss | t | t | t
(2 rows)
The next query specifies unicode format for c1:
=> SELECT c1, c1 = U&'\00DF' AS equality,
c1 LIKE U&'\00DF' AS LIKE,
c1 ILIKE U&'\00DF' AS ILIKE from src;
c1 | equality | LIKE | ILIKE
----+----------+------+-------
ß | t | t | t
ss | f | f | f
(2 rows)
Now change the locale to German with a strength of 1 (ignore case and accents):
\locale LDE_S1
=> SELECT c1, c1 = 'ss' AS equality,
c1 LIKE 'ss' as LIKE, c1 ILIKE 'ss' AS ILIKE from src;
c1 | equality | LIKE | ILIKE
----+----------+------+-------
ß | t | t | t
ss | t | t | t
(2 rows)
This example illustrates binary data types with pattern-matching predicates:
=> CREATE TABLE t (c BINARY(1));
=> INSERT INTO t values(HEX_TO_BINARY('0x00'));
=> INSERT INTO t values(HEX_TO_BINARY('0xFF'));
=> SELECT TO_HEX(c) from t;
TO_HEX
--------
00
ff
(2 rows)
select * from t;
c
------
\000
\377
(2 rows)
=> SELECT c, c = '\000', c LIKE '\000', c ILIKE '\000' from t;
c | ?column? | ?column? | ?column?
------+----------+----------+----------
\000 | t | t | t
\377 | f | f | f
(2 rows)
=> SELECT c, c = '\377', c LIKE '\377', c ILIKE '\377' from t;
c | ?column? | ?column? | ?column?
------+----------+----------+----------
\000 | f | f | f
\377 | t | t | t
(2 rows)
Tests for null values.
value_expression IS [ NOT ] NULL
value_expression |
A column name, literal, or function. |
Column name:
=> SELECT date_key FROM date_dimension WHERE date_key IS NOT NULL;
date_key
----------
1
366
1462
1097
2
3
6
7
8
...
Function:
=> SELECT MAX(household_id) IS NULL FROM customer_dimension;
?column?
----------
f
(1 row)
Literal:
=> SELECT 'a' IS NOT NULL;
?column?
----------
t
(1 row)