Summary

This chapter builds on concepts introduced in Compose SELECT statements. It provides sample syntax and results for more advanced kinds of SELECT statements, which are used to query a relational database. This chapter presents the following material:

• Introduces the GROUP BY and HAVING clauses, which you can use with aggregates to return groups of rows and apply conditions to those groups
• Shows how to join a table to itself with a self-join to compare values in a column with other values in the same column and to identify duplicates
• Explains how an outer join treats two or more tables asymmetrically, and provides examples of the four kinds of outer join using both the HCL® OneDB® extension and ANSI join syntax.
• Describes how to nest a SELECT statement in the WHERE clause of another SELECT statement to create correlated and uncorrelated subqueries and shows how to use aggregate functions in subqueries
• Describes how to nest SELECT statements in the FROM clause of another SELECT statement to specify uncorrelated subqueries whose results are a data source for the outer SELECT statement
• Demonstrates how to use the keywords ALL, ANY, EXISTS, IN, and SOME to create subqueries, and the effect of adding the keyword NOT or a relational operator
• Describes how to use collection subqueries to convert relational data to a collection of type MULTISET and how to use collection-derived tables to access elements within a collection
• Discusses the union, intersection, and difference set operations
• Shows how to use the UNION and UNION ALL keywords to create compound queries that consist of two or more SELECT statements