|
Specify a cursor that retrieves a selection of rows from one or more tables.
While the full syntax is rather complex, the main syntactical elements can be simplified as:
SELECT select-list
[ INTO table-name ]
FROM table-reference
[ WHERE search-condition ]
[ GROUP BY grouping-element ]
[ HAVING search-condition ]
[ ORDER BY sort-specification
Syntax
<select statement> ::= <cursor specification>
<cursor specification> :=
<query expression> ::=
<query term> ::=
<query specification> ::=
<select list> ::=
<select sublist> ::=
|
|
|
|
derived-column [ [ AS ] column-alias ]
qualified-asterisk
|
<table expression> ::=
<from clause> := FROM <table reference> [ { , <table reference> }... ]
<table reference> ::=
<table factor> ::=
<derived column list> ::= column-alias [ { , column-alias }... ]
<table function derived table> ::= TABLE ( <routine invocation> )
<joined table> ::=
<cross join> ::= <table reference> CROSS JOIN <table factor>
<qualified join> ::= <table reference> [ <join type> ] JOIN <table reference> <join specification>
<natural join> ::= <table reference> NATURAL [ <join type> ] JOIN <table factor>
<join type> ::=
|
|
|
|
|
|
INNER
LEFT [ OUTER ]
RIGHT [ OUTER ]
FULL [ OUTER ]
|
<join specification> ::=
<where clause> ::= WHERE <search condition>
<group by clause> ::= GROUP BY <grouping element> [ { , <grouping element> }... ]
<grouping element> ::= column-reference <collate clause>
<having clause> ::= HAVING <search condition>
<order by clause> ::= ORDER BY <sort specification> [ { , <sort specification> }... ]
<sort specification> ::= column-reference <sort options>
Live and static cursors
NexusDB supports both live (updateable) and static (read-only) cursors as the result of executing a SELECT statement. A live cursor is effectively a direct cursor on the underlying base table referenced in the table expression, with a filter set to the source columns specified in the select list and the rows resulting from evaluating the query specification. A SELECT statement will return a static result set (a special in-memory table) if the query expression does not satisfy the requirements of a live cursor, or the client access software executing the query does not support live cursors.
The requirements for a live cursor are as follows:
§
|
The FROM clause shall reference a single source table.
|
§
|
A DISTINCT quantifier shall not be specified.
|
§
|
A TOP quantifier shall not be specified.
|
§
|
A GROUP BY clause shall not be specified.
|
§
|
If an ORDER BY clause is specified, then a supporting index that includes the same columns and sort options must exist.
|
Table operators
§
|
The UNION, EXCEPT and INTERSECT set operators are implicitly DISTINCT if no set quantifier is specified. The result of a table operation with DISTINCT implied or specified contains no duplicate rows.
|
§
|
The UNION operator is used to combine the tables derived from two query expressions. The two tables shall have the same number of columns (degree), and each corresponding column shall have the same ordinal position and a compatible data type. The result of an UNION operation is a combination of the two tables without duplicate rows. If ALL is specified, then the duplicate rows are preserved.
|
§
|
The EXCEPT operator is used to return all rows in the first table except those that also appear in the second table. If ALL is specified, then the number of duplicates of any specific row is equal to the number of such duplicates in the first table less the number of duplicates in the second table.
|
§
|
The INTERSECT operator is used to return rows that appear in both tables. If ALL is specified, then the number of duplicates of any specific row is equal to the lesser number of such duplicates in the two tables.
|
§
|
INTERSECT has a higher precedence than UNION and EXCEPT which are executed left to right.
|
§
|
The data type of columns in the result of a query expression with UNION, EXCEPT and INTERSECT is determined according to the rules for Result data types of aggregations.
|
Joined tables
NexusDB supports all common types of joins between tables. While the old-style join syntax with a comma-separated list of table names in the FROM clause and the join criteria contained in the WHERE clause, is still supported in SQL, we recommend the use of the new syntax introduced in SQL-92 and carried on by the latest revisions of the standard.
§
|
The CROSS join is effectively a cross-product of the two tables, commonly referred to as the Cartesian product, containing all possible combinations of rows from the first and second tables. Like its equivalent, the classic comma-separated join, a more meaningful result can be obtained by specifying a join criteria in the WHERE clause.
|
§
|
A NATURAL join is based on all columns in the referenced tables with the same name, and retrieves the rows that have equal values in the corresponding columns. If there are no corresponding columns, the result is a cross-join between the two tables.
|
§
|
A qualified join connects two tables based on an explicit join specification. The join specification can be expressed as a condition join with the ON keyword followed by a search condition, or as a column name join with the USING keyword followed by a list of corresponding columns.
|
§
|
The INNER keyword is implicit if no join type is specified. An INNER JOIN retrieves only the rows that match the join criteria. The OUTER keyword is implicit for outer joins, but can optionally be included for clarity. A LEFT OUTER JOIN will preserve all rows from the left table with null values in the columns of non-matching rows that correspond to the right table. A RIGHT OUTER JOIN is similar to a LEFT JOIN, except that the right table has its rows preserved. Finally, a FULL OUTER JOIN will preserve the rows from both tables.
|
SELECT clause
§
|
The select list specifies the source columns to be retrieved from the table expression and additional columns computed as the result of a value expression.
|
§
|
An asterisk (*) is a short-hand syntax for specifying all columns that are part of the table expression. An asterisk qualified by a table name specifies all the fields from that particular table.
|
§
|
The optional AS column-alias clause is used to give computed columns meaningful names or to rename source columns.
|
§
|
The ALL keyword is implicit if no set quantifier is specified. If DISTINCT is specified, then all duplicate rows are removed from the result set.
|
§
|
The TOP quantifier is used to restrict the result set to the number of rows specified by the n argument, or a percentage of all rows in the result set if the n argument is followed by the PERCENT keyword. The optional start-position argument indicates the position of the first row to be included in the result set.
|
Tip: A syntactical way of retrieving the last n rows, is to ORDER BY a descending sort specification using the TOP quantifier.
INTO clause
§
|
The INTO clause is used to create a new table with columns according to the select list and data resulting from executing the query.
|
§
|
Each column in the new table has the same name, data type, ordinal position and row values as the corresponding columns in the select list. Computed columns in the select list will be ordinary columns in the new table, with the column values computed by the query execution.
|
§
|
If a table with the same name already exists in the database, then the old table will be implicitly dropped before the new table is created.
|
§
|
A new table without data can be created by having a FALSE condition in the WHERE clause.
|
§
|
Using the INTO clause to create a new table is an alternate syntax for the AS subquery clause in the CREATE TABLE statement.
|
FROM clause
§
|
The FROM clause specifies one or more source tables from which to retrieve rows. The name of base tables, views and user-defined functions may optionally be qualified by the schema name to reference such objects in databases other than the current, or just for syntax clarity.
|
§
|
A correlation name is required for source tables derived from a subquery or a user-defined function, but is optional for base tables and views.
|
§
|
A derived column list may be specified to rename the columns of the table reference. The number of columns in the list shall match the number of source columns contained in the table reference.
|
§
|
If two or more tables are referenced in the FROM clause without an explicit join or a WHERE clause joining the tables, the result will be a cross-join between the tables.
|
WHERE clause
§
|
The WHERE clause is a filter that is applied to the result of evaluating the FROM clause.
|
§
|
Only the rows that satisfy the criteria specified in the search condition are included in the result set.
|
§
|
The scope of the WHERE clause is the tables referenced in the FROM clause. Expression columns or renamed columns in the select list cannot be referenced in the WHERE clause by their column aliases.
|
GROUP BY clause
§
|
The effect of the GROUP BY clause is to partition the result of evaluating the table expression so far into one or more groups, such that for each grouping column, no two rows of any group have different values for the grouping column. Furthermore, no other group has the same value for the complete set of grouping columns.
|
§
|
Each group is represented in the result set by a single row with aggregate functions, if specified, applied to the group.
|
§
|
All non-aggregate source columns that are referenced in the select list shall be specified as grouping columns.
|
§
|
The GROUP BY clause may also specify source columns that are not referenced in the select list.
|
§
|
Each grouping column shall be a reference to a source column in the table expression.
|
HAVING clause
§
|
The HAVING clause works like the WHERE clause, except that the filter criteria is applied to the groups of rows in the grouped table resulting from evaluating the GROUP BY clause.
|
§
|
If no GROUP BY clause is specified, then the result of evaluating the preceding clause is considered a single group.
|
§
|
Columns referenced in the search condition shall be a grouping column, a source column contained in an aggregate function or an outer reference.
|
ORDER BY clause
§
|
The ORDER BY clause specifies the order of rows in the result set.
|
§
|
The optional sort options can be specified to override the default sort behavior of the individual columns.
|
§
|
The sort keys shall be a reference to columns in the select list.
|
§
|
If DISTINCT, GROUP BY, UNION, EXCEPT or INTERSECT is not specified in the main query expression, then the ORDER BY clause may also specify source columns that are not included in the select list.
|
§
|
Columns that are present in the select list can be referenced by the column name or the ordinal position.
|
Note: The option of referencing a column by its ordinal position has been deprecated, and may be removed in a future version of NexusDB SQL.
Examples
1)
|
The following example selects all students ordered by names:
|
SELECT studentID, studentName, gender
FROM students
ORDER BY lastName, firstName
2)
|
The following example selects either female or male students depending on the value of the gender parameter:
|
SELECT studentID, studentName, gender
FROM students
WHERE gender = :gender
ORDER BY lastName, firstName
3)
|
The following example selects a list of students with a computed column called nickName:
|
SELECT studentID, city || '-' || firstName AS nickName, lastName, gender
FROM students
ORDER BY lastName, firstName
4)
|
The following example computes how many students are enrolled in the available courses:
|
SELECT courseID, count( * ) AS total_enrolled
FROM enrolls
GROUP BY courseID
ORDER BY total_enrolled DESC
5)
|
The following example select courses with more than 5 students enrolled:
|
SELECT courseID, count( * ) AS total_enrolled
FROM enrolls
GROUP BY courseID
HAVING COUNT( * ) > 5
ORDER BY total_enrolled DESC
6)
|
The following example uses joins to select columns from three tables:
|
SELECT
e.courseID,
c.courseName,
s.studentName,
e.grade
FROM enrolls e
JOIN courses c ON c.courseID = e.courseID
JOIN students s USING ( studentID )
ORDER BY courseID
7)
|
The following example combines rows from two tables using the UNION ALL table operator:
|
SELECT orderID, orderDate, orderTotal, amountPaid
FROM orders_2002
UNION ALL
SELECT orderID, orderDate, orderTotal, amountPaid
FROM orders_2003
ORDER BY orderDate, orderID
8)
|
The following example uses a subquery as table reference:
|
SELECT studentID, studentName, city, gender
FROM ( SELECT * FROM students ) AS s
ORDER BY lastName, firstName
9)
|
The following example uses a table function as table reference:
|
SELECT studentID, studentName, city, gender
FROM TABLE( getStudents() ) AS s
ORDER BY lastName, firstName
Conformance
SQL:2003 standard
NexusDB extensions
|
-
-
-
-
-
-
-
-
-
-
|
Core SQL
Feature F302 "INTERSECT table operator"
Feature F304 "EXCEPT ALL table operator"
Feature F401 "Extended joined table"
Feature F591 "Derived tables"
Feature T326 "Table functions"
Feature T551 "Optional key words for default syntax"
TOP quantifier in SELECT clause
INTO clause in query specification
Sort options
|
|
