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.
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.SELECT SUPPLIER_NUMBER, Y.ITEM_NUMBER, ITEM_NAME
FROM SAMPLECOLL.SUPPLIERS X, SAMPLECOLL.INVENTORY_LIST Y
WHERE X.ITEM_NUMBER = Y.ITEM_NUMBER
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
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.
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