SQL Statement Syntax

Query service supports requests built using standard SELECT statements with following clauses.

The syntax for the SELECT statement.

The expression can be columns or calculations that you want to retrieve. Use ALL or the asterisk wildcard character (*) with the SELECT clause to retrieve all the columns. To retrieve unique records, use the SELECT DISTINCT clause.

CREATE, INSERT, DROP, or DELETE statements aren’t applicable to Query service.

Let's discuss the clauses supported in the SELECT statement.

Use the FROM clause to specify the source from which data is to be read. The possible data sources can be:

  • Data Lake Objects (DLOs), Data Model Object (DMOs), Calculated Insights Objects (CIOs).
  • Joins on DLOs, DMOs, CIOs. For more information on Joins, see SQL Joins.

Here’s the basic syntax of the SELECT statement with the FROM clause.

For example,

WITH

Use the WITH clause to declare a temporary data set whose output is available in the subsequent queries for reference and reuse. Here is the general syntax of the SELECT statement with the WITH clause.

For example,

Let's use the Catalog_sales__dll table as an example to illustrate the WITH clause.

Example query

Query output

You can declare any number of temporary data sets using WITH clause and reference them in the main query. Refer to JOIN clause to learn more about retrieving data from one or more temporary data sets.

Use the WHERE clause to specify the conditions that must be met for the records to be selected. Here’s the basic syntax of the SELECT statement with the WHERE clause.

For example,

Use the GROUP BY clause in the SELECT statement to aggregate identical data into groups. Here's the general syntax of the SELECT statement with the GROUP BY clause.

The output expression of the SELECT statement when a GROUP BY clause is used can be an aggregate function or a column that's used in the GROUP BY clause.

The grouping_element in a GROUP BY clause can be GROUPING SETS, CUBE, or ROLLUP.

Let's use the Catalog_sales__dll table to understand the GROUP BY clause.

Example query

Query output

GROUP BY clause supports three operators:

  • GROUPING SETS - Use them to specify multiple group-by clauses in a single SELECT statement. A grouping-set is a list of columns by which you group your result sets in the GROUP BY clause. For example, to know a grouped count of o_cityc andd_cityc in Catalog_salesdll table, we can create a grouping-set (o_cityc, d_city__c).

  • CUBE - Use the CUBE operator to generate all possible grouping sets for a given set of columns. For example, to find sales numbers per state and to find total quantity sold

  • ROLLUP- Use the ROLLUP operator to generate all possible subtotals for a given set of columns. For example,

Use the HAVING clause to filter the results from a GROUP BY clause. A HAVING clause eliminates groups that don’t satisfy the specified conditions.

The basic syntax of the SELECT statement with the HAVING clause.

Use the ORDER BY clause to sort the results of a query in ascending or descending order.

Use the OFFSET clause to drop specified number of leading rows from the retrieved results.

Use the LIMIT clause in the SELECT statement to restrict the number of rows in the result set.