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'.

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

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

• Source tables and views are defined in the FROM clause. Through the values_table, you can easily define static tables. For example, by (VALUES (1, TRUE), (2, FALSE), (3, NULL)) AS t(i, b) a table with two columns and three rows is specified.

• The SELECT list defines the columns of the result table. If * is used, then all columns will be listed. t.* can be used to list all columns of the table, the view t, or the object with the table alias t.

• In complex expressions within the SELECT list, the usage of column aliases can be very useful. 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.
  An example of indirect referencing: SELECT ABS(x) AS x FROM t WHERE local.x>10.
  Moreover, column aliases define the column names of the result table.

• The WHERE clause can be used to restrict the result by certain filter conditions.

• Equality join conditions among tables can be specified 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. It is evaluated before the WHERE conditions - except the join conditions on tables which are referenced in the CONNECT BY clause.
  The following elements are relevant for the CONNECT BY clause:

Elements	Meaning
START WITH	By this condition you can specify the set of root nodes in the graph.
condition(s)	You can define several conditions. A hierarchical connection between father and son rows can be defined by the keyword PRIOR (for example, PRIOR employee_id = manager_id). If you don't 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 there exists a cycle. 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 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, that is, 1 for the root node, 2 for its direct sons, 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. Inside the SELECT list, you can use aggregate functions. Using a numerical value x (position) results in aggregating the result table by the x-th column. For example, GROUP BY 1 is equivalent to GROUP BY the first column in the SELECT list. If GROUP BY is used, all SELECT list elements have to be aggregated except those which define the grouping keys.

• CUBE, ROLLUP, and GROUPING SETS are extensions of the GROUP BY clause for calculating superaggregates. Those are hierarchical aggregation levels like, for example, 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, 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, separated by a comma, then 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),().

• By using the HAVING clause you can restrict the number of groups.

• QUALIFY can be used to restrict the result by certain filter conditions in a way similar to WHERE or HAVING clauses, but its filter condition may contain or reference Analytic Functions.

• If the condition does not contain an analytic function, it is preferable to use WHERE or HAVING clauses instead of QUALIFY, because the filter for QUALIFY is evaluated later.

• The result table can be sorted by specifying the ORDER BY clause. Using a numerical value x (position) results in sorting the result table by the x-th column, for example, ORDER BY 1.

  Options of the ORDER BY clause: 

  • ASC (Default) means ascending, DESC means descending sorting.
  • The NULLS LAST (Default) and NULLS FIRST option can be used to determine whether NULL values are sorted at the end or the beginning.

  String data is sorted by its binary representation.

• The number of result rows can be restricted by defining 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.

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

  • For information about the usage of external data sources and their options, refer to the description of the IMPORT statement in Section Manipulation of database (DML) section. It is highly recommended to explicitly specify the target column types (see example below). 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], INTERSECT, MINUS.

• The SELECT queries can be directly returned from the query cache in case the syntactically equivalent query was already executed before. For more information, refer to the command ALTER SYSTEM.

• 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, refer to Analytic Functions.

Examples

The examples relate to the following two tables (customers and sales):

Customers table

SELECT * FROM customers;

Sales table

SELECT * FROM sales;

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);