Queries

Queries are database operations that retrieve data from one or more tables or views. In Vertica, the top-level SELECT statement is the query, and a query nested within another SQL statement is called a subquery.

Vertica is designed to run the same SQL standard queries that run on other databases. However, there are some differences between Vertica queries and queries used in other relational database management systems.

The Vertica transaction model is different from the SQL standard in a way that has a profound effect on query performance. You can:

In Vertica, the primary structure of a SQL query is its statement. Each statement ends with a semicolon, and you can write multiple queries separated by semicolons; for example:

=> CREATE TABLE t1( ..., date_col date NOT NULL, ...);
=> CREATE TABLE t2(..., state VARCHAR NOT NULL, ...);

Multiple Instances of Dimension Tables in the FROM Clause

The same dimension table can appear multiple times in a query's FROM clause, using different aliases. For example:

=> SELECT * FROM   fact, dimension d1, dimension d2
WHERE  fact.fk = d1.pk
       AND 
       fact.name = d2.name;