SELECT query syntax

Firebolt supports running SELECT statements with the syntax described in this topic. You can run multiple queries in a single script. Separating them with a semicolon (;) is required. Firebolt also supports CREATE TABLE...AS SELECT (CTAS). For more information, see CREATE TABLE…AS SELECT.

• Syntax
• WITH
• FROM
• JOIN
• UNNEST
• WHERE
• GROUP BY
• HAVING
• UNION [ALL]
• ORDER BY
• LIMIT
• LIMIT_DISTINCT
• OFFSET

Syntax

[ WITH <with_query> [, ...n] ]
SELECT [ DISTINCT ] {<select_expr> [, ...]}
    [ FROM <from_item> [, ...] ]
    [ WHERE <condition> ]
    [ GROUP BY <grouping_element> [, ...] ]
    [ HAVING <condition> [, ...] ]
    [ UNION <select_expr> [ ...n]
    [ ORDER BY <expression> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...] ]
    [ LIMIT <count> ]
    [ OFFSET <start> ]

WITH

The WITH clause is used for subquery refactoring so that you can specify subqueries and then reference them as part of the main query. This simplifies the hierarchy of the main query, enabling you to avoid using multiple nested sub-queries.

In order to reference the data from the WITH clause, a name must be specified for it. This name is then treated as a temporary relation table during query execution.

Each subquery can comprise a SELECT, TABLE, VALUES, INSERT, UPDATE, or DELETE statement. The WITH clause is the only clause that precedes the main query.

The primary query and the queries included in the WITH clause are all executed at the same time; WITH queries are evaluated only once every time the main query is executed, even if the clause is referred to by the main query more than once.

When using the WITH clause as part of a DML command, it must include a RETURNING clause as well, without which the main query cannot reference the WITH clause.

Materialized common table expressions (Beta)

The query hint MATERIALIZED or NOT MATERIALIZED controls whether common table expressions (CTEs) produce an internal results table that is cached in engine RAM (MATERIALIZED) or calculated each time the sub-query runs. NOT MATERIALIZED is the default. MATERIALIZED must be specified explicitly.

Materialized results can be accessed more quickly in some circumstances. By using the proper materialization hint, you can control when a CTE gets materialized and improve query performance. We recommend the MATERIALIZED hint to improve query performance in the following circumstances:

• The CTE is reused at the main query level more than once.

• The CTE is computationally expensive, producing a relatively small number of rows.

• The CTE calculation is independent of the main query, and no external optimizations from the main table are needed for it to be fast.

• The materialized CTE fits into the nodes’ ram.

Syntax

WITH <subquery_table_name> [ <column_name> [, ...n] ] AS [MATERIALIZED|NOT MATERIALIZED] <subquery>

Example

The following example retrieves all customers from the “EMEA” region, having the results of the WITH query in the temporary table emea_customrs.

The results of the main query then list the customer_name and contact_details for those customers, sorted by name.

WITH emea_customrs AS (
	SELECT
		*
	FROM
		customers
	WHERE
		region = 'EMEA'
)
SELECT
	customer_name,
	contact_details
FROM
	emea_customrs
ORDER BY
	customer_name

FROM

Use the FROM clause to list the tables and any relevant join information and functions necessary for running the query.

Syntax

FROM <from_item> [, ...n]

Example

In the following example, the query retrieves all entries from the customers table for which the region value is “EMEA”.

SELECT
	*
FROM
	customers
WHERE
	region = 'EMEA'

JOIN

A JOIN operation combines rows from two data sources, such as tables or views, and creates a new table of combined rows that can be used in a query.

JOIN operations can be used with an ON clause for conditional logic or a USING clause to specify columns to match.

JOIN with ON clause syntax

FROM <join_table1> [ INNER | LEFT | RIGHT | FULL ] JOIN <join_table2> ON <join_condition>

JOIN with USING clause syntax

FROM <join_table1> [ INNER | LEFT | RIGHT | FULL ] JOIN <join_table2> USING (column_list)

JOIN Types

The type of JOIN operation specifies which rows are included between two specified tables. If unspecified, JOIN defaults to INNER JOIN.

JOIN types include:

Examples

The JOIN examples below use two tables, num_test and num_test2. These tables are created and populated with data as follows.

CREATE DIMENSION TABLE num_test (
    firstname varchar,
    score integer);

INSERT INTO num_test VALUES
    ('Carol', 11),
    ('Albert', 50),
    ('Sammy', 90),
    ('Peter', 50),
    ('Deborah', 90),
    ('Frank', 87),
    ('Thomas', 85),
    ('Humphrey', 56);

CREATE DIMENSION TABLE num_test2 (
    firstname varchar,
    score integer);

INSERT INTO num_test2 VALUES
    ('Sammy', 90),
    ('Hector', 56),
    ('Tom', 85),
    ('Peter', 50),
    ('Carl', 100),
    ('Frank', 87),
    ('Deborah', 90),
    ('Albert', 50);

The tables and their data are shown below.

INNER JOIN example

The INNER JOIN example below includes only the rows where the firstname and score values match.

SELECT
    *
FROM
    num_test
INNER JOIN
    num_test2
    USING (
        firstname,
        score
	);

This query is equivalent to:

SELECT
    *
FROM
    num_test
INNER JOIN
    num_test2
        ON num_test.firstname = num_test2.firstname
        AND num_test.score = num_test2.score;

Returns

LEFT OUTER JOIN example

The LEFT OUTER JOIN example below includes all firstname values from the num_test table. Any rows with no matching value in the num_test2 table return NULL.

SELECT
    num_test.firstname,
    num_test2.firstname
FROM num_test
LEFT OUTER JOIN
    num_test2
    USING (firstname);

Returns

RIGHT OUTER JOIN example

The RIGHT OUTER JOIN example below includes all firstname values from num_test2. Any rows with no matching values in the num_test table return NULL.

SELECT
    num_test.firstname,
    num_test2.firstname
FROM
    num_test
RIGHT OUTER JOIN
    num_test2
    USING (firstname);

Returns

FULL OUTER JOIN example

The FULL OUTER JOIN example below includes all values from num_test and num_test2. Any rows with no matching values return NULL.

SELECT
    num_test.firstname,
    num_test2.firstname
FROM
    num_test
FULL OUTER JOIN
    num_test2
    USING (firstname);

Returns

CROSS JOIN example

A CROSS JOIN produces a table with every combination of row values in the specified columns.

This example uses two tables, crossjoin_test and crossjoin_test2, each with a single letter column. The tables contain the following data.

The CROSS JOIN example below produces a table of every possible pairing of these rows.

SELECT
    crossjoin_test.letter,
    crossjoin_test2.letter
FROM
    crossjoin_test
CROSS JOIN
    crossjoin_test2;

Returns

UNNEST

An UNNEST operator performs join between the table in the left side, and the array in the right side. The output table repeats rows of the table from the left for every element of the array. If the array is empty, the corresponding row from the table is eliminated.

Syntax

FROM <from_item> UNNEST(<array_column> [[ AS ] <alias_name>][,<array_column>...])

Example

The example is based on the following table:

CREATE FACT TABLE table_with_arrays
(
    product TEXT,
    cost ARRAY(INT)
) PRIMARY INDEX product;

Assume the table was populated and contains the following values:

The following query with UNNEST:

SELECT
	product,
	cost
FROM
	table_with_arrays UNNEST(cost);

Returns the following result:

WHERE

Use the WHERE clause to define conditions for the query in order to filter the query results. When included, the WHERE clause always follows the FROM clause as part of a command such as SELECT.

Syntax

WHERE <condition>

Example

In the following example, the query retrieves all entries from the customers table for which the region value is “EMEA”.

SELECT
	*
