The From Clause and SQL Queries - Table Links
(Page 4 of 4 )
The second deviation from the simple from clause definition is the mandate that if more than one table appears in thefromclause, then the conditions used to link the tables must be included as well. This is not a requirement of MySQL or any other database server, but it is the ANSI-approved method of joining multiple tables, and it is the most portable across the various database servers. Joining multiple tables will be explored in depth in Chapters 5 and 10, but here’s a simple example in case I have piqued your curiosity:
mysql> SELECT employee.emp_id, employee.fname,
-> employee.lname, department.name dept_name
-> FROM employee INNER JOIN department
-> ON employee.dept_id = department.dept_id;
|
emp_id fname | lname | dept_name | |
|
| 1 Michael | Smith | Administration |
| 2 Susan | Barker | Administration |
| 3 Robert | Tyler | Administration |
| 4 Susan Hawthorne Operations | |
| 5 John | Gooding | Loans | |
| 6 Helen | Fleming | Operations | |
| 7 Chris | Tucker | Operations | |
| 8 Sarah | Parker | Operations | |
| 9 Jane | Grossman | Operations | |
| 10 Paula | Roberts | Operations | |
| 11 Thomas | Ziegler | Operations | |
| 12 Samantha Jameson | Operations | |
| 13 John | Blake | Operations | |
| 14 Cindy | Mason | Operations | |
| 15 Frank | Portman | Operations | |
| 16 Theresa | Markham | Operations | |
| 17 Beth | Fowler | Operations | |
| 18 Rick | Tulman | Operations | |
|
18 rows in set (0.05 sec)
The previous query displays data from both theemployeetable (emp_id,fname,lname) and thedepartmenttable (name), so both tables are included in thefromclause. The mechanism for linking the two tables (referred to as a join) is the employee’s department affiliation stored in theemployeetable. Thus, the database server is instructed to use the value of thedept_idcolumn in theemployeetable to look up the associated department name in thedepartmenttable. Join conditions for two tables are found in theonsubclause of thefromclause; in this case, the join condition isON e.dept_id = d.dept_id. Again, please refer to Chapter 5 for a thorough discussion of joining multiple tables.
Defining Table Aliases
When multiple tables are joined in a single query, you need a way to identify which table you are referring to when you reference columns in the select, where,group by,having, andorder byclauses. You have two choices when referencing a table outside thefromclause:
- Use the entire table name, such asemployee.emp_id.
- Assign each table an alias and use the alias throughout the query.
In the previous query, I chose to use the entire table name in theselectandon clauses. Here’s what the same query looks like using table aliases:
SELECT e.emp_id, e.fname, e.lname,
d.name dept_name
FROM employee e INNER JOIN department d
ON e.dept_id = d.dept_id;
If you look closely at thefromclause, you will see that theemployeetable is assigned the aliase, and thedepartmenttable is assigned the aliasd. These aliases are then used in theonclause when defining the join condition as well as in theselect clause when specifying the columns to include in the result set. I hope you will agree that using aliases makes for a more compact statement without causing confusion (as long as your choices for alias names are reasonable).
Please check back next week for the continuation of this article.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter three of the book Learning SQL, written by Alan Beaulieu (O'Reilly; ISBN: 0596007272). Check it out today at your favorite bookstore. Buy this book now.
|
|
