Home
SQL programmingYou can use the HCL OneDB™ implementation of the SQL language to develop applications for HCL OneDB™ database servers.
Guide to SQL: SyntaxThe HCL OneDB™ Guide to SQL: Syntax describes the syntax of the statements, data types, expressions, operators, and built-in functions of the HCL OneDB™ dialect of the SQL language.
SQL statementsThis chapter describes the syntax and semantics of SQL statements that are recognized by HCL OneDB™.
SELECT statementUse the SELECT statement to retrieve values from a database or from an SPL or collection variable. A SELECT operation is called a query.
Set operators in combined queriesThe set operators UNION and UNION ALL can combine the result sets of two queries that specify the same number of columns in the Projection clause, and that have compatible data types in the corresponding columns of both queries.The set operators UNION, UNION ALL, INTERSECT, and MINUS can manipulate the result sets of two queries that specify the same number of columns in the Projection clause, and that have compatible data types in the corresponding columns of both queries.
MINUS operatorWhen two queries are combined by this set operator, the MINUS operator calculates the set difference between the rows returned by the SELECT statement on the left side and the rows returned by the SELECT statement on the right side.

MINUS operator

When two queries are combined by this set operator, the MINUS operator calculates the set difference between the rows returned by the SELECT statement on the left side and the rows returned by the SELECT statement on the right side.

The MINUS operator returns only the rows that are present in first result set but that are not in the second set. The MINUS results are always distinct or unique rows, because MINUS eliminates any duplicate rows.

For the same data set that is listed in the INTERSECT Operator topic, the following query returns all the distinct rows from result set of the query to the left of the MINUS operator that are not in the result set of the query on the right:

SELECT col1 FROM t1 MINUS SELECT col1 FROM t2;

       col1 

          2

1 row(s) retrieved.

The MINUS operator has some (but not all) of the same restrictions as the UNION operator, but MINUS does not support the ALL keyword that enables UNION to return duplicate values. See also the topic Restrictions on a Combined SELECT.