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