SELECT

Purpose

Use this statement to retrieve data from tables or views.

Prerequisites

• You need either the system privilege USE ANY SCHEMA or the object privilege USAGE on the target schema, or the schema must be owned by you or one of your assigned roles.

• You need either the system privilege SELECT ANY TABLE or the SELECT object privilege on tables or views which are referenced in the SELECT list, or the tables or views must be owned by you or one of your assigned roles.
• When accessing views, the owner of the view must have SELECT privileges on the objects that are referenced in the view.
• If you use a subimport, you need the appropriate rights similar to the IMPORT statement.

Usage notes

• You can calculate scalar expressions by omitting the FROM clause. For example: SELECT 'abc'.

• You can use the WITH clause to define common table expressions (CTEs). These are temporary views that are valid only during the execution of a subquery.

• To eliminate identical rows, use DISTINCT. If you use the keyword ALL (default), all rows will be present in the result table.

• To define the source tables or views, use the FROM clause. You can define static tables in a values_table. For example, (VALUES (1, TRUE), (2, FALSE), (3, NULL)) AS t(i, b) defines a table with two columns and three rows.

• The select_list defines the columns of the result table. If * is used, all columns are listed. You can use an expression like t.* to list all columns of the table t, the view t, or the object with the table alias t.

• If you have complex expressions within a select_list, consider using column aliases. You can only reference such aliases directly inside the ORDER BY clause and the QUALIFY filter condition. However, you can also reference those aliases indirectly through the keyword LOCAL within the other clauses (WHERE, GROUP BY, HAVING), and even in the SELECT list. Column aliases define the column names of the result table.

  Example of indirect referencing: SELECT ABS(x) AS x FROM t WHERE local.x>10

• Unqualified column names (without a table prefix) are resolved to table columns from the FROM clause, except in ORDER BY and QUALIFY, where select_list names are preferred over table columns. To resolve ambiguity and improve readability, use table name or alias when referring to table columns, and LOCAL when referring to select_list names.

• To restrict the result by certain filter conditions, you can use the WHERE clause.

  You can specify equality join conditions among tables within the WHERE clause by using the = operator. If you want to define an outer condition, add (+) after the outer expression.

• The CONNECT BY clause can be used to define hierarchical conditions. This clause is evaluated before the WHERE conditions, excepting the join conditions on tables that are referenced in the CONNECT BY clause itself.
  The following elements are relevant for the CONNECT BY clause:

Elements	Meaning
START WITH	Specifies the set of root nodes in the graph.
condition(s)	You can define multiple conditions. A hierarchical connection between parent and child rows can be defined by the keyword PRIOR (for example, PRIOR employee_id = manager_id). If you do not specify such a PRIOR condition, the cross product will be computed. Thus, the statement SELECT LEVEL FROM dual CONNECT BY LEVEL<=100 results in a table with 100 rows, because 100 cross products were calculated for the table dual.
NOCYCLE	If you specify this option, the query also returns results if a cycle exists. In this case the expansion will be terminated when a cycle is detected.

The following functions and operators can be used in the select_list and in the WHERE conditions to qualify the results of a hierarchical query.

Functions and operators	Meaning
SYS_CONNECT_BY_PATH (expr, char)	Returns a string containing the full path from the root node to the current node, containing the values for expr and separated by char.
LEVEL	Returns the hierarchy level of a row: 1 for the root node, 2 for its direct children, and so on. IMPORTANT: Cycle detection is disabled if LEVEL is used in CONNECT BY. This means that a condition using LEVEL that is always true, such as CONNECT BY LEVEL > 0, will cause the query to hang unless NOCYCLE is used.
PRIOR	This operator references the row’s parent row, which allows you to define a parent-child condition. You can also use this operator to access the values of the parent row. Hence, the following two CONNECT BY conditions are equivalent: PRIOR employee_id = manager_id AND PRIOR employee_ id=10. PRIOR employee_id = manager_id AND manager_ id=10.
CONNECT_BY_ROOT	Instead of a row’s value, the corresponding value of the root node is used. For example, CONNECT_BY_ROOT last_name would be evaluated by the name of the highest manager of an employee if the condition PRIOR employee_id = manager_id was defined in the CONNECT BY clause.
CONNECT_BY_ISLEAF	This expression returns 1 if a row is a leaf in the tree (the row has no children), otherwise 0.
CONNECT_BY_ISCYCLE	Returns whether the current row causes a cycle. In the path, such a row will occur exactly twice. This expression can only be used in combination with the NOCYCLE option. NOTE: This behavior is different to Oracle’s implementation, where the parent node is returned instead of the connection point of the cycle.

The function LEVEL and the operator PRIOR can also be used within the CONNECT BY clause.

• The PREFERRING clause defines a Skyline preference term.

• The GROUP BY clause defines groups of rows which will be aggregated in the result table. You can use aggregate functions in the select_list. Using a numerical value n (position) results in aggregating the result table by the n^th column. For example, GROUP BY 1 is equivalent to GROUP BY the first column in select_list.

  If GROUP BY is used, all SELECT select_list elements must be aggregated except those that define the grouping keys.

• CUBE, ROLLUP, and GROUPING SETS are extensions of the GROUP BY clause for calculating superaggregates. These are hierarchical aggregation levels, such as partial sums for days, months and years, and can be computed within one single GROUP BY statement instead of using a UNION of several temporary results.

  You can distinguish between regular result rows (normal aggregation on the deepest level) and superaggregate rows. The total of all arguments results in the normal aggregation, and the subsets result in the corresponding superaggregates. You can discern the result row types by using the function GROUPING[_ID].

Grouping	Meaning
CUBE	Calculates the aggregation levels for all possible combinations of the arguments (2n combinations). Example: By CUBE(countries, products) you can sum up all subtotal revenues of all country/product pairs (regular result rows), but additionally the subtotals of each country, the subtotals of each product and the total sum. This expression is equivalent to GROUPING SETS ((countries, products), (countries), (products), ()).
ROLLUP	Calculates the aggregation levels for the first n, n-1, n-2, ... 0 arguments (overall n+1 combinations). The last level corresponds to the grand total. Example: By ROLLUP(year,month,day) you can sum up all revenues of all single date (regular result rows), but additionally for each month of year, for each year and the grand total (superaggregate rows). This expression is equivalent to GROUPING SETS ((year,month,day), (year,month), (year), ()).
GROUPING SETS	Calculates the aggregation levels for the specified combinations. CUBE and ROLLUP are special forms of GROUPING SETS and simplify the notation.
()	Is similar to GROUPING SETS () and aggregates the whole table as one single group.

If multiple hierarchical groupings are specified as a comma-separated list, the result is the set of all combinations of partial groupings (cross product). For example, the expression ROLLUP(a,b),ROLLUP(x,y) results in 9 combinations. Starting with the subsets (a,b), (a), () and (x,y), (x), () you get the following combinations: (a,b,x,y), (a,b,x), (a,b), (a,x,y), (a,x), (a), (x,y), (x),().

To restrict the number of groups, you can use the HAVING clause.

• To restrict the result by filter conditions that contain or reference analytic functions, use the QUALIFY clause.

  If the condition does not contain an analytic function, use the WHERE or HAVING clauses, since the filter for QUALIFY is evaluated later.

• To sort the result table, use the ORDER BY clause.

  • Using a numerical value n (position) results in sorting the result table by the n^th column, for example, ORDER BY 1.

  • String data is sorted by its binary representation.

  • Options for the ORDER BY clause: 

    • ASC means ascending order, DESC means descending order. If not specified, results are sorted in ascending order.
    • NULLS LAST and NULLS FIRST determine if NULL values are sorted at the end or the beginning of the result list. If not specified, NULL results will be at the end of the result list.

• To restrict the number of result rows you can use the LIMIT clause.

  • The optional offset can only be used in combination with ORDER BY.

  • LIMIT is not allowed within correlated subqueries.

  • LIMIT with OFFSET is not allowed in aggregated SELECT statements without GROUP BY.

• To integrate the import of external data sources directly in your query, you can use the subimport clause.

  • For information about the usage of external data sources and their options, see IMPORT.

    We strongly recommend that you explicitly specify the target column types when importing data from external sources. Otherwise, the column names and data types are chosen in a generic way. For importing files, these types are mandatory.

  • Local files cannot be imported directly within queries.

  • By creating a view, external data sources can be integrated with Exasol as a sort of external tables.

  • Local filter conditions on such imports are not propagated to the source databases. However, you can achieve that by using the STATEMENT option.

• SELECT statements can be combined using the table operators UNION ALL, UNION, INTERSECT, and MINUS (=EXCEPT). For more information, see Table Operators - UNION [ALL], INTERSECT, MINUS.

• The arguments min_value and max_value in the VALUES BETWEEN clause must be constant expressions that evaluate to integers. The argument step_value must be a constant expression that evaluates to a positive integer.

  The value range table has a single column with the name RANGE_VALUE and an integer type (DECIMAL(9,0), DECIMAL(18,0), or DECIMAL(36,0) as appropriate). The column contains all the values between min_value and max_value combined if no step is given. If a step is given, the column skips values and produces the arithmetic series min_value + step_value * n with the last value less than or equal to max_value. See also Example 7.

  VALUES BETWEEN is significantly faster than using CONNECT BY to generate a range of values.

• The WINDOW clause allows the definition of named window specifications (i.e. the content of OVER clauses) for analytic functions. For more information, see Analytic Functions.

• The query cache stores a SELECT query together with its result if certain conditions are fulfilled. If the same query is sent again, the database can read the result directly out of the cache instead of executing the query. For more information, see ALTER SYSTEM.

Examples

Customers table

SELECT * FROM customers;

Sales table

SELECT * FROM sales;

The following examples relate to the customers and sales tables above:

Example 1

SELECT store, SUM(price) AS volume FROM sales
GROUP BY store ORDER BY store DESC;

Example 2

SELECT name, SUM(price) AS volume FROM
customers JOIN sales USING (c_id)
GROUP BY name ORDER BY name;

Example 3

WITH tmp_view AS (SELECT name, price, store FROM customers, sales
                  WHERE customers.c_id=sales.c_id)
SELECT sum(price) AS volume, name, store FROM tmp_view
GROUP BY GROUPING SETS (name,store,());

Example 4

SELECT * FROM (IMPORT INTO (v VARCHAR(1))
               FROM EXA AT my_connection
               TABLE sys.dual);

SELECT last_name, employee_id id, manager_id mgr_id,
       CONNECT_BY_ISLEAF leaf, LEVEL,
       LPAD(' ', 2*LEVEL-1)||SYS_CONNECT_BY_PATH(last_name, '/') "PATH"   
    FROM employees
    CONNECT BY PRIOR employee_id = manager_id
    START WITH last_name = 'Clark'
    ORDER BY employee_id;

Example 5

-- Get the largest sale per customer
SELECT S_ID, C_ID, PRICE, ROW_NUMBER() OVER (PARTITION BY C_ID ORDER BY PRICE DESC) NUM FROM SALES QUALIFY NUM = 1;

-- It is also possible to specify the analytic function call directly in the qualify expression

SELECT S_ID, C_ID, PRICE FROM SALES QUALIFY ROW_NUMBER() OVER (PARTITION BY C_ID ORDER BY PRICE DESC) = 1;

Example 7

SELECT * FROM VALUES BETWEEN 1 AND 15 WITH STEP 4;

Example 8

SELECT v,
       DATE'2020-10-26' + v * INTERVAL'7'DAY AS late_2020_mondays,
       5 * v AS five_times_table
FROM VALUES BETWEEN 1 AND 9 AS v(v);