Retrieves data from a structured data source. The SELECT statement can be used individually, or with one or more of clauses or joins.

Syntax

SELECT selectlist FROM [col1,...] [AS 'alias'] CLAUSE

Arguments

  • selectlist: A list of columns to retrieve from the data source.

  • col1,…: A list of source columns to retrieve from the data source.

  • alias - an alias or new name for the source columns.

  • object: The object identifier for the data source. The data source can be a table, view, or materialized view.

  • CLAUSE: SQL-92 Clauses which include WHERE, TOP, LIMIT, GROUP BY, HAVING, ORDER BY.

    • WHERE: One or more expressions which evaluate to true or false for each row of data. When true, the row is returned. When false, the row is not returned. The WHERE clause can include the AND and OR keywords to combine logical arguments.

    • TOP or LIMIT: Use integer values with TOP or LIMIT clauses to display a subset of results. TOP gives you the top X results in a dataset, while LIMIT returns X results. If X exceeds the number of rows in the result set, the complete result set is returned.

    • GROUP BY: When used with a SELECT statement, the selectList portion of the SELECT statement is restricted to aggregate functions or grouping columns.

    • SUM: Adds together results from specified column rows related to GROUP BY clause. If GROUP BY selects rows 1, 3 and 5, SUM adds rows 1, 3 and 5 together and outputs the result.

    • HAVING: can be used with GROUP BY to restrict returned values and evaluate to true or false for each group of data. When true, the group is returned. When false, the group is not returned. The HAVING clause can include the AND and OR keywords to combine logical arguments.

    • ORDER BY: The ORDER BY clauses forces a specific ordering of results. Expression can be a column name or other expression. ASC specifies ascending (default) and DESC specifies descending.

Examples

SELECT FROM materialized view.

SELECT * FROM ORACLE1.oracle.INVENTORY.custview22;

Statement with WHERE clause

SELECT firstName, lastName
FROM MYSQL.inventory.customer
WHERE firstName = 'Ann' AND lastName = 'Miller'

Select TOP

SELECT TOP 100 firstName, lastName, zipCode FROM MYSQL.inventory.customer;

Select with LIMIT

SELECT firstName, lastName, zipCode FROM MYSQL.inventory.customer LIMIT 100;

Select with SUM and GROUP BY

  • SUM adds all results in orderAmt column which belong to the orderDate column in GROUP BY clause.
SELECT SUM(orderAmt), orderDate
FROM MYSQL.inventory.orders
GROUP BY orderDate;

Select with COUNT, column alias and GROUP BY column ID

SELECT COUNT(*) AS chk, '999' AS id2 FROM FROM MYSQL.inventory.orders GROUP BY 2;

SELECT statement with SUM, GROUP BY and HAVING

SELECT SUM(orderAmt) AS totalOrderAmt, orderDate
FROM MYSQL.inventory.orders
GROUP BY orderDate
HAVING totalOrderAmt > 1000;

SELECT statement with ORDER BY

SELECT firstName, lastName, zipCode DESC
FROM MYSQL.inventory.customer
ORDER BY lastName DESC;