Database Reference
In-Depth Information
In this case the condition will check for a matching piece of data in a column that
appears in the tables to be joined. Most likely this will be matching a foreign key in one
table with a primary key in another.
The last paragraph may seem a bit confusing, but as ever with SQL its much easier to
demonstrate by example, so we will turn the previous cross join into an equi-join by adding
a WHERE clause as follows:
SELECT
*
FROM
log, webpage
WHERE
webpage.id = log.webpageid
Figure 7.4 shows the results of the above query. Notice how the WHERE clause restricted
the number of rows of the query to 5, and got all and more of the data that we want on each
row.
Notice that in the WHERE clause we specified the table name as well as the column name
for the match. It is not always necessary to do this but if you get into the habit of doing so it
can save you a lot of time debugging later. For instance, in our example, if we had just spec-
ified the ID column without the table name, we could have been referring to the ID column
in either the webpage table or the log table, as there are two with the same name.
If you cast your mind back to the start of this chapter you will remember we were trying
to get more information from the log table so that we could see the title of the page that we
were looking for as opposed to only its ID. We needed to join the pages together to find this
out. We can now tidy up this equi-join by restricting the columns we return as follows:
SELECT
webpage.title, log.*
FROM
log, webpage
WHERE
webpage.id = log.webpageid
Figure 7.5 shows the results of this equi-join. What the join has done is take every row
from the log table, which contains the foreign key pointing to the entry in the webpage
table. It then has included the relevant column from the webpage table ( title ) which
matches that foreign key.
Figure 7.4
A basic equi-join.
Search WWH ::




Custom Search