FROM
	customers
WHERE
	region = 'EMEA'

The following query retrieves users who registered after August 30, 2020 from the users’ table:

SELECT
	user_id,
	city,
	country
FROM
	users
WHERE
	registration_date >= TO_DATE('2020-08-30');

The following query retrieves users who registered after August 30 2020 and made a purchase:

SELECT
	user_id,
	city,
SELECT
	user_id,
	city,
	country
FROM
	users
WHERE
	registration_date >= TO_DATE('2020-08-30')
	AND user_id IN (
		SELECT
			user_id
		FROM
			purchases
	)

GROUP BY

The GROUP BY clause indicates by what column or columns the results of the query should be grouped. GROUP BY is usually used in conjunction with aggregations, such as SUM, COUNT, MIN, etc. Grouping elements can include column names, the position of columns as specified in the SELECT expression, or other expressions used in the query.

Syntax

GROUP BY <grouping_element> [, ...n]

Example

In the following example, the results that are retrieved are grouped by the product_name and then by the product_id columns.

SELECT
	product_name,
	product_id,
	sum(total_sales)
FROM
	purchases
GROUP BY
	product_name,
	product_id

You can get similar results by specifying the column positions.

SELECT
	product_name,
	product_id,
	SUM(total_sales)
FROM
	purchases
GROUP BY
	1,
	2

Other expressions can also be used, such as a CASE statement:

SELECT
	product_name,
	SUM(price),
	CASE
		WHEN price > 500 THEN "High value"
		WHEN price < 500 THEN "Low value"
	END AS price_text
FROM
	purchases
GROUP BY
	product_name,
	price_text;

HAVING

The HAVING clause is used in conjunction with the GROUP BY clause, and is computed after computing the GROUP BY clause and aggregate functions. HAVING is used to further eliminate groups that don’t satisfy the <condition> by filtering the GROUP BY results.

Syntax

HAVING <condition> [, ...n]

UNION [ALL]

The UNION operator combines the results of two or more SELECT statements into a single query.

• UNION combines with duplicate elimination.
• UNION ALL combines without duplicate elimination.

When including multiple clauses, the same number of columns must be selected by all participating SELECT statements. Data types of all column parameters must be the same. Multiple clauses are processed left to right; use parentheses to define an explicit order for processing.

Syntax

<select_expr1> UNION [ALL] <select_expr2> [ ...n]

ORDER BY

The ORDER BY clause sorts a result set by one or more output expressions. ORDER BY is evaluated as the last step after any GROUP BY or HAVING clause. ASC and DESC determine whether results are sorted in ascending or descending order. When the clause contains multiple expressions, the result set is sorted according to the first expression. Then the second expression is applied to rows that have matching values from the first expression, and so on.

The default null ordering is NULLS LAST, regardless of ascending or descending sort order.

Syntax

ORDER BY <expression> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...]

LIMIT

The LIMIT clause restricts the number of rows that are included in the result set.

Syntax

LIMIT <count>

LIMIT_DISTINCT

The LIMIT_DISTINCT clause selects a defined number of rows to return for each distinct value as specified by the expression.

Returned values will not be grouped together unless you use an ORDER BY statement. LIMIT_DISTINCT does not preclude also using a LIMIT clause.

Syntax

LIMIT_DISTINCT <returned_values> [OFFSET <offset_value>] BY <expr> [, <expr2> [,...n]]

Example

To demonstrate the LIMIT_DISTINCT clause, we will create an example table of students called class_test:

CREATE DIMENSION TABLE IF NOT EXISTS class_test3
    (
        First_name      TEXT,
        Last_name       TEXT,
        Teacher         TEXT,
        Grade_level     INT
    );

