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
Order of Operations
While not every query includes every clause, each part of the SQL statement executes in a particular sequence:
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 DISTINCT, to further specify which source data to include
ORDER BY, to specify how to sort the data in the results
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.
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 JOINselects records matched in both sources.
LEFT JOINselects all records from the left source, and also any matched in the right source.
RIGHT JOINselects all records from the right source, and also any matched in the left source.
FULL JOINselects 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.
After the query determines its sources based on the
JOIN clauses, it executes the
WHERE clause to filter the results to only data that meets specific criteria.
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).
If the query includes a
GROUP BY statement, it applies the constraints of the
HAVING clause. Similar to
HAVING includes only data from aggregated rows that meet the specific criteria and discards any rows that don't.
After the query applies the constraints of the
WHERE clause to the sources determined by the
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 sourcereturns only fields
SELECT * FROM sourcereturns all fields from
If the query includes a
SELECT DISTINCT statement, it then excludes any rows with duplicate values from each column.
If the query includes an
ORDER BY keyword, it sorts the results in ascending or descending order—
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
DESC following the field name in the
ORDER BY statement, it automatically sorts in ascending order.
Finally, the query discards any rows that fall outside the range of its
- To return a specific number of records in the results, include a
LIMITclause. 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
OFFSETclause with the number of records to skip. For example, to return ten records starting with the 16th,
SELECT * FROM source LIMIT 10 OFFSET 15.
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