SQL clauses and statements

When you enter your query in the SQL Editor or create a calculation, you use Presto SQL syntax. For example, in the SQL Editor, you may compose a complete SELECT query to find the sources to include and then filter their data:

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM source
    JOIN another_source
      ON source.column = another_source.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT

Note: To ensure data integrity, the SQL editor supports read-only statements such as SELECT, but not read/write statements like INSERT or DELETE.

In addition to these clauses and statements, your SQL syntax can contain several functions and operators to further define the results and perform calculations:

• Text string functions
• Date and time functions
• Conversion functions
• Conditional expressions
• Aggregate and mathematical functions and operators
• Comparison functions and operators

Order of operations

While not every query includes every clause, each part of the SQL statement executes in a particular sequence:

• FROM and JOIN, to determine the sources of the data being queried
• WHERE, to include only data that meets specific criteria
• GROUP BY, to summarize data based on common values
• HAVING, to include only data from aggregated rows that meet specific criteria
• SELECT or SELECT DISTINCT, to further specify which source data to include
• ORDER BY, to specify how to sort the data in the results
• LIMIT and OFFSET, to specify how many—and which—records from the query to include

Understanding the order in which the query executes its SQL syntax can help you identify the results available at each step.

FROM and JOIN

To determine the data being queried, the query first executes the FROM command, which specifies the source to select data from.

To include data from multiple sources, include a JOIN clause to combine the sources' rows based a related column:

• INNER JOIN selects records matched in both sources.
• LEFT JOIN selects all records from the left source, and also any matched in the right source.
• RIGHT JOIN selects all records from the right source, and also any matched in the left source.
• FULL JOIN selects all records in either source.

To compile data from more than two sources, you can apply multiple JOIN clauses to combine the sources in order.  For example, to combine orders with data about their customers and shippers: 

SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID)

Note: In Query Builder, you create a JOIN clause as a relationship.

WHERE

After the query determines its sources based on the FROM and JOIN clauses, it executes the WHERE clause to filter the results to only data that meets specific criteria.

GROUP BY

If the query contains aggregate functions, it may include a GROUP BY statement to summarize its rows based on common values in the column specified, such as GROUP BY column name(s). 

HAVING

If the query includes a GROUP BY statement, it applies the constraints of the HAVING clause. Similar to WHERE, HAVING includes only data from aggregated rows that meet the specific criteria and discards any rows that don't.

SELECT or SELECT DISTINCT

After the query applies the constraints of the WHERE clause to the sources determined by the FROM and JOIN clauses and groups rows based on GROUP BY statement and HAVING clause, it applies any expressions in the SELECT statement to further specify the data to include. For example:

• SELECT column1, column2 FROM source returns only fields column1 and column2 from source.
• SELECT * FROM source returns all fields from source.

If the query includes a SELECT DISTINCT statement, it then excludes any rows with duplicate values from each column.

ORDER BY

If the query includes an ORDER BY keyword, it sorts the results in ascending or descending order—ASC or DESC—based on the specified column. For example, ORDER BY field_header ASC. 

To sort according to multiple fields, separate each with a comma, such as ORDER BY field_header1 ASC, field_header2 DESC.

Note: The default value of ORDER BY is ASC. If you don't specify ASC or DESC following the field name in the ORDER BY statement, it automatically sorts in ascending order.

LIMIT and OFFSET

Finally, the query discards any rows that fall outside the range of its LIMIT and OFFSET clauses.

• To return a specific number of records in the results, include a LIMIT clause. For example, to return the first 30 records from a source, SELECT * FROM source LIMIT 30.
• To return a specific number of records but not start with the first record in the source, add an OFFSET clause with the number of records to skip. For example, to return ten records starting with the 16th, SELECT * FROM source LIMIT 10 OFFSET 15.