# Multiple Table Specifications in the FROM Clause

### Multiple Table Specifications in the FROM Clause

Until now, we have used only one table specification in the FROM clause. If we want to present data from different tables in our result table, we must specify multiple tables in the FROM clause.

##### 6. Get the team number and the name of the captain of each team.

 The TEAMS table holds information about team numbers and the player numbers of each team. However, the names of the captains are not stored in the TEAMS table but in the PLAYERS table. In other words, we need both tables. Both must be mentioned in the FROM clause. ```SELECT TEAMNO, NAME FROM TEAMS, PLAYERS WHERE TEAMS.PLAYERNO = PLAYERS.PLAYERNO ``` The intermediate result of the FROM clause is: ```TEAMNO PLAYERNO DIVISION PLAYERNO NAME ... ------ -------- -------- -------- --------- --- 1 6 first 6 Parmenter ... 1 6 first 44 Baker ... 1 6 first 83 Hope ... 1 6 first 2 Everett ... 1 6 first 27 Collins ... 1 6 first 104 Moorman ... 1 6 first 7 Wise ... 1 6 first 57 Brown ... 1 6 first 39 Bishop ... 1 6 first 112 Bailey ... 1 6 first 8 Newcastle ... 1 6 first 100 Parmenter ... 1 6 first 28 Collins ... 1 6 first 95 Miller ... 2 27 second 6 Parmenter ... 2 27 second 44 Baker ... 2 27 second 83 Hope ... 2 27 second 2 Everett ... 2 27 second 27 Collins ... 2 27 second 104 Moorman ... 2 27 second 7 Wise ... 2 27 second 57 Brown ... 2 27 second 39 Bishop ... 2 27 second 112 Bailey ... 2 27 second 8 Newcastle ... 2 27 second 100 Parmenter ... 2 27 second 28 Collins ... 2 27 second 95 Miller ... ```

Explanation Each row of the PLAYERS table is aligned "beside" each row of the TEAMS table. This results in a table in which the total number of columns equals the number of columns in one table plus the number of columns in the other table, and in which the total number of rows equals the number of rows in one table multiplied by the number of rows in the other table. We call this result the Cartesian product of the tables concerned.

In the WHERE clause, each row where the value in the TEAMS.PLAYERNO column equals the one in the PLAYERS.PLAYERNO column is selected:

```TEAMNO  PLAYERNO  DIVISION  PLAYERNO  NAME       ...
------  --------  --------  --------  ---------  ---
1         6  first            6  Parmenter  ...
2        27  second          27  Collins    ...
```

The end result is:

```TEAMNO  NAME
------  ---------
1  Parmenter
2  Collins
```

In this example, it is essential to specify the table name in front of the PLAYERNO column. Without qualifying the column name, it would be impossible for SQL to determine which column was intended.

Conclusion: If you use a column name that appears in more than one table specified in the FROM clause, it is mandatory to include a table specification with the column specification.

##### 7. For each penalty, find the payment number, the amount of the penalty, the player number, the name, and the initials of the player who incurred the penalty.

 The payment numbers, the amounts, and the player numbers are held in the PENALTIES table, while names and initials are found in the PLAYERS table. Both tables must be included in the FROM clause: ```SELECT PAYMENTNO, PENALTIES.PLAYERNO, AMOUNT, NAME, INITIALS FROM PENALTIES, PLAYERS WHERE PENALTIES.PLAYERNO = PLAYERS.PLAYERNO ``` The intermediate result from the FROM clause is (not all the rows have been included): ```PAYMENTNO PLAYERNO AMOUNT ... PLAYERNO NAME INITIALS ... --------- -------- ------ --- -------- --------- -------- --- 1 6 100.00 ... 6 Parmenter R ... 1 6 100.00 ... 44 Baker E ... 1 6 100.00 ... 83 Hope PK ... 1 6 100.00 ... 2 Everett R .. : : : : : : 2 44 75.00 ... 6 Parmenter R ... 2 44 75.00 ... 44 Baker E .. 2 44 75.00 ... 83 Hope PK ... 2 44 75.00 ... 2 Everett R ... : : : : : : 3 27 100.00 ... 6 Parmenter R ... 3 27 100.00 ... 44 Baker E ... 3 27 100.00 ... 83 Hope PK ... 3 27 100.00 ... 2 Everett R ... : : : : : : : : : : : : ``` The intermediate result after processing the FROM clause is: ```PAYMENTNO PLAYERNO AMOUNT ... PLAYERNO NAME INITIALS ... --------- -------- ------ --- -------- --------- -------- --- 1 6 100.00 ... 6 Parmenter R ... 2 44 75.00 ... 44 Baker E ... 3 27 100.00 ... 27 Collins DD ... 4 104 50.00 ... 104 Moorman D ... 5 44 25.00 ... 44 Baker E ... 6 8 25.00 ... 8 Newcastle B ... 7 44 30.00 ... 44 Baker E ... 8 27 75.00 ... 27 Collins DD ... ``` The end result is: ```PAYMENTNO PLAYERNO AMOUNT NAME INITIALS --------- -------- ------ --------- -------- 1 6 100.00 Parmenter R 2 44 75.00 Baker E 3 27 100.00 Collins DD 4 104 50.00 Moorman D 5 44 25.00 Baker E 6 8 25.00 Newcastle B 7 44 30.00 Baker E 8 27 75.00 Collins DD ``` To avoid ambiguity, the table name must be specified in front of the PLAYERNO column in the SELECT clause. The order of the table specifications in a FROM clause does not affect the result of this clause and the end result of the table expression. The SELECT clause is the only clause that determines the order of the columns in the result. The ORDER BY clause is used to determine the order in which the rows will be presented. Thus, the results of the next two statements are equal: ```SELECT PLAYERS.PLAYERNO FROM PLAYERS, TEAMS WHERE PLAYERS.PLAYERNO = TEAMS.PLAYERNO ``` and ```SELECT PLAYERS.PLAYERNO FROM TEAMS, PLAYERS WHERE PLAYERS.PLAYERNO = TEAMS.PLAYERNO ```

 Exercise 7.1: Indicate why these SELECT statements are not correctly formulated: ```SELECT PLAYERNO FROM PLAYERS, TEAMS ``` ```SELECT PLAYERS.PLAYERNO FROM TEAMS ``` Exercise 7.2: For each clause of the following statement, determine the intermediate result and the result. Also, give a description of the question that underlies the statement. ```SELECT PLAYERS.NAME FROM TEAMS, PLAYERS WHERE PLAYERS.PLAYERNO = TEAMS.PLAYERNO ``` Exercise 7.3: For each penalty, find the payment number, the amount, and the number and name of the player who incurred it. Exercise 7.4: For each penalty incurred by a team captain, find the payment number and the captain's name.