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 Informix® 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