Query specification

Queries in SQL

Selection of Records from Query Result

Query Expression (SQL)

query_expression ::= { query_term | query_expression { UNION | EXCEPT } [ ALL ] 
    [ corresponding ] query_term } [ sorting ] [ selection ]
query_term ::= query_specification  | query_term INTERSECT [ ALL ] [ corresponding ] 
    query_specification
corresponding ::= CORRESPONDING [ BY ( column_name {, column_name }… )]
sorting ::= ORDER BY expression [ ASC | DESC ] {, expression [ASC | DESC ] }… 
selection ::= LIMIT [ offset , ] count

The query expression allows you to make the operations UNION, INTERSECT and EXCEPT over the result of a query specification.

The UNION operation merges the record sets, the EXCEPT operation is the difference, and the INTERSECT operation only shows similarities. If the keyword ALL is not specified, then each record may only be in the result once. Specifying ALL will make the query operation faster.

Both operands of the operation must have the same number of columns and types. It is assumed in the case of EXCEPT and INTERSECT that there will be some identical records in both operands (the records that construct the intersection or that are omitted from the difference result).

You can set the record in the operation result using the corresponding specification:

• If CORRESPONDING is not set, it is assumed that both operands have the same number of columns and the types of corresponding columns are the same. These columns will be in the result.
• If the BY keyword is not specified in the corresponding clause, only those columns that have the same names in both operands will be in the result. It is assumed that they are of the same type and that there exists at least one column.
• If the BY keyword is specified in the corresponding clause, only those columns that follow the BY keyword will be in the result. It is assumed, that these columns exist in both operands and that they are of the same type.

The result of a query expression is sorted according to the ORDER BY expression values. DESC means descending sorting and ASC (or nothing) means ascending sorting. If some expression has the same value for two records, then another expression determines the record order. If an index containing NULL values is used for sorting or if no index is used for sorting, then the NULL values are considered lower than any other values. If an index used for sorting does not contain NULL values, then the records with the expression NULL value are not listed in the result.

Expressions in the ORDER BY clause may contain only the column names from the query specification, not any column names from the tables used in the query expression (but not used after SELECT ...).

You can further select a section of records from the query phrase result using the LIMIT clause.

602SQL variations

• The ORDER BY clause is an extension of 602SQL.
• The LIMIT clause is an extension of 602SQL.

Variations from Intermediate to Full Level

• The UNION, EXCEPT and INTERSECT operations can be used in subqueries.

Example:

List companies that have at least one invoice.

SELECT F.company_number,F.name
FROM Companies F
WHERE EXISTS (
    SELECT * 
    FROM Main_invoice H
    WHERE F.company_number=H.company_number)

List companies that do not have an invoice:

SELECT F.company_number,F.name
FROM Companies F
EXCEPT
SELECT F.company_number,F.name
FROM Companies F
WHERE EXISTS (
    SELECT * 
    FROM Main_invoice H
    WHERE F.company_number=H.company_number)

List companies with the maximum amount of invoices:

SELECT SUM(Invoices.value) AS sum,Companies.name
FROM Companies, Invoices
WHERE Invoices.company=Companies.number
GROUP BY Companies.name
ORDER BY sum DESC
LIMIT 1

See

• Set of queries

Query specification

Queries in SQL

Selection of Records from Query Result