INSERT INTO class_test3
    VALUES
        ('Sammy','Sardine','Roberts', 12),
        ('Carol','Catnip','Roberts', 10),
        ('Thomas','Tinderbox','Roberts', 10),
        ('Deborah','Donut','Roberts', 9),
        ('Humphrey','Hoagie','Roberts', 11),
        ('Frank','Falafel', 'Chamberpot', 11),
        ('Peter','Patchouli', 'Chamberpot', 11),
        ('Albert','Applesauce','Chamberpot', 11),
        ('Gary','Garnish', 'Chamberpot', 12),
        ('Roseanna','Rotisserie','Chamberpot', 12),
        ('Jesse','Jelloshot','Chamberpot', 12),
        ('Mary','Marblecake','Diddlysquat', 9),
        ('Iris','Icecream','Diddlysquat', 9),
        ('Larry','Lardbelly','Diddlysquat', 9),
        ('Charles','Cokebottle','Diddlysquat', 10),
        ('Brunhilda','Breadknife','Diddlysquat', 10),
        ('Franco','Frenchdip','Rosebottom', 11),
        ('Yolinda','Yogurt','Rosebottom', 11),
        ('Jojo','Jujubee','Rosebottom', 11),
        ('Shangxiu','Shadytree','Rosebottom', 12),
        ('Otis','Oatmeal','Rosebottom', 12),
        ('Wanda','Waterfall','Rosebottom', 10),
        ('Shawn','Sharpshooter','Rosebottom', 10)

The example below uses LIMIT_DISTINCT to get a subset of each teacher’s students. For each teacher, only two students are included in the query results.

SELECT
	Teacher,
	Student_Firstname,
	Student_Lastname
FROM
	class_test
ORDER BY
	Teacher
LIMIT_DISTINCT 2 BY
	Teacher;

Returns:

+--------------------------------------------+
| Teacher,Student_Firstname,Student_Lastname |
+--------------------------------------------+
| Chamberpot,Peter,Patchouli                 |
| Chamberpot,Roseanna,Rotisserie             |
| Diddlysquat,Mary,Marblecake                |
| Diddlysquat,Larry,Lardbelly                |
| Roberts,Carol,Catnip                       |
| Roberts,Sammy,Sardine                      |
| Rosebottom,Wanda,Waterfall                 |
| Rosebottom,Shawn,Sharpshooter              |
+--------------------------------------------+

LIMIT_DISTINCT can also include multiple expressions to limit for distinct values. The example below returns a subset of each teacher’s students in each grade level.

SELECT
	Teacher,
	Grade_level,
	First_name,
	Last_name
FROM
	class_test3
ORDER BY
	Teacher
LIMIT_DISTINCT 1 BY
	Teacher,
	Grade_level;

Returns:

+----------------------------------------------------------+
| Teacher,Grade_level, Student_Firstname, Student_Lastname |
+----------------------------------------------------------+
| Chamberpot,11,Peter,Patchouli                            |
| Chamberpot,12,Gary,Garnish                               |
| Diddlysquat,9,Mary,Marblecake                            |
| Diddlysquat,10,Charles,Cokebottle                        |
| Roberts,12,Sammy,Sardine                                 |
| Roberts,11,Humphrey,Hoagie                               |
| Roberts,10,Carol,Catnip                                  |
| Roberts,9,Deborah,Donut                                  |
| Rosebottom,12,Otis,Oatmeal                               |
| Rosebottom,10,Shawn,Sharpshooter                         |
| Rosebottom,11,Jojo,Jujubee                               |
+----------------------------------------------------------+

OFFSET

The OFFSET clause omits a specified number of rows from the beginning of the result set. When used with LIMIT, it specifies the row number after which the limited rows are returned. When used with FETCH, only the number of rows specified by the <fetch_count> are returned after the OFFSET

Syntax

[ LIMIT <limit_number> ] OFFSET <offset_number> [ FETCH <fetch_count>]  

In the following example, we will retrieve 3 rows, starting from the 2nd row.

SELECT
	product_name,
	product_id
FROM
	purchases
ORDER BY
	1
LIMIT
	3 OFFSET 1;