SELECT

Specifies one or more source tables from which to retrieve rows.

Syntax

FROM table-reference [ , table-reference ] ...

Syntax

table-primary | joined-table

Syntax

{ table-name [ AS ] alias

[ ( column-alias [ , ...] ) ] [ , ...] ]

| ( joined-table ) }

Semantics

table-name	specifies a table in the logical schema. Vertica selects a suitable projection to use.
alias	specifies a temporary name to be used for references to the table.
column-alias	specifies a temporary name to be used for references to the column.
joined-table	specifies an outer join.

Semantics

table-primary	specifies an optionally qualified table name with optional table aliases, column aliases, and outer joins.
joined-table	specifies an outer join.

Syntax

table-primary | joined-table

Syntax

{ table-name [ AS ] alias

[ ( column-alias [ , ...] ) ] [ , ...] ]

| ( joined-table ) }

Semantics

table-name	specifies a table in the logical schema. Vertica selects a suitable projection to use.
alias	specifies a temporary name to be used for references to the table.
column-alias	specifies a temporary name to be used for references to the column.
joined-table	specifies an outer join.

Semantics

table-primary	specifies an optionally qualified table name with optional table aliases, column aliases, and outer joins.
joined-table	specifies an outer join.

Semantics

table-reference

is a table-primary or a joined-table.

Syntax

{ table-name [ AS ] alias

[ ( column-alias [ , ...] ) ] [ , ...] ]

| ( joined-table ) }

Semantics

table-name	specifies a table in the logical schema. Vertica selects a suitable projection to use.
alias	specifies a temporary name to be used for references to the table.
column-alias	specifies a temporary name to be used for references to the column.
joined-table	specifies an outer join.

Notes

If more than one table-reference is specified in the FROM list, they are joined together, in which case the first table in the list is the anchor table. The anchor table can be:
- the fact table in a star schema
- the central fact table in a snowflake schema
- a dimension table in a snowflake schema that functions as a fact table (with a restriction as described in the Snowflake Schema section of the Database Administrator's Guide).

Full Cartesian products (joins with no

join-predicate) are not allowed.

Eliminates rows from the result table that do not satisfy one or more predicates.

Syntax

WHERE boolean-expression

Semantics

boolean-expression

is an expression that returns true or false. Only rows for which the expression is true become part of the result set.

The boolean-expression can include Boolean operators and the following elements:

BETWEEN-predicate

Boolean-predicate

column-value-predicate

IN-predicate

join-predicate

LIKE-predicate

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

NULL-predicate

Notes

You can use parentheses to group expressions, predicates, and boolean operators. For example:
WHERE NOT (A=1 AND B=2) OR C=3;

GROUP BY divides a query result set into groups of rows that match an expression.

Syntax

GROUP BY expression [ ,... ]

Semantics

expression

is any expression including constants and references to columns in the tables specified in the FROM clause.

Notes

The expression cannot include aggregate functions.
All non-aggregated columns in the SELECT list must be included in the GROUP BY clause.

Examples

SELECT C1, C2

FROM T1

GROUP BY C1, C2;

SELECT C1, AVG(C2)

FROM T1

GROUP BY C1;

SELECT x+y, SUM(z)

FROM foo

GROUP BY x+y;

SELECT x+y, y+z, COUNT(*)

FROM foo

GROUP BY x+y, y+z;

SELECT RTRIM(a) || LTRIM(b), AVG(c), COUNT(c)

FROM foo

GROUP BY RTRIM(a) || LTRIM(b);

Invalid Examples

SELECT C1, C2

FROM T1

GROUP BY C1;

Eliminates group rows that do not satisfy a predicate.

Syntax

HAVING predicate [, ...]

Semantics

predicate

is the same as specified for the WHERE clause.

Notes

Each column referenced in predicate must unambiguously reference a grouping column, unless the reference appears within an aggregate function.
You can use expressions in the HAVING clause.

Sorts a query result set on one or more columns.

Syntax

ORDER BY expression [ ASC | DESC ] [, ...]

Semantics

expression

can be:

the name or ordinal number of a SELECT list item
an arbitrary expression formed from columns that do not appear in the SELECT list
a CASE expression

Notes

The ordinal number refers to the position of the result column, counting from the left beginning at one. This makes it possible to order by a column that does not have a unique name. (You can assign a name to a result column using the AS clause.)
Vertica uses the ASCII collating sequence to store data and to compare character strings. In general the order is:
- space
- numbers
- upper-case letters
- lower-case letters
Special characters collate in between and after the groups mentioned. See man ascii for details.
For integer,bigint, and date/time data types, NULL appears first (smallest) in ascending order.
For float, boolean, char, and varchar, NULL appears last (largest) in ascending order.

Specifies the maximum number of result set rows to return.

Syntax

LIMIT { rows | ALL }

Semantics

rows	specifies the maximum number of rows to return
ALL	returns all rows (same as omitting LIMIT)

Notes

When both LIMIT and OFFSET are specified, specified number of rows are skipped before starting to count the rows to be returned.
When using LIMIT, use an ORDER BY clause that includes all columns in the select list. Otherwise the query returns an undefined subset of the result set. For example:

C1	C2
1	2
2	1
1	1
2	2

SELECT *

FROM T

ORDER BY C1

LIMIT 3;

The last row of the result set in this example is undefined in any SQL database but may be consistent enough in other databases for poorly-coded application programs or reports to get the same result set every time. In Vertica however, the distributed nature of the database makes the last row unpredictable, which may cause poorly-coded application programs or reports to get different a result set each time.

Omits a specified number of rows from the beginning of the result set.

Syntax

OFFSET rows

Semantics

rows

specifies the number of result set rows to omit.

Notes

When both LIMIT and OFFSET are specified, specified number of rows are skipped before starting to count the rows to be returned.
When using OFFSET, use an ORDER BY clause. Otherwise the query returns an undefined subset of the result set.

An epoch represents committed changes to the data stored in a database between two specific points in time. In other words, an epoch contains all COPY, INSERT, UPDATE, and DELETE operations that have been executed and committed since the end of the previous epoch.

Vertica can execute a query from a snapshot of the database taken at a specific date and time. The syntax is:

AT TIME 'timestamp' SELECT...

The command queries all data in the database up to and including the epoch representing the specified date and time without holding a lock or blocking write operations.

Syntax

[ tablename. ] columnname

Semantics

tablename

is one of:

the name of a table
an alias for a table defined by means of a FROM clause in a query

columnname

is the name of a column that must be unique across all the tables being used in a query

Notes

There are no space characters in a column reference.

Mathematical operators are provided for many data types.

Operator	Description	Example	Result
+	addition	2 + 3	5
-	subtraction	2 - 3	-1
*	multiplication	2 * 3	6
/	division (integer division truncates results)	4 / 2	2
%	modulo (remainder)	5 % 4	1
^	exponentiation	2.0 ^ 3.0	8
\|/	square root	\|/ 25.0	5
\|\|/	cube root	\|\|/ 27.0	3
!	factorial	5 !	120
!!	factorial (prefix operator)	!! 5	120
@	absolute value	@ -5.0	5
&	bitwise AND	91 & 15	11
\|	bitwise OR	32 \| 3	35
#	bitwise XOR	17 # 5	20
~	bitwise NOT	~1	-2
<<	bitwise shift left	1 << 4	16
>>	bitwise shift right	8 >> 2	2

Notes

The bitwise operators work only on integer data types, whereas the others are available for all numeric data types.
Vertica supports the use of the factorial operators on positive and negative floating point (DOUBLE PRECISION) numbers as well as integers. For example:
=> select 4.98!;

?column?

------------------

115.978600750905

(1 row)

Factorial is defined as z! = gamma(z+1) for all complex numbers z. See the Handbook of Mathematical Functions (1964) Section 6.1.5.

To concatenate two strings on a single line, use the concatenation operator (two consecutive vertical bars).

Syntax

string || string

Semantics

string

is an expression of type CHAR or VARCHAR

Notes

Two consecutive strings within a single SQL statement on separate lines are concatenated.

Examples

> SELECT 'auto' || 'mobile';

?column?

----------

automobile

(1 row)

> SELECT 'auto'

> 'mobile';

?column?

----------

automobile

(1 row)

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

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

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

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