Joins

Up till now we have focussed on grabbing data from a single table in the database. However, the true power of the relational database comes from the fact that data is stored in multiple tables that are related by values within them. Thus, performing a uni-table SELECT is only the most trivial of operations afforded by SQL.

The true power of the SQL database lies in its ability to create views based upon the data in multiple tables using the JOIN operation.

To create a JOIN, you simply define two or more tables in your SELECT-FROM statement. For example, consider the following which joins the CLIENTS and SALES tables into a single view:

    SELECT C_NAME, S_NUM, S_QUANTITY
    FROM SALES, CLIENTS;

The previous SQL code would yield the following results:

    C_NAME		S_NUM	S_QUANTITY
    -------------------------------------
    Jason Lim		001	1
    Rick Tan		001	1
    Stephen Petersen	001	1
    Jason Lim		002	2
    Rick Tan		002	2
    Stephen Petersen	002	2
    Jason Lim		003	1
    Rick Tan		003	1
    Stephen Petersen	003	1
    -------------------------------------

Notice that the results will contain every combination of the two tables.

But what happens if two tables share the same name? SQL assigns name space by specifying that when two column names are the same that you should specify the table name along with the column name with a period in between. Thus, consider the following example in which column C_NUM is shared by both the CLIENTS and the SALES tables.

    SELECT C_NAME, S_NUM, SALES.C_NUM
    FROM SALES, CLIENTS;

In this case, SQL will draw the data from the C_NUM column in the SALES table and not from the CLIENTS table. Being able to specify columns exactly is important because it helps us sculpt complex and more useful joins using the WHERE clause.

As you saw above, a raw JOIN returns perhaps too much data. For example, you might want to see unique sales information tied to the name of the client. In this case, you would use the WHERE clause to limit the results as follows:

    SELECT C_NAME, S_NUM, S_AMOUNT
    FROM SALES, CLIENTS
    WHERE CLIENTS.C_NUM = SALES.C_NUM;

    C_NAME		S_NUM	S_AMOUNT
    -------------------------------------
    Rick Tan		001	 99.99
    Jason Lim		002	199.98
    Stephen Petersen	003	865.99
    -------------------------------------

Averages
Introduction to Databases for the Web | Table of Contents
Subqueries