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.
WHERE clause of SELECTThe WHERE clause can specify join conditions for HCL OneDB™-extension joins, post-join filters for ANSI-compliant joins, and for search criteria on data values.
Using a Condition in the WHERE Clause
EXISTS Subquery From the EXISTS subquery, rows that satisfy EXISTS conditions in one or more columns can be returned. (Similarly, the NOT EXISTS subquery can returns rows that satisfy NOT EXISTS conditions in one or more columns.)

EXISTS Subquery

From the EXISTS subquery, rows that satisfy EXISTS conditions in one or more columns can be returned. (Similarly, the NOT EXISTS subquery can returns rows that satisfy NOT EXISTS conditions in one or more columns.)

The following example of a SELECT statement with a NOT EXISTS subquery returns the stock number and manufacturer code for every item that has never been ordered (and is therefore not listed in the items table).

It is appropriate to use a NOT EXISTS subquery in this SELECT statement because you need the correlated subquery to test both stock_num and manu_code in the items table.

SELECT stock_num, manu_code FROM stock
   WHERE NOT EXISTS
      (SELECT stock_num, manu_code FROM items
         WHERE stock.stock_num = items.stock_num AND
            stock.manu_code = items.manu_code);

The preceding example would work equally well if you use a SELECT * in the subquery in place of the column names, because you are testing for the existence of a row or rows.

For additional information, see the EXISTS Subquery condition.