Join data from more than one table

Sometimes the information you want to see is not in a single table. To form a row of the result table, you might want to retrieve some column values from one table and some column values from another table. You can retrieve and join column values from two or more tables into a single row.

Several different types of joins are supported by DB2^® UDB for iSeries™: inner join, left outer join, right outer join, left exception join, right exception join, and cross join.

Usage notes on join operations

When you join two or more tables, consider the following items:

If there are common column names, you must qualify each common name with the name of the table (or a correlation name). Column names that are unique do not need to be qualified. However, the USING clause can be used in a join to allow you to identify columns that exist in both tables without specifying table names.
If you do not list the column names you want, but instead use SELECT *, SQL returns rows that consist of all the columns of the first table, followed by all the columns of the second table, and so on.
You must be authorized to select rows from each table or view specified in the FROM clause.
The sort sequence is applied to all character, or UCS-2 or UTF-16 graphic columns being joined.

Inner join
An inner join returns only the rows from each table that have matching values in the join columns. Any rows that do not have a match between the tables will not appear in the result table.
Left outer join
A left outer join returns all the rows that an inner join returns plus one row for each of the other rows in the first table that did not have a match in the second table.
Right outer join
A right outer join returns all the rows that an inner join returns plus one row for each of the other rows in the second table that did not have a match in the first table. It is the same as a left outer join with the tables specified in the opposite order.
Exception join
A left exception join returns only the rows from the first table that do not have a match in the second table.
Cross join
A cross join (or Cartesian Product join) returns a result table where each row from the first table is combined with each row from the second table.
Simulate a full outer join
Like the left and right outer joins, a full outer join returns matching rows from both tables. However, a full outer join also returns non-matching rows from both tables; left and right.
Multiple join types in one statement
There are times when more than two tables need to be joined to produce the result that you want.

Parent topic: Retrieve data using the SELECT statement