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:

  1. SELECT   PLAYERNO
    FROM     PLAYERS, TEAMS
    

  2. 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.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows