34,333
edits
Changes
→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.
Let's begin by looking at our two tables, the ''supplier'' table and the ''product''. First, the ''supplier'' table contains the following rows:
<pre>
mysql> SELECT * FROM suppliers;
+-------------+---------------+-------------------+------------------+
| supplier_id | supplier_name | supplier_address | supplier_contact |
+-------------+---------------+-------------------+------------------+
| 1 | Microsoft | 1 Microsoft Way | Bill Gates |
| 2 | Apple, Inc. | 1 Infinate Loop | Steve Jobs |
| 3 | EasyTech | 100 Beltway Drive | John Williams |
| 4 | WildTech | 100 Hard Drive | Alan Wilkes |
+-------------+---------------+-------------------+------------------+
4 rows in set (0.00 sec)
</pre>
And our ''product'' table contains the following rows:
<pre>
SELECT * FROM product;
+-----------+----------------------------+-----------------------------+-------------+
| prod_code | prod_name | prod_desc | supplier_id |
+-----------+----------------------------+-----------------------------+-------------+
| 1 | CD-RW Model 4543 | CD Writer | 3 |
| 2 | EasyTech Mouse 7632 | Cordless Mouse | 3 |
| 3 | WildTech 250Gb 1700 | SATA Disk Drive | 4 |
| 4 | Microsoft 10-20 Keyboard | Ergonomic Keyboard | 1 |
| 5 | Apple iPhone 8Gb | Smart Phone | 2 |
+-----------+----------------------------+-----------------------------+-------------+
</pre>
As you can see from the above output, the product rows contain a column which holds the supplier_id of the supplier from which the product is obtained. Now that we have the tables created, we can begin to perform some joins.
== Performing a Cross-Join ==
Joining tables involves combining rows from two tables. The most basic of join types is the ''cross-join''. The cross-join simply assigns a row from one table to every row of the second table. This is of little or no use in real terms, but for the purposes of completeness, the syntax for a cross-join is as follows:
SELECT ''column_names'' FROM ''table1'', ''table2'';
For example, if we were to perform the following command on our sample table we would get the following output:
<pre>
+----------------------------+---------------+
| prod_name | supplier_name |
+----------------------------+---------------+
| CD-RW Model 4543 | Microsoft |
| CD-RW Model 4543 | Apple, Inc. |
| CD-RW Model 4543 | EasyTech |
| CD-RW Model 4543 | WildTech |
| EasyTech Mouse 7632 | Microsoft |
| EasyTech Mouse 7632 | Apple, Inc. |
| EasyTech Mouse 7632 | EasyTech |
| EasyTech Mouse 7632 | WildTech |
| WildTech 250Gb 1700 | Microsoft |
| WildTech 250Gb 1700 | Apple, Inc. |
| WildTech 250Gb 1700 | EasyTech |
| WildTech 250Gb 1700 | WildTech |
| Microsoft 10-20 Keyboard | Microsoft |
| Microsoft 10-20 Keyboard | Apple, Inc. |
| Microsoft 10-20 Keyboard | EasyTech |
| Microsoft 10-20 Keyboard | WildTech |
| Apple iPhone 8Gb | Microsoft |
| Apple iPhone 8Gb | Apple, Inc. |
| Apple iPhone 8Gb | EasyTech |
| Apple iPhone 8Gb | WildTech |
+----------------------------+---------------+
</pre>
As you can see, it is hard to imagine how this could of use in many situations. A much more useful type of join is the ''Equi-Join'' or ''Inner Join''.
== Equi-Join (aka the Inner Join) ==
The Equi-Join joins rows from two tables based on comparisons betweena specific column in each table. The syntax for this approach is as follows:
SELECT ''column_names'' FROM ''table1'', ''table2'' WHERE (''table1.column'' = ''table2.column'');
For example, to extract the product name and supplier name for each row in our product table we would use the following command:
<pre>
SELECT prod_name, supplier_name, supplier_address FROM product, suppliers WHERE (product.supplier_id = suppliers.supplier_id);
</pre>
Note that we have to use what is known as the ''fully qualified name'' for the supplier_id column in each table since both tables contain a ''supplier_id''. A fully qualified column name is defined by specifyin gthe table name followed by a dot (.) and then the column name.
The result of the above command is to produces a lists of products and the name and address of the supplier for each product:
<pre>
+--------------------------+---------------+-------------------+
| prod_name | supplier_name | supplier_address |
+--------------------------+---------------+-------------------+
| Microsoft 10-20 Keyboard | Microsoft | 1 Microsoft Way |
| Apple iPhone 8Gb | Apple, Inc. | 1 Infinate Loop |
| CD-RW Model 4543 | EasyTech | 100 Beltway Drive |
| EasyTech Mouse 7632 | EasyTech | 100 Beltway Drive |
| WildTech 250Gb 1700 | WildTech | 100 Hard Drive |
+--------------------------+---------------+-------------------+
5 rows in set (0.00 sec)
</pre>
