SELECT
SELECT queries data from one or more tables, views, or materialized views. SELECT can work as an independent statement or a clause nested in other statements. The output of the SELECT clause can be used as the input of other statements.
CTE
You can use a common table expression (CTE) to define a temporary result set that you can reference possibly multiple times within the scope of a SQL statement.
DISTINCT
The DISTINCT keyword deduplicates the result set.
EXCEPT/MINUS
Returns distinct results of the left-hand query that do not exist in the right-hand query. EXCEPT is equivalent to MINUS.
EXCLUDE
This feature is supported starting from version 4.0.
GROUP BY
The GROUP BY clause is often used with aggregate functions. Columns specified in the GROUP BY clause will not participate in the aggregation operation.
HAVING
The HAVING clause does not filter row data in a table, but filters the results of aggregate functions.
INTERSECT
Calculates the intersection of the results of multiple queries, that is, the results that appear in all the result sets. This clause returns only unique rows among the result sets. The ALL keyword is not supported.
JOIN
Join operations combine data from two or more tables and then return a result set of some columns from some of them.
LIMIT
LIMIT clauses are used to limit the maximum number of rows returned. Setting the maximum number of rows returned can help StarRocks optimize memory usage.
OFFSET
The OFFSET clause causes the result set to skip the first few rows and return the following results directly.
ORDER BY
The ORDER BY clause of a SELECT statement sorts the result set by comparing the values from one or more columns.
PIVOT
This feature is supported from v3.3 onwards.
UNION
Combines the result of multiple queries.
WHERE and Operators
SQL operators are a series of functions used for comparison and are widely used in where clauses of select statements.
Alias
When you write the names of tables, columns, or expressions that contain columns in a query, you can assign them an alias. Aliases are usually shorter and better to remember than original names.
Subquery
Subqueries are categorized into two types in terms of relevance: