MaxCompute:SELECT execution order

How it works

A MaxCompute SELECT statement supports nine clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, DISTRIBUTE BY, SORT BY, and LIMIT.

These clauses are processed in one of two sequences, depending on which clauses are present.

Sequence 1 — used when GROUP BY, HAVING, or ORDER BY is present:

Sequence 2 — used when DISTRIBUTE BY or SORT BY is present:

ORDER BY and GROUP BY cannot be used together with DISTRIBUTE BY or SORT BY in the same query.

Write SELECT statements in execution order

MaxCompute lets you write a SELECT statement with clauses in execution order, placing FROM before SELECT. This makes the logical flow explicit and easier to read.

Standard syntax (write order):

SELECT [ALL | DISTINCT] <select_expr>, <select_expr>, ...
FROM <table_reference>
[WHERE <where_condition>]
[GROUP BY <col_list>]
[HAVING <having_condition>]
[ORDER BY <order_condition>]
[DISTRIBUTE BY <distribute_condition> [SORT BY <sort_condition>]]
[LIMIT <number>]

Execution-order syntax (FROM first):

FROM <table_reference>
[WHERE <where_condition>]
[GROUP BY <col_list>]
[HAVING <having_condition>]
SELECT [ALL | DISTINCT] <select_expr>, <select_expr>, ...
[ORDER BY <order_condition>]
[DISTRIBUTE BY <distribute_condition> [SORT BY <sort_condition>]]
[LIMIT <number>]

Both forms produce identical results. The FROM-first form is recommended when you want to make the execution sequence visible in the code.

Sample data

The examples in this topic use the sale_detail table. Run the following statements to create and populate it:

-- Create a partitioned table
CREATE TABLE IF NOT EXISTS sale_detail
(
  shop_name     STRING,
  customer_id   STRING,
  total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

-- Add partitions
ALTER TABLE sale_detail ADD PARTITION (sale_date='2013', region='china') PARTITION (sale_date='2014', region='shanghai');

-- Insert data into the 2013/china partition
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china')
VALUES ('s1','c1',100.1),('s2','c2',100.2),('s3','c3',100.3);

-- Insert data into the 2014/shanghai partition
INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai')
VALUES ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);

To verify the inserted data:

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail;

Result:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

Querying a partitioned table without specifying a partition requires SET odps.sql.allow.fullscan=true; before the statement. In production, specify partitions directly to avoid a full table scan.

Examples

-- Standard write order
SET odps.sql.allow.fullscan=true;
SELECT region, MAX(total_price)
FROM sale_detail
WHERE total_price > 100
GROUP BY region
HAVING SUM(total_price) > 300.5
ORDER BY region
LIMIT 5;

-- Execution order (FROM first) — equivalent to the query above
FROM sale_detail
WHERE total_price > 100
GROUP BY region
HAVING SUM(total_price) > 300.5
SELECT region, MAX(total_price)
ORDER BY region
LIMIT 5;

+------------+------------+
| region     | _c1        |
+------------+------------+
| china      | 100.3      |
+------------+------------+

-- Standard write order
SET odps.sql.allow.fullscan=true;
SELECT shop_name,
       total_price,
       region
FROM   sale_detail
WHERE  total_price > 100.2
DISTRIBUTE BY region
SORT BY total_price;

-- Execution order (FROM first) — equivalent to the query above
FROM   sale_detail
WHERE  total_price > 100.2
SELECT shop_name,
       total_price,
       region
DISTRIBUTE BY region
SORT BY total_price;

+------------+-------------+------------+
| shop_name  | total_price | region     |
+------------+-------------+------------+
| s3         | 100.3       | china      |
| s6         | 100.4       | shanghai   |
| s7         | 100.5       | shanghai   |
+------------+-------------+------------+