The most basic SQL SELECT query selects data from one file and produces a table of resulting data: the result set. It is possible, however, to create a result set combining the data from more than one file.

Two types of multi-file result sets can be generated. Let's look at both of them.

First Type

The first is a set that combines the result sets of two or more subselects into one final result set. Every subselect result set has the same number and type of columns, and they are combined using the special keywords UNION, UNION ALL, INTERCEPT, and EXCEPT.

For this discussion, let's assume two single column tables: A, which contains rows 1, 2, 3, 4, 5, and B, which contains 3, 4, 5, 6, 7.

UNION

This keyword simply returns a set consisting of all rows in A and B. All the rows are unique; duplicate rows are eliminated. So the following code results in 1, 2, 3, 4, 5, 6, 7.

UNION ALL

This keyword works just like UNION, except duplicate rows are not eliminated. This results in 1, 2, 3, 3, 4, 4, 5, 5, 6, 7.

INTERSECT

This returns a result set consisting of only those rows that exist in both subsets. Duplicate rows are eliminated. So this results in 3, 4, 5.

EXCEPT

This returns a result set consisting of only those rows from the first subset that do not exist in the second. Duplicate rows are eliminated. The result is 1, 2.

This results in 6, 7.

Second Type

The second type of multi-file result set comes from one subselect whose result columns are created by combining columns from more than one file. These are more affectionately known as JOINs, because you are JOINing columns from more than one file into one row.

Consider our two tables A and B. Let's assume the column we talked about above is called A1 for A and B1 for B. And let's assume now that A also has columns A2, A3, and X, and B has B2, B3, B4, and X. Here is the general syntax of a SELECT statement with a JOIN:

We are joining the data from the columns of A and B into one row, so a result row will contain the columns A1, A2, A3, X (from A), B1, B2, B3, B4, and X (from B).

You can, of course, specify which columns you want from each table, like this example:

Note that I had to qualify column X, because both tables have a column X.

Here's an example that uses the all-columns asterisk (*) syntax on specific files:

Now, back to the meat of the matter. Let's go back to our first example:

The ON clause tells how the rows from A will be matched to the rows from B. In this case, we are matching rows where the data in A1 is equal to the data in B1. The tells us which rows are returned. And here are the join clauses:

INNER

This default syntax selects only rows from A that have matching rows on B. This is the result set, where X indicates any data.

LEFT OUTER (or LEFT)

This selects all rows from A (the file on the left side of the JOIN clause). If there are no matches from B, the columns from B will be null.

RIGHT OUTER (or RIGHT)

This selects all rows from B (the file on the right side of the JOIN clause). If there are no matches from A, the columns from A will be null.

LEFT EXCEPTION (or EXCEPTION)

This selects all rows from A (the file on the left side of the JOIN clause) that do not have a match in B. The columns from B will be null.

RIGHT EXCEPTION

This selects all rows from B (the file on the right side of the JOIN clause) that do not have a match in A. The columns from A will be null.

CROSS

This matches each record of A to every record of B (a Cartesian product). The syntax is slightly different. There is no ON clause.

More about ON

Our ON clause (A1 = B1) is the simplest form of the ON clause; we are matching on one pair of columns. We can match on more than one:

And we don't have to use equality. This ON clause matches each record in A to every record in B where A1 > B1:

If all the columns you want to match on have the same name between the files, you can use USING in place of ON:

is equivalent to

Nesting JOINs

In addition to joining a file to another file, you can join a file to another join:

In this statement, the columns from B and C are joined into a result set, matching B1 to C1. Then, the columns of that result set are joined to the columns of A, matching A1 to B1. This is not the B1 from B, but the B1 of B join C.

So, there you have it--a quick review of SQL result sets. Let it open up the possibilities for your queries.

Doug Eckersley is the iSeries programmer with a premier homebuilder in Columbus. He has been programming on the iSeries for 10 years and has been in the business for 15. He is certified by IBM.