SELECT Expression

A SelectExpression is the basic SELECT-FROM-WHERE construct used to build a table value based on filtering and projecting values from other tables.

Syntax

SELECT [ DISTINCT | ALL ] SelectItem [ , SelectItem ]*
   FROM clause
   [ WHERE clause ]
   [ GROUP BY clause ]
   [ HAVING clause ]
   [ ORDER BY clause ]
   [ result offset clause ]
   [ fetch first clause ]

SELECT clause

The SELECT clause contains a list of expressions and an optional quantifier that is applied to the results of the  WHERE clause.

If DISTINCT is specified, only one copy of any row value is included in the result. Nulls are considered duplicates of one another for the purposes of DISTINCT.

If no quantifier, or ALL, is specified, no rows are removed from the result in applying the SELECT clause. This is the default behavior.

SelectItem:

{
    * |
    { <a href="correlation-Name } .* |
      Expression [AS Simple-column-Name] }
}

A SelectItem projects one or more result column values for a table result being constructed in a SelectExpression.

For queries that do not select a specific column from the tables involved in the SelectExpression (for example, queries that use COUNT(*)), the user must have at least one column-level SELECT privilege or table-level SELECT privilege. See GRANT statement for more information.

FROM clause

The result of the  FROM clause is the cross product of the FROM items.

WHERE clause

The  WHERE clause can further qualify the result of the FROM clause.

GROUP BY clause

The  GROUP BY clause groups rows in the result into subsets that have matching values for one or more columns.

GROUP BY clauses are typically used with aggregates. If there is a GROUP BY clause, the SELECT clause must contain only aggregates or grouping columns. If you want to include a non-grouped column in the SELECT clause, include the column in an aggregate expression. For example, this query computes the average salary of each team in a baseball league:

splice> SELECT COUNT(*) AS PlayerCount, Team, AVG(Salary) AS AverageSalary
   FROM Players JOIN Salaries ON Players.ID=Salaries.ID
   GROUP BY Team
   ORDER BY AverageSalary;

If there is no GROUP BY clause, but a SelectItem contains an aggregate not in a subquery, the query is implicitly grouped. The entire table is the single group.

HAVING clause

The  HAVING clause can further qualify the result of the FROM clause. This clause restricts a grouped table, specifying a search condition (much like a WHERE clause) that can refer only to grouping columns or aggregates from the current scope.

The HAVING clause is applied to each group of the grouped table. If the HAVING clause evaluates to TRUE, the row is retained for further processing; if it evaluates to FALSE or NULL, the row is discarded. If there is a HAVING clause but no GROUP BY, the table is implicitly grouped into one group for the entire table.

ORDER BY clause

The  ORDER BY clause allows you to specify the order in which rows appear in the result set. In subqueries, the ORDER BY clause is meaningless unless it is accompanied by one or both of the result offset and fetch first clauses.

result offset and fetch first clauses

The  fetch first clause, which can be combined with the result offset clause, limits the number of rows returned in the result set.

Usage

The result of a SelectExpression is always a table.

Splice Machine processes the clauses in a Select expression in the following order:

  • FROM clause
  • WHERE clause
  • GROUP BY (or implicit GROUP BY)
  • HAVING clause
  • ORDER BY clause
  • Result offset clause
  • Fetch first clause
  • SELECT clause

When a query does not have a FROM clause (when you are constructing a value, not getting data out of a table), use a   VALUES expression, not a SelectExpression. For example:

VALUES CURRENT_TIMESTAMP;

The * wildcard

The wildcard character (***) expands to all columns in the tables in the associated FROM clause.

  • correlation-Name identifiers expand to all columns in the identified table. That table must be listed in the associated FROM clause.

Naming columns

You can name a SelectItem column using the AS clause.

If a column of a SelectItem is not a simple ColumnReference expression or named with an AS clause, it is given a generated unique name.

These column names are useful in several cases:

  • They are made available on the JDBC ResultSetMetaData.
  • They are used as the names of the columns in the resulting table when the SelectExpression is used as a table subquery in a FROM clause.
  • They are used in the ORDER BY clause as the column names available for sorting.

Examples

This example shows using a SELECT with WHERE and ORDER BY clauses; it selects the name, team, and birth date of all players born in 1985 and 1989:

splice> SELECT DisplayName, Team, BirthDate
   FROM Players
   WHERE YEAR(BirthDate) IN (1985, 1989)
   ORDER BY BirthDate;
DISPLAYNAME             |TEAM      |BIRTHDATE
-----------------------------------------------
Jeremy Johnson          |Cards     |1985-03-15
Gary Kosovo             |Giants    |1985-06-12
Michael Hillson         |Cards     |1985-11-07
Mitch Canepa            |Cards     |1985-11-26
Edward Erdman           |Cards     |1985-12-21
Jeremy Packman          |Giants    |1989-01-01
Nathan Nickels          |Giants    |1989-05-04
Ken Straiter            |Cards     |1989-07-20
Marcus Bamburger        |Giants    |1989-08-01
George Goomba           |Cards     |1989-08-08
Jack Hellman            |Cards     |1989-08-09
Elliot Andrews          |Giants    |1989-08-21
Henry Socomy            |Giants    |1989-11-17

13 rows selected

This example shows using correlation names for the tables:

splice> SELECT CONSTRAINTNAME, COLUMNNAME
  FROM SYS.SYSTABLES t, SYS.SYSCOLUMNS col,
  SYS.SYSCONSTRAINTS cons, SYS.SYSCHECKS checks
  WHERE t.TABLENAME = 'FLIGHTS'
    AND t.TABLEID = col.REFERENCEID
    AND t.TABLEID = cons.TABLEID
    AND cons.CONSTRAINTID = checks.CONSTRAINTID
  ORDER BY CONSTRAINTNAME;

This example shows using the DISTINCT clause:

 SELECT DISTINCT SALARY   FROM Salaries;

This example shows how to rename an expression. We use the name BOSS as the maximum department salary for all departments whose maximum salary is less than the average salary i all other departments:

 SELECT WORKDEPT AS DPT, MAX(SALARY) AS BOSS
   FROM EMPLOYEE EMP_COR
   GROUP BY WORKDEPT
   HAVING MAX(SALARY) <     (SELECT AVG(SALARY)
      FROM EMPLOYEE
      WHERE NOT WORKDEPT = EMP_COR.WORKDEPT)
   ORDER BY BOSS;

See Also