SELECT syntax¶

The queries in DataFusion scan data from tables and return 0 or more rows. In this documentation we describe the SQL syntax in DataFusion.

DataFusion supports the following syntax for queries:


[ WITH with_query [, …] ] 

SELECT [ ALL | DISTINCT ] select_expr [, …] 

[ FROM from_item [, …] ] 

[ WHERE condition ] 

[ GROUP BY grouping_element [, …] ] 

[ HAVING condition] 

[ UNION [ ALL | select ] 

[ ORDER BY expression [ ASC | DESC ][, …] ] 

[ LIMIT count ]

WITH clause¶

A with clause allows to give names for queries and reference them by name.

WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
SELECT a, b FROM x;

SELECT clause¶

Example:

SELECT a, b, a + b FROM table

The DISTINCT quantifier can be added to make the query return all distinct rows. By default ALL will be used, which returns all the rows.

SELECT DISTINCT person, age FROM employees

FROM clause¶

Example:

SELECT t.a FROM table AS t

WHERE clause¶

Example:

SELECT a FROM table WHERE a > 10

GROUP BY clause¶

Example:

SELECT a, b, MAX(c) FROM table GROUP BY a, b

HAVING clause¶

Example:

SELECT a, b, MAX(c) FROM table GROUP BY a, b HAVING MAX(c) > 10

UNION clause¶

Example:

SELECT
    a,
    b,
    c
FROM table1
UNION ALL
SELECT
    a,
    b,
    c
FROM table2

ORDER BY clause¶

Orders the results by the referenced expression. By default it uses ascending order (ASC). This order can be changed to descending by adding DESC after the order-by expressions.

Examples:

SELECT age, person FROM table ORDER BY age;
SELECT age, person FROM table ORDER BY age DESC;
SELECT age, person FROM table ORDER BY age, person DESC;

LIMIT clause¶

Limits the number of rows to be a maximum of count rows. count should be a non-negative integer.

Example:

SELECT age, person FROM table
LIMIT 10

SQL Reference DDL