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.
select * from A
union
select * from B
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.
select * from A
union all
select * from B
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.
select * from A
intersect
select * from B
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.
select * from A
except
select * from B
This results in 6, 7.
select * from B
except
select * from A
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:
select * from A join B on (A1 = B1)
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:
select A1, A2, B2, B.X from A join B on (A1 = B1)
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:
select A.*, B2, B3 from A join B on (A1 = B1)
Now, back to the meat of the matter. Let's go back to our first
example:
select * from A join B on (A1 = B1)
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.
|
INNER Join Clause Result Set
|
|
A1
|
A2
|
A3
|
A.X
|
B1
|
B2
|
B3
|
B4
|
B.X
|
|
3
|
X
|
X
|
X
|
3
|
X
|
X
|
X
|
X
|
|
4
|
X
|
X
|
X
|
4
|
X
|
X
|
X
|
X
|
|
5
|
X
|
X
|
X
|
5
|
X
|
X
|
X
|
X
|
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.
|
LEFT OUTER (or LEFT) Join Clause Result
Set
|
|
A1
|
A2
|
A3
|
A.X
|
B1
|
B2
|
B3
|
B4
|
B.X
|
|
1
|
X
|
X
|
X
|
Null
|
Null
|
Null
|
Null
|
Null
|
|
2
|
X
|
X
|
X
|
Null
|
Null
|
Null
|
Null
|
Null
|
|
3
|
X
|
X
|
X
|
3
|
X
|
X
|
X
|
X
|
|
4
|
X
|
X
|
X
|
4
|
X
|
X
|
X
|
X
|
|
5
|
X
|
X
|
X
|
5
|
X
|
X
|
X
|
X
|
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.
|
RIGHT OUTER (or RIGHT) Join Clause Result
Set
|
|
A1
|
A2
|
A3
|
A.X
|
B1
|
B2
|
B3
|
B4
|
B.X
|
|
3
|
X
|
X
|
X
|
3
|
X
|
X
|
X
|
X
|
|
4
|
X
|
X
|
X
|
4
|
X
|
X
|
X
|
X
|
|
5
|
X
|
X
|
X
|
5
|
X
|
X
|
X
|
X
|
|
Null
|
Null
|
Null
|
Null
|
6
|
X
|
X
|
X
|
X
|
|
Null
|
Null
|
Null
|
Null
|
7
|
X
|
X
|
X
|
X
|
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.
|
LEFT EXCEPTION (or EXCEPTION) Join Clause Result
Set
|
|
A1
|
A2
|
A3
|
A.X
|
B1
|
B2
|
B3
|
B4
|
B.X
|
|
1
|
X
|
X
|
X
|
Null
|
Null
|
Null
|
Null
|
Null
|
|
2
|
X
|
X
|
X
|
Null
|
Null
|
Null
|
Null
|
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.
|
RIGHT EXCEPTION Join Clause Result Set
|
|
A1
|
A2
|
A3
|
A.X
|
B1
|
B2
|
B3
|
B4
|
B.X
|
|
Null
|
Null
|
Null
|
Null
|
6
|
X
|
X
|
X
|
X
|
|
Null
|
Null
|
Null
|
Null
|
7
|
X
|
X
|
X
|
X
|
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.
select * from A cross join B
|
CROSS Join Clause Result Set
|
|
A1
|
A2
|
A3
|
A.X
|
B1
|
B2
|
B3
|
B4
|
B.X
|
|
1
|
X
|
X
|
X
|
3
|
X
|
X
|
X
|
X
|
|
1
|
X
|
X
|
X
|
4
|
X
|
X
|
X
|
X
|
|
1
|
X
|
X
|
X
|
5
|
X
|
X
|
X
|
X
|
|
1
|
X
|
X
|
X
|
6
|
X
|
X
|
X
|
X
|
|
1
|
X
|
X
|
X
|
7
|
X
|
X
|
X
|
X
|
|
2
|
X
|
X
|
X
|
3
|
X
|
X
|
X
|
X
|
|
2
|
X
|
X
|
X
|
4
|
X
|
X
|
X
|
X
|
|
2
|
X
|
X
|
X
|
5
|
X
|
X
|
X
|
X
|
|
2
|
X
|
X
|
X
|
6
|
X
|
X
|
X
|
X
|
|
2
|
X
|
X
|
X
|
7
|
X
|
X
|
X
|
X
|
|
3
|
X
|
X
|
X
|
3
|
X
|
X
|
X
|
X
|
|
3
|
X
|
X
|
X
|
4
|
X
|
X
|
X
|
X
|
|
3
|
X
|
X
|
X
|
5
|
X
|
X
|
X
|
X
|
|
3
|
X
|
X
|
X
|
6
|
X
|
X
|
X
|
X
|
|
3
|
X
|
X
|
X
|
7
|
X
|
X
|
X
|
X
|
|
4
|
X
|
X
|
X
|
3
|
X
|
X
|
X
|
X
|
|
4
|
X
|
X
|
X
|
4
|
X
|
X
|
X
|
X
|
|
4
|
X
|
X
|
X
|
5
|
X
|
X
|
X
|
X
|
|
4
|
X
|
X
|
X
|
6
|
X
|
X
|
X
|
X
|
|
4
|
X
|
X
|
X
|
7
|
X
|
X
|
X
|
X
|
|
5
|
X
|
X
|
X
|
3
|
X
|
X
|
X
|
X
|
|
5
|
X
|
X
|
X
|
4
|
X
|
X
|
X
|
X
|
|
5
|
X
|
X
|
X
|
5
|
X
|
X
|
X
|
X
|
|
5
|
X
|
X
|
X
|
6
|
X
|
X
|
X
|
X
|
|
5
|
X
|
X
|
X
|
7
|
X
|
X
|
X
|
X
|
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:
on (A1 = B1, A2 = B2 ... )
And we don't have to use equality. This ON clause matches each record in
A to every record in B where A1 > B1:
on (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:
using (X)
is equivalent to
on (A.X = B.X)
Nesting JOINs
In addition to joining a file to another file, you
can join a file to another join:
Select * from A join (B join C on (B1 = C1)) on (A1 = B1)
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. | 
[size="1">Code[/size>