2.13 SELECT Statement

SELECT statement clauses that are only available in the HeatWave secondary engine.

The GROUP BY clause permits the following:

• A CUBE modifier in addition to the WITH ROLLUP modifier.

• A ROLLUP modifier as a preferred alternative to the WITH ROLLUP modifier.

See: Section 2.12.1.1, “GROUP BY Modifiers”.

The SELECT statement includes the QUALIFY clause. This is between the WINDOW clause and the ORDER BY clause:

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY [CUBE] [ROLLUP] {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [QUALIFY qualify_condition]
    [ORDER BY [CUBE] [ROLLUP] {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

For a full explanation of the SELECT syntax, see: SELECT Statement.

In addition to constraints similar to the HAVING clause, the QUALIFY clause can also include predicates related to a window function.

Similar to the HAVING clause, the QUALIFY clause can refer to aliases mentioned in the SELECT list.

The QUALIFY clause requires the inclusion of at least one window function in the query. The window function can be part of any one of the following:

• The SELECT column list.

• A filter predicate of the QUALIFY clause.

The following query uses SUM() as a window function:

mysql> SELECT
          year, country, product, profit,
          SUM(profit) OVER() AS total_profit,
          SUM(profit) OVER(PARTITION BY country) AS country_profit
          FROM sales
          ORDER BY country, year, product, profit;
+------+---------+------------+--------+--------------+----------------+
| year | country | product    | profit | total_profit | country_profit |
+------+---------+------------+--------+--------------+----------------+
| 2000 | Finland | Computer   |   1500 |         7535 |           1610 |
| 2000 | Finland | Phone      |    100 |         7535 |           1610 |
| 2001 | Finland | Phone      |     10 |         7535 |           1610 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Calculator |     75 |         7535 |           1350 |
| 2000 | India   | Computer   |   1200 |         7535 |           1350 |
| 2000 | USA     | Calculator |     75 |         7535 |           4575 |
| 2000 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | Calculator |     50 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1200 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | TV         |    100 |         7535 |           4575 |
| 2001 | USA     | TV         |    150 |         7535 |           4575 |
+------+---------+------------+--------+--------------+----------------+

The same query, but now with a QUALIFY clause that only returns results where country_profit is greater than 1,500:

mysql> SELECT
          year, country, product, profit,
          SUM(profit) OVER() AS total_profit,
          SUM(profit) OVER(PARTITION BY country) AS country_profit
          FROM sales
          QUALIFY  country_profit  > 1500
          ORDER BY country, year, product, profit;
+------+---------+------------+--------+--------------+----------------+
| year | country | product    | profit | total_profit | country_profit |
+------+---------+------------+--------+--------------+----------------+
| 2000 | Finland | Computer   |   1500 |         7535 |           1610 |
| 2000 | Finland | Phone      |    100 |         7535 |           1610 |
| 2001 | Finland | Phone      |     10 |         7535 |           1610 |
| 2000 | USA     | Calculator |     75 |         7535 |           4575 |
| 2000 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | Calculator |     50 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1200 |         7535 |           4575 |
| 2001 | USA     | Computer   |   1500 |         7535 |           4575 |
| 2001 | USA     | TV         |    100 |         7535 |           4575 |
| 2001 | USA     | TV         |    150 |         7535 |           4575 |
+------+---------+------------+--------+--------------+----------------+

The SELECT statement includes the TABLESAMPLE clause, which only applies to base tables:

SELECT
    select_expr [, select_expr] ...
    [into_option]
    FROM table_references
      [PARTITION partition_list]
    TABLESAMPLE { SYSTEM | BERNOULLI } ( sample_percentage )

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

The TABLESAMPLE clause retrieves a sample of the data in a table with the SYSTEM or BERNOULLI sampling method.

The sample_percentage can be an integer, decimal or float numeric value.

SYSTEM sampling samples chunks, and each chunk has a random number. Bernoulli sampling samples rows, and each row has a random number. If the random number is less than or equal to the sample_percentage, the chunk or row is chosen for subsequent processing.

A TABLESAMPLE example that counts the number of rows from a join of a 10% sample of the LINEITEM table with a 10% sample of the ORDERS table. It applies TABLESAMPLE with the SYSTEM sampling method to both base tables.

mysql> SELECT COUNT(*)
        FROM LINEITEM TABLESAMPLE SYSTEM (10), ORDERS TABLESAMPLE SYSTEM (10)
        WHERE L_ORDERKEY=O_ORDERKEY;