Joe Celko's SQL for Smarties, 3rd Edition

25.5. Comparing Arrays in Table Format

It is often necessary to compare one array or set of values with another when the data is represented in a table. Remember that comparing a set with a set does not involve ordering the elements, whereas an array does. For this discussion, let us create two tables, one for employees and one for their dependents. The children are subscripted in the order of their births—i.e., 1 is the oldest living child, 2 is the second oldest, and so forth.

CREATE TABLE Employees (emp_id INTEGER PRIMARY KEY, emp_name CHAR(15) NOT NULL, ... ); CREATE TABLE Dependents (emp_id INTEGER NOT NULL -- the parent kid CHAR(15) NOT NULL, -- the array element birthorder INTEGER NOT NULL, -- the array subscript PRIMARY KEY (emp_id, ...