Lesson 4 | Create a Simple Join |
Objective | Use the WHERE clause to create a simple join |
Create Simple Join
Here is the SQL statement again, for reference:
SELECT pub_name FROM publishers, titles
WHERE publishers.pub_id=titles.pub_ID
AND title_id='BU1032'
An interesting change comes if you ask to return a column that is found in both tables. In other words, if your SELECT statement calls for
the return of the pub_id column, you have to make one change.
Because the column is found in both tables, you have to indicate which table you want to retrieve the value from. This may seem a bit confusing, but it will make more sense if you consider the following statement:
SELECT titles.pub_id, pub_name FROM publishers, titles
WHERE publishers.pub_id=titles.pub_ID
AND title_id='BU1032'
By adding the name of the table and a period in front of the column name, you indicate where the information should be obtained. If you forget this, by the way, the SQL engine will likely give you a message that indicates that the
column name is ambiguous. Simply add the table name and period and you will be off and running.
You will also notice that you can still use a typical WHERE type clause to test for a value.
Combining these will, in this case, return a single publisher's name, the one associated with the book with the title ID indicated.
Simple Join - Exercise
Click on the Exercise link below to make a simple join of your own.
Simple Join - Exercise
In the next lesson, we will begin an examination of INNER and OUTER joins.