602SQL Documentation Index  

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:

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

Variations from Intermediate to Full Level

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