Joining 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 for i: 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.