SELECT retrieves data from the database.
SELECT select_list FROM table_list [select_condition [select_condition…]]
select_list ::=
[DISTINCT] {term|function} [AS name] [, {term|function} [AS name]...]
table_list ::=
[account.]table_name [AS name][, [account.]table_name [AS name]...]
select_condition ::=
WHERE search_condition [{AND|OR} search_condition...]
| GROUP BY column_name [, column_name ...]
| HAVING search_condition
| UNION [ALL] select_statement
| ORDER BY order_item [ASC | DESC] [, order_item [ASC | DESC]...]
| INTO TABLE table_name
term ::=
[[account.]table_name.]column_name | constant
function ::=
aggregate_function | scalar_function
aggregate_function ::=
AVG ([DISTINCT] expression)
| COUNT (expression)
| COUNT (DISTINCT expression)
| COUNT (*)
| MIN (expression)
| MAX (expression)
| SUM ([DISTINCT] expression})
expression ::= term [operator {term|expression}]
operator ::=
+ | - | * | / | string concatenation(||)
search_condition ::=
[NOT] conditional_expression [{AND|OR} [NOT] conditional_expression...]
conditional_expression ::=
expression operator {term|USER}
| expression operator {ALL|ANY|SOME}
(select_statement)
| expression [NOT] BETWEEN expression AND expression
| expression [NOT] IN ({select_statement | value [, value...]})
| expression [NOT] LIKE value [ESCAPE value]
| column_name IS [NOT] NULL
| EXISTS (select_statement)
By default, all rows in query results are displayed. To exclude duplicate rows, specify DISTINCT, which can be used only once per SELECT clause.
There are two types of functions that can operate on columns in a SELECT statement – aggregate and scalar. Aggregate functions are used to get summary values for the rows selected by the search_condition in a SELECT statement. Since aggregate functions return a single value, they cannot appear in a SELECT statement with typical columns or expressions.
Each of the aggregate functions is defined below:
| AVG() | Averages a column of numeric data. |
| AVG(DISTINCT) | After eliminating duplicates, averages a column of numeric data |
| COUNT() | Counts the number of select_items in a column. |
| COUNT(DISTINCT) | After eliminating duplicates, counts the number of select_items in a column. |
| COUNT(*) | Counts the number of rows in the query output. |
| MIN() | Determines the smallest value of select_item in a column. |
| MAX() | Determines the largest value of select_item in a column. |
| SUM() | Totals a column of numeric data. |
| SUM(DISTINCT) | After eliminating duplicates, totals a column of numeric data. |
There are three types of scalar functions:
string-manipulation functions
numeric functions
time/date functions
See Scalar functions for a complete list of supported scalar functions, by type.
All three types of scalar functions operate on columns from either a table, a constant value, or another scalar function.
{fn scalar_function(parameters)}
The CONCAT scalar function concatenates two string expressions. In this example assume that the CLIENTS table has separate columns for first and last name. The statement below creates a query containing the full name of each client:
select {fn CONCAT(first_name, last_name)} from clients
Unfortunately, the query above does not place a space between the names. To place the necessary space between the first and last names, write the statement like this:
select {fn CONCAT({fn CONCAT(first_name, ' ')}, last_name)} from clients
The optional AS name clause is used to specify an alias name for a table or a column within a table. The alias name will appear in the query output.
Use AS name to alias a column name when there is a select_item that is an expression or that contains a field function, and the column is to be given a name.
select customerid as ID, organizationname as Company from customers
If AS name is used to alias a table, the alias name must be used in place of the table name throughout the SELECT command.
select contactname from customers as C where C.customerid=13
USER is the keyword for the current user account.
search_condition specifies the criteria records must meet to be included in the query results. Multiple search conditions can be connected using the AND/OR operators. The NOT operator can be used to reverse the outcome of a logical expression.
search_condition can take one of the forms below:
| Form 1 | column1 operator field2 |
| Example | amount = 100.00 |
| Form 2 | column1 operator column2 |
| Example | salesreps.repoffice = offices.office |
| Form 3 | column1 operator expression |
| Example | city = "Denver" |
| Form 4 | expression operator ALL (subquery) |
| Example | (0.5 * target)< ALL(SELECT sales FROM salesreps
WHERE repoffice = office) With ALL, the column must meet the comparison condition for all values generated by the subquery before its record is included in the query results. |
| Form 5 | expression operator ANY (subquery) |
| Example | 0.1*quota)<ANY(SELECT amount FROM orders WHERE
rep=EMPL_NUM) With ANY, the expression must meet the comparison condition for at least one of the values generated by the subquery. |
| Form 6 | expression [NOT] BETWEEN startrange AND endrange |
| Example | orderdate BETWEEN "1990-01-01" AND "1990-06-30" Checks to see if expression is within a range of values. |
| Form 7 | [NOT] EXISTS (subquery) |
| Example | EXISTS ;(SELECT*FROM orders WHERE product= product_id
AND mfr=mfr_id AND amount > 25000.0) Checks to see if at least one row meets the criteria in the subquery. With EXISTS, the search condition evaluates to true unless the subquery evaluates to the empty set. |
| Form 8 | expression [NOT] IN valueset |
| Example | repoffice NOT IN ("11", "13", "22") With IN, expression must be part of a given set of values before its item is included in the query results. |
| Form 9 | expression [NOT] IN (subquery) |
| Example | repoffice IN (SELECT office FROM offices WHERE sales
> target) With this form, expression must be part of the set of values returned by the subquery before its item is included in the query results. |
| Form 10 | column1 [NOT] LIKE expC |
| Example | company LIKE "Smith% Corp." This search condition searches for each column1 that matches the specified expC. The wild card characters can be used <%> (percent) and <_> (underscore) as part of expC. The <_> character represents a single unknown character in the string and the <%> represents a sequence of zero or more unknown characters in the string. |
| Form 11 | column1 IS [NOT] NULL |
| Example | repoffice IS NULL This search condition searches for each column1 that is NULL or empty string. |
If two tables are included in a query, and a search condition is not specified, every item in the first table will be joined with every item in the second table as long as the search conditions are met. This can produce sizable query results.
Combines final results of one SELECT statement with final results of another SELECT statement. By default, UNION checks the combined results and eliminates duplicate rows. Use ALL to prevent UNION from eliminating duplicate rows from the combined results.
The rules for using UNION are:
Do not use UNION to combine subqueries.
Both SELECT statements must output the same number of columns.
Each column in the query results of one SELECT statement must have the same data type as the corresponding column in the other SELECT statement.
Only the final SELECT statement can have an ORDER BY clause, and ORDER BY must refer to output columns by number. If ORDER BY is included, it affects the entire result.
Use the ORDER BY option to sort query results based on values in the column(s). Each order_item must correspond to a column in the query results and can be:
A column in a FROM table that is also a select_item in the main SELECT clause (not in a subquery).
A numeric expression indicating the location of the column in the result table. The left column is number one.
DESC orders query results in descending order according to the order_item(s).
ASC specifies ascending order for query results and is the default.
If the select_item is a column from the SQL table, column headers in query results are the column_name.
If the select_item includes the AS name option, name displays as the column header.
If the select_item is an expression, the column header corresponds to that expression.
If the select_item includes a function, the column header is the function.
Examples
Display companies in the CUSTOMERS table (one column from one table) on the screen.
SELECT company FROM customers
Display three columns from two tables, and join the two tables based on the MFR column equal to EMPL_NUM column. Use local aliases for both tables.
SELECT a.city, b.name, b.title FROM offices a, salesreps b WHERE a.mgr = b.empl_num
Display rows with unique data in the specified fields.
SELECT DISTINCT a.city, b.name, b.title FROM offices a, salesreps b WHERE a.mgr = b.empl_num
Display rows in ascending order by CITY, NAMEE and TITLE.
SELECT city, name, title FROM offices, salesreps WHERE mgr = empl_num ORDER BY CITY, NAME, TITLE
The INTO TABLE clause specifies that the results of the SELECT statement be inserted into the specified table.
SELECT city, name, title FROM offices, salesreps WHERE mgr = empl_num INTO TABLE tempfile
Display all descriptions from the PRODUCTS table with a PRODUCT_ID and MFR_ID those match a PRODUCT and MFR in the orders table with amount of an order more than 25000.00.
SELECT description FROM products a WHERE EXISTS (SELECT * FROM orders b WHERE a.product_id = b.product AND a.mfr_id = b.mfr and amount > 25000.00)
Display all records from the CUSTOMERS table having a company name that begins with a substring 'Smith' and finishes with a substring 'Corp.'
SELECT company FROM customers a WHERE a.company LIKE 'Smith% Corp.'
There are two types of joins commonly used to relate data from more that one table, the inner join and the outer join. The inner join returns only rows that meet the specified criteria, while the outer join maintains some or all of the unmatched data.
Using D3 SQL, an inner join can be accomplished using the SELECT statement involving two or more tables and a WHERE clause that specifies the join criteria. For instance, to display customers and their orders, a SELECT statement would include both the CUSTOMERS and ORDERS tables, using a column that is common to both tables, CUSTOMERID, to join the tables. For example,
SELECT customername, orderid, orderdate FROM customers, orders WHERE customers.customerid = orders.customerid
In this query, only the customers with orders are returned.
If a list of all customers and their orders (if any) is desired, an outer join is needed. An outer join is simulated in D3 SQL using the UNION operator. The queries that will be "unioned" involve all the customers with orders and all the customers without orders. For example,
SELECT customername, orderid, orderdate FROM customers, orders WHERE customers.customerid = orders.customerid UNION SELECT customername, NULL, NULL FROM customers WHERE customerid NOT IN (select customerid FROM orders)
The first part of the UNION statement returns only customers with orders, while the second part of the UNION statement returns only customers not found in the orders table. Therefore, the UNION of the two queries includes all the customers. Since UNION statements must have the same number and type of columns, NULL is used as a placeholder.