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 object CLAUSE

Arguments

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

  • 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


-- Example selecting from a materialized view

SELECT * FROM ORACLE1.oracle.INVENTORY.custview22;

WHERE CLAUSE


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

TOP or LIMIT


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

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

GROUP BY and SUM


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

HAVING


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

ORDER BY


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