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.
FROM ClauseThe FROM clause of the SELECT statement lists table objects from which to retrieve data.
ANSI JoinsIf the FROM clause specifies more than one table reference, the query can join rows from several tables or views. A join condition specifies a relationship between at least one column from each table to be joined. Because the columns in a join condition are being compared, they must have compatible data types.
Using the ON Clause
Specifying a Post-Join Filter

Specifying a Post-Join Filter

When you use the ON clause to specify the join, you can use the WHERE clause as a post-join filter. The database server applies the post-join filter of the WHERE clause to the results of the outer join.

The following example illustrates the use of a post-join filter. This query returns data from the stores_demo database. Suppose you want to determine which items in the catalog are not being ordered. The next query creates an outer join of the data from the catalog and items tables and then determines which catalog items from a specific manufacturer (HRO) have not sold:

SELECT c.catalog_num, c.stock_num, c.manu_code, i.quantity 
   FROM catalog c LEFT JOIN items i 
   ON c.stock_num = i.stock_num AND c.manu_code = i.manu_code 
   WHERE i.quantity IS NULL AND c.manu_code = "HRO";

The WHERE clause contains the post-join filter that locates the rows of HRO items in the catalog for which nothing has been sold.

When you apply a post-join filter to a base table in the dominant or subordinate part of an outer join, you might improve performance. For more information, see your HCL OneDB™ Performance Guide.