Difference between revisions of "Joining Tables in MySQL"

Revision as of 20:27, 19 October 2007

Table joins provide a way of associating data that resides in different tables during data retrieval. Suppose we have a table in our MySQL database called product. This table contains data about the products that an on-line electronics store sells. Each product in turn is purchased from a supplier and a single supplier can supply multiple items listed in the product table.

Given this scenario we have two options for storing contact information about the supplier. One option is to store the supplier contact information information with each row in the product where the product is sourced from that supplier. Whilst this would clearly work it is a highly inefficient approach to take because we would be duplicating the suppliers contact information for every product that the supplier sells to us. Also, if the supplier moves to a new location we would have to update every single row in the product table associated with that supplier.

A much better approach would be to have a separate supplier table which contains the contact information for each supplier and then reference this table when we want to extract the supplier information for a particualr product in the product table. This approach is known using a join and forms the basis of a relational database.

How Does a Join Work?

A join works through the use of keys. Continuing our example, our supplier table contains a column designated as the supplier_id. This column is configured as the primary key (for details on primary keys see Database Basics). The product table contains all of the products sold by our company, including product id, product description and product name. In addition it also contains the supplier id of the supplier from which we buy the individual products. Because this is a key from a different table,(the suppliers table) it is referred to a as foreign key. When using a SELECT statement to retrieve data from the product table we can use this foreign key to extract the relevant supplier information from the supplier table for each product.