| |
Join A Table To Itself Using Aliases
By Taylor Gillespie
Expert Author
Article Date: 2010-10-12
SQL programmers are familiar with the common ways of joining multiple fields from multiple tables based on predicate criteria by using Inner and Outer, Left, and Right. But sometimes an SQL programmer will find a time when the need arises to combine, or join, the fields of a table to itself.
Sometimes, the related information is all contained in one table, and a query that joins a table to itself becomes desirable. It possible using a query called a "self-join".
The example shown illustrates a self-joining situation where a query needs "...to find all pairings of two employees in the same country ... [and] all the employee information is contained within a single large table." In order for the query to join a table to itself, table aliases must be used. In that way, the join references the aliases which point to the same table. The Wikipedia self-join example is a good one because it illustrates a case where a self-join is necessary because of the one employee table; however, if self-joins proliferate it can be a sign of badly needing more normalization in the database schema. To avoid having to use self-joins, the related data should be located in a separate table and referenced through foreign keys. Self-joins
When a scenario comes around where you need to join a table to itself, it is possible through the use of aliases. A self-join can identify the relevant data in a query when a use-case makes it necessary without altering the database schema. Be weary that self-joins are often a symptom of a poorly designed database in need of some normalization, but they will in any case get the job done.
About the Author:
Taylor is a Staff Writer for WebProNews
|
|
