TABLE OF CONTENTS I am defining subset operations as queries, which extract a particular subset from a given set, as opposed to set operations, which work among sets. The obvious way to extract a subset from a table is just to use a WHERE clause, which will pull out the rows that meet that criterion. But not all the subsets we want are easily defined by such a simple predicate. This chapter is a collection of tricks for constructing useful, but not obvious, subsets from a table.
SQL is a set-oriented language, which cannot identify individual rows by their physical positions in a disk file that holds a table. Instead, a unique logical key is detected by logical expressions, and a row is retrieved. If you are given a file of employees in which the ordering of the file is based on their employee numbers, and you want to pick out every nth employee record for a survey, the job is easy. You write a procedure that loops through the file and writes every nth one to a second file.
The immediate thought of how this should be done in SQL is to simply compute MOD (emp_nbr, :n), where MOD() is the modulo function found in most SQL implementations, and save those employee rows where this function is zero. The trouble is that employees are...
