Select

Select syntax

  1. SELECT
  2.     [hint_statement, ...]
  3.     [ALL | DISTINCT | DISTINCTROW | ALL EXCEPT ( col_name1 [, col_name2, col_name3, ...] )]
  4.     select_expr [, select_expr ...]
  5.     [FROM table_references
  6.       [PARTITION partition_list]
  7.       [TABLET tabletid_list]
  8.       [TABLESAMPLE sample_value [ROWS | PERCENT]
  9.         [REPEATABLE pos_seek]]
  10.     [WHERE where_condition]
  11.     [GROUP BY [GROUPING SETS | ROLLUP | CUBE] {col_name | expr | position}]
  12.     [HAVING where_condition]
  13.     [ORDER BY {col_name | expr | position}
  14.       [ASC | DESC], ...]
  15.     [LIMIT {[offset,] row_count | row_count OFFSET offset}]
  16.     [INTO OUTFILE 'file_name']

Syntax explanation

  • select_expr, ...Specifies the columns to retrieve and display in the result set. Aliases can be used, and as is optional.
  • table_referencesSpecifies the target tables for retrieval, which can be one or more tables (including temporary tables generated by subqueries).
  • where_definitionSpecifies the retrieval conditions (expressions). If a WHERE clause exists, its conditions filter the row data. The where_condition is an expression that evaluates to true for each row to be selected. If there is no WHERE clause, the statement selects all rows. In a WHERE expression, you can use any MySQL-supported functions and operators except aggregate functions.
  • ALL | DISTINCTFilters the result set. ALL selects all rows, while DISTINCT or DISTINCTROW filters out duplicate rows. The default is ALL.
  • ALL EXCEPTFilters the result set from ALL by specifying one or more column names to exclude from the full result set. All matching column names will be ignored in the output.
  • INTO OUTFILE 'file_name'Saves the result set to a new file (which must not exist beforehand), with differences in the saved format.
  • Group by havingGroups the result set by one or more columns. If HAVING is present, it filters the groups produced by GROUP BY. Extensions to GROUP BY such as GROUPING SETS, ROLLUP, and CUBE are available and detailed in the GROUPING SETS.
  • Order bySorts the final result set. ORDER BY sorts the result set by comparing values in one or more columns. Sorting operations can be time-consuming and resource-intensive because all data needs to be sent to a single node for sorting. Sorting requires more memory compared to non-sorted operations. If you need to return the top N sorted results, use the LIMIT clause.
  • Limit nLimits the number of rows in the output result set. LIMIT m,n means to start outputting from the mth row and return n records. Using LIMIT m,n is meaningful only when combined with ORDER BY, otherwise the data returned may be inconsistent each time the query is executed.
  • HavingThe HAVING clause does not filter rows in the table but filters the results produced by aggregate functions. Typically, HAVING is used with aggregate functions (such as COUNT(), SUM(), AVG(), MIN(), MAX()) and the GROUP BY clause.
  • SELECT supports explicit partition selection using PARTITION, which includes a list of partitions or subpartitions (or both) following the table name in table_reference.
  • [TABLET tids] TABLESAMPLE n [ROWS | PERCENT] [REPEATABLE seek]Limits the number of rows read from a table in the FROM clause by pseudo-randomly selecting several tablets based on the specified number of rows or percentage. REPEATABLE with a specified seed allows the same sample to be returned again. Alternatively, Tablet IDs can be manually specified, but this is only applicable to OLAP tables.
  • hint_statementUsing hints before the select list can influence the optimizer’s behavior to obtain a desired execution plan. For more information, refer to the joinHint Document.

Syntax constraints

  • SELECT can also be used to retrieve calculated rows without referencing any tables.
  • All clauses must strictly follow the above format. A HAVING clause must come after the GROUP BY clause and before the ORDER BY clause.
  • The alias keyword AS is optional. Aliases can be used in GROUP BY, ORDER BY, and HAVING.
  • WHERE clause: Executes the WHERE statement to determine which rows should be included in the GROUP BY section, while HAVING is used to determine which rows from the result set should be used.
  • The HAVING clause can reference aggregate functions, such as count, sum, max, min, avg, while the WHERE clause cannot. However, the WHERE clause can reference other functions besides aggregate functions. Column aliases cannot be used in the WHERE clause to define conditions.
  • Following GROUP BY with WITH ROLLUP allows for one or more aggregations of the results.

Join syntax

Doris supports the following JOIN syntax.

  1. JOIN
  2. table_references:
  3.     table_reference [, table_reference] …
  4. table_reference:
  5.     table_factor
  6.   | join_table
  7. table_factor:
  8.     tbl_name [[AS] alias]
  9.         [{USE|IGNORE|FORCE} INDEX (key_list)]
  10.   | ( table_references )
  11.   | { OJ table_reference LEFT OUTER JOIN table_reference
  12.         ON conditional_expr }
  13. join_table:
  14.     table_reference [INNER | CROSS] JOIN table_factor [join_condition]
  15.   | table_reference LEFT [OUTER] JOIN table_reference join_condition
  16.   | table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
  17.   | table_reference RIGHT [OUTER] JOIN table_reference join_condition
  18.   | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor
  19. join_condition:
  20.     ON conditional_expr

UNION:

  1. SELECT ...
  2. UNION [ALL| DISTINCT] SELECT ......
  3. [UNION [ALL| DISTINCT] SELECT ...]

UNION is used to combine the results of multiple SELECT statements into a single result set. The column names from the first SELECT statement are used as the column names for the returned result. The selected columns listed in the corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected in the first statement should have the same type as the first column selected in the other statements.)

By default, UNION removes duplicate rows from the result. The optional DISTINCT keyword has no effect beyond the default, as it also specifies duplicate row removal. Using the optional ALL keyword, no duplicate row removal occurs, and the result includes all matching rows from all SELECT statements.

WITH:

To specify a common table expression, use a WITH clause with one or more comma-separated subclauses. Each subclause provides a subquery that generates a result set and associates a name with the subquery. The following example defines CTEs named cte1 and cte2 in the WITH clause, and refers to them in the top-level SELECT following the WITH clause.

  1. WITH
  2.   cte1 AS (SELECT a，b FROM table1),
  3.   cte2 AS (SELECT c，d FROM table2)
  4. SELECT b，d FROM cte1 JOIN cte2
  5. WHERE cte1.a = cte2.c;

In statements that include this WITH clause, each CTE name can be referenced to access the corresponding CTE result set. CTE names can be referenced in other CTEs, allowing CTEs to be defined based on other CTEs. Currently, recursive CTEs are not supported.

Example

  • Query the names of students whose ages are 18, 20, and 25.
  1. select Name from student where age in (18,20,25);
  • ALL EXCEPT
  1. -- Query all information except for the age of the students.
  2. select * except(age) from student;
  • GROUP BY
  1. --Query the tb_book table, group by type, and calculate the average price for each category of books.
  2. select type,avg(price) from tb_book group by type;
  • DISTINCT
  1. --Query the tb_book table and remove duplicate type data.
  2. select distinct type from tb_book;
  • ORDER BY

Sort the query results in ascending order (by default) or descending order (DESC). In ascending order, NULL values should appear at the beginning, and in descending order, NULL values should appear at the end.

  1. --Query all records from the tb_book table, sort them in descending order by id, and display only the first three records.
  2. select * from tb_book order by id desc limit 3;
  • LIKE

LIKE can perform fuzzy queries with two wildcards: % and _. The % wildcard matches one or more characters, while the _ wildcard matches a single character.

  1. -- Find all books where the second character is 'h'.
  2. select * from tb_book where name like('_h%');
  • LIMIT (Limit the number of result rows.)
  1. -- Display 3 records in descending order.
  2. select * from tb_book order by price desc limit 3;
  4. Display 4 records starting from id=1
  5. select * from tb_book where id limit 1,4;
  • CONCAT (Concatenate multiple columns
  1. --Concatenate 'name' and 'price' into a new string for output.
  2. select id,concat(name,":",price) as info,type from tb_book;
  • Functions and expressions
  1. --Calculate the total price of each category of books in the tb_book table.
  2. select sum(price) as total,type from tb_book group by type;
  3. --20% off the price
  4. select *,(price * 0.8) as "20% off" from tb_book;
  • UNION
  1. SELECT a FROM t1 WHERE a = 10 AND B = 1 ORDER by a LIMIT 10
  2. UNION
  3. SELECT a FROM t2 WHERE a = 11 AND B = 2 ORDER by a LIMIT 10;
  • WITH clause
  1. WITH cte AS
  2. (
  3.   SELECT 1 AS col1, 2 AS col2
  4.   UNION ALL
  5.   SELECT 3, 4
  6. )
  7. SELECT col1, col2 FROM cte;
  • JOIN
  1. SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
  2.                  ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

the same as

  1. SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
  2.                  ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)
  • INNER JOIN
  1. SELECT t1.name, t2.salary
  2.   FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;
  4. SELECT t1.name, t2.salary
  5.   FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;
  • LEFT JOIN
  1. SELECT left_tbl.*
  2.   FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  3.   WHERE right_tbl.id IS NULL;
  • RIGHT JOIN
  1. mysql SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a);
  2. +------+------+------+------+
  3. | a    | b    | a    | c    |
  4. +------+------+------+------+
  5. |    2 | y    |    2 | z    |
  6. | NULL | NULL |    3 | w    |
  7. +------+------+------+------+
  • TABLESAMPLE
  1. --Randomly sample 1000 rows in t1 using pseudo-random method. Note that the actual process is to select several Tablets based on the statistical information of the table, and the total number of rows in the selected Tablets may be greater than 1000. Therefore, if you want to return exactly 1000 rows, you need to add a Limit clause.
  2. SELECT * FROM t1 TABLET(10001) TABLESAMPLE(1000 ROWS) REPEATABLE 2 limit 1000;

Best practice

  • Additional explanation about the SELECT clause:
    • An alias can be specified for select_expr using AS alias_name. The alias serves as the column name for the expression and can be used in GROUP BY, ORDER BY, or HAVING clauses.
    • The table_references after FROM indicate one or multiple tables involved in the query. If multiple tables are listed, a JOIN operation will be performed. Each specified table can be assigned an alias.
    • The selected columns after SELECT can be referenced in ORDER BY and GROUP BY clauses using column names, column aliases, or integers representing the column position (starting from 1).
  1. SELECT college, region, seed FROM tournament
  2.   ORDER BY region, seed;
  4. SELECT college, region AS r, seed AS s FROM tournament
  5.   ORDER BY r, s;
  7. SELECT college, region, seed FROM tournament
  8.   ORDER BY 2, 3;
  • If ORDER BY appears in a subquery and is also applied to the outer query, the outermost ORDER BY takes precedence.
  • When using GROUP BY, the grouped columns are automatically sorted in ascending order (as if an ORDER BY clause followed with the same columns). To avoid the overhead caused by the automatic sorting of GROUP BY, adding ORDER BY NULL can solve the issue:
  1. SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;
  • When sorting columns in a SELECT statement using ORDER BY or GROUP BY, the server only sorts the values using the initial number of bytes indicated by the max_sort_length system variable.
  • The HAVING clause is typically applied at the end, just before the result set is returned to the client, and it is not optimized (whereas LIMIT is applied after HAVING).
  • According to the SQL standard, HAVING must reference columns that are either in the GROUP BY list or used in aggregate functions. However, MySQL extends this by allowing HAVING to reference columns from the SELECT clause list and columns from outer subqueries.
  • If a column referenced in HAVING is ambiguous, a warning will be generated. In the following statement, col2 is ambiguous:
  1. SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

Do not use HAVING where WHERE should be used. HAVING is intended to be used with GROUP BY.

The HAVING clause can reference aggregate functions, whereas WHERE cannot.

  1. SELECT user, MAX(salary) FROM users
  2.   GROUP BY user HAVING MAX(salary)  10;
  • The LIMIT clause can be used to restrict the number of rows returned by a SELECT statement. LIMIT can have one or two parameters, both of which must be non-negative integers.
  1. -- Retrieve rows 6 to 15 from the result set.
  2. SELECT * FROM tbl LIMIT 5,10;
  3. -- If you want to retrieve all rows starting from a certain offset, you can set a very large constant as the second parameter. The following query retrieves all data starting from the 96th row:
  4. SELECT * FROM tbl LIMIT 95,18446744073709551615;
  5. -- If LIMIT has only one parameter, then the parameter specifies the number of rows that should be retrieved, and the offset is defaulted to 0, which means starting from the first row.
  • SELECT...INTO allows the query results to be written to a file.
  • Modifiers for the SELECT keyword:
    • Primarily used for removing duplicates.
    • The ALL and DISTINCT modifiers specify whether to remove duplicate rows (not a specific column) from the result set.
    • ALL is the default modifier, meaning all rows that meet the criteria will be retrieved.
    • DISTINCT removes duplicate rows.
  • Key advantages of subqueries:
    • Subqueries enable structured queries, allowing each part of a statement to be isolated.
    • Some operations require complex joins and associations. Subqueries provide alternative methods to perform these operations.
  • Accelerating queries:
    • Utilize Doris’s partitioning and bucketing as data filtering conditions to reduce the data scanning range as much as possible.
    • Make full use of Doris’s prefix index fields as data filtering conditions to speed up query performance.
  • UNION:Using only the union keyword has the same effect as using union distinct. Since deduplication can be memory-intensive, using union all for queries can result in faster performance and reduced memory consumption. If users want to perform order by and limit operations on the returned result set, they should place the union operation within a subquery, then select from that subquery, and finally, place the subquery along with order by outside.
  1. select * from (select age from student_01 union all select age from student_02) as t1 
  2. order by age limit 4;
  4. +-------------+
  5. |     age     |
  6. +-------------+
  7. |      18     |
  8. |      19     |
  9. |      20     |
  10. |      21     |
  11. +-------------+
  12. 4 rows in set (0.01 sec)
  • JOIN
    • In addition to supporting equi-join in inner join conditions, non-equi-join is also supported. However, for performance considerations, it is recommended to use equi-join.
    • Other types of joins only support equi-join.