Home arrow MySQL arrow Page 5 - MySQL Table Joins

Self-joins - MySQL

One of the great characteristics of SQL (Structured Query Language) is thefact that onecan develop, access and modify data across various tables. There are severalbenefits tothis, including greater ease of manipulation, increased speed of access, andreduceddata redundancy. In MySQL (as well as many other SQL languages), this isaccomplished via the join command.

  1. MySQL Table Joins
  2. The Cross Join
  3. The Equi-join
  4. The Left Join
  5. Self-joins
By: W.J. Gilmore
Rating: starstarstarstarstar / 150
July 06, 1999

print this article


The self-join provides the administrator with a powerful method of centralizing relational data to a single table. In fact, the self-join is performed by joining a particular table to itself. Let's illustrate this concept with an example:

Suppose we are in control of a large database containing information regarding various pieces of hardware used to build a computer workstation. A workstation may consist of a desk, pc, monitor, keyboard and mouse. Furthermore, the desk can be considered the 'parent' of all other parts of the workstation. We want to keep accurate records of each workstation, so we will correlate all parts of a specific workstation together via a unique id number. Actually, each part will contain two id numbers, one unique to that specific item, and one identifying its' parent (the desk) id number.

Assume that this is our table:

pc345200mhz pcd001
pa556350 mhz pcd002

Notice that the desktop does not have a parent_id, since it is in fact the parent for all of its' corresponding parts. With the table filled with data, we can now begin querying it for useful information. Also note that while our table is simple for reason of best illustration of use of the self-join, one could provide significantly more useful information regarding each item.

mysql> select t1.*, t2.* from page5 as t1, page5 as t2;
So what is the outcome? Like previously seen with such as join regarding two tables, each row from the first table will be matched with every row in the second table. Try it and see. Again, however, this is not very useful to us. Let's look at a more interesting example:

We are interested in viewing information regarding a specific workstation in which we several technical support calls had been made. We know what the particular workstation id is (the desk id). Let's query the database to pull up all relevant pieces of this workstation:

mysql> select parent.uniqid, parent.name, child.uniqid, child.name     -> from page5 as parent, page5 as child     -> where child.parent_id = parent.uniqid AND parent.uniqid = "d001";
This provides a much more interesting outcome, displayed as follows:
d001desktoppc345200 mhz pc

The self-join is also used as an efficient method of verifying table data. Since the uniqid column within the table is intended to be unique, it would not be good if the data-entry dept. accidentally entered two items with the same uniqid into the database. This could be periodically checked by using a self-join. Assume that we modified the 350 mhz pc uniqid to be 'm156' (which is incidentally the uniqid value of the monitor belonging to workstation 'd002'). Consider the following example:

mysql> select parent.uniqid, parent.name, child.uniqid, child.name     -> from page5 as parent, page5 as child     -> where parent.uniqid = child.uniqid AND parent.name <> child.name
This would result in the following:

m156350 mhz pcm156monitor
m156monitorm156350 mhz pc

There you have it. Table joins made easy. Try playing around with variations of the commands highlighted within this article to gain a clear understanding of the syntax. Once this is understood, you will find that table joins will play an integral part in your development activities. Be sure to check out MySQL's various discussion groups (http://www.mysql.com), as there is usually quite a bit of information exchanged regarding table joins.

>>> More MySQL Articles          >>> More By W.J. Gilmore

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: