Informix Guide to SQL: Tutorial Composing Advanced SELECT Statements

This chapter builds on concepts introduced in Chapter 2. 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
• Introduces the keyword OUTER, explains how an outer join treats two or more tables asymmetrically, and provides examples of the four kinds of outer join
• 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
• 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