Getting information from multiple tables

With SQL, you can get information from columns in more than one table. This operation is called a join operation.

In SQL, a join operation is specified by placing the names of those tables that you want to join in the same FROM clause of a SELECT statement.

Suppose that you want to see a list of all the suppliers and the item numbers and item names for their supplied items. The item name is not in the SUPPLIERS table; it is in the INVENTORY_LIST table. Using the common column, ITEM_NUMBER, you can see all of the columns as if they were from a single table.

Whenever the same column name exists in two or more tables being joined, the column name must be qualified by the table name to specify which column is being referenced. In this SELECT statement, the column name ITEM_NUMBER is defined in both tables, so it needs to be qualified by the table name. If the columns have different names, no qualification is needed.

To perform this join operation, enter the following SELECT statement by typing it directly on the Enter SQL Statements display or by prompting:

SELECT SUPPLIER_NUMBER, SAMPLECOLL.INVENTORY_LIST.ITEM_NUMBER, ITEM_NAME
      FROM SAMPLECOLL.SUPPLIERS, SAMPLECOLL.INVENTORY_LIST
      WHERE SAMPLECOLL.SUPPLIERS.ITEM_NUMBER
                         = SAMPLECOLL.INVENTORY_LIST.ITEM_NUMBER

If you use prompting, you need to type both table names on the FROM tables input line.

Another way to enter the same statement is to use a correlation name. A correlation name provides another name for a table name to use in a statement. A correlation name must be used when the table names are the same. It can be specified by following each table name in the FROM list. The previous statement can be rewritten as:

SELECT SUPPLIER_NUMBER, Y.ITEM_NUMBER, ITEM_NAME
      FROM SAMPLECOLL.SUPPLIERS X, SAMPLECOLL.INVENTORY_LIST Y
      WHERE X.ITEM_NUMBER = Y.ITEM_NUMBER

In this example, SAMPLECOLL.SUPPLIERS is given a correlation name of X and SAMPLECOLL.INVENTORY_LIST is given a correlation name of Y. The names X and Y are then used to qualify the ITEM_NUMBER column name.

Running this example returns the following output.

                                 Display Data
                                             Data width . . . . . . :      45
Position to line  . . . . .              Shift to column  . . . . . .
....+....1....+....2....+....3....+....4....+
SUPPLIER_NUMBER  ITEM    ITEM
                 NUMBER  NAME
     1234        153047  Pencils, red
     1234        229740  Lined tablets
     1234        303476  Paper clips
     9988        153047  Pencils, red
     9988        559343  Envelopes, legal
     2424        153047  Pencils, red
     2424        303476  Paper clips
     5546        775298  Chairs, secretary
     3366        303476  Paper clips
     3366        073956  Pens, black
********  End of data  ********
 
F3=Exit      F12=Cancel      F19=Left      F20=Right      F21=Split

Note: Because no ORDER BY clause was specified for the query, the order of the rows returned by your query may be different.

The data values in the result table represent a composite of the data values contained in the two tables INVENTORY_LIST and SUPPLIERS. This result table contains the supplier number from the SUPPLIER table and the item number and item name from the INVENTORY_LIST table. Any item numbers that do not appear in the SUPPLIER table are not shown in this result table. The results are not guaranteed to be in any order unless the ORDER BY clause is specified for the SELECT statement. Because you did not change any column headings for the SUPPLIER table, the SUPPLIER_NUMBER column name is used as the column heading.

The following example shows how to use ORDER BY to guarantee the order of the rows. The statement first sorts the result table by the SUPPLIER_NUMBER column. Rows with the same value for SUPPLIER_NUMBER are sorted by their ITEM_NUMBER.

SELECT SUPPLIER_NUMBER,Y.ITEM_NUMBER,ITEM_NAME
	FROM SAMPLECOLL.SUPPLIERS X,SAMPLECOLL.INVENTORY_LIST Y
	WHERE X.ITEM_NUMBER = Y.ITEM_NUMBER
	ORDER BY SUPPLIER_NUMBER,Y.ITEM_NUMBER

Running the previous statement produces the following output.

                                 Display Data
                                             Data width . . . . . . :      45
Position to line  . . . . .              Shift to column  . . . . . .
....+....1....+....2....+....3....+....4....+
SUPPLIER_NUMBER  ITEM    ITEM
                 NUMBER  NAME
     1234        153047  Pencils, red
     1234        229740  Lined tablets
     1234        303476  Paper clips
     2424        153047  Pencils, red
     2424        303476  Paper clips
     3366        073956  Pens, black
     3366        303476  Paper clips
     5546        775298  Chairs, secretary
     9988        153047  Pencils, red
     9988        559343  Envelopes, legal
********  End of data  ********
 
F3=Exit      F12=Cancel      F19=Left      F20=Right      F21=Split