34,333
edits
Changes
→Equi-Join (aka the Inner Join)
== Equi-Join (aka the Inner Join) ==
The Equi-Join joins rows from two or more tables based on comparisons betweena between a specific column in each table. The syntax for this approach is as follows:
SELECT ''column_names'' FROM ''table1'', ''table2'' WHERE (''table1.column'' = ''table2.column'');
</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 specifying the 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:
+--------------------------+---------------+-------------------+
5 rows in set (0.00 sec)
</pre>
== Performing a Left Join or a Right Join==
Another way to join tables is use a ''LEFT JOIN'' in the select statement.The LEFT JOIN causes the tables to be joined before any WHERE clause is used. The syntax for this type of join is:
SELECT ''column names'' FROM ''table1'' LEFT JOIN ''table2'' ON (''table1.column'' = ''table2.column'';
Therefore, we can perform a LEFT JOIN that gives us the same result as our Equi-Join:
<pre>
SELECT prod_name, supplier_name, supplier_address FROM product LEFT JOIN suppliers
ON (product.supplier_id = suppliers.supplier_id);
+----------------------------+---------------+-------------------+
| prod_name | supplier_name | supplier_address |
+----------------------------+---------------+-------------------+
| CD-RW Model 4543 | EasyTech | 100 Beltway Drive |
| EasyTech Mouse 7632 | EasyTech | 100 Beltway Drive |
| WildTech 250Gb 1700 | WildTech | 100 Hard Drive |
| Microsoft 10-20 Keyboard | Microsoft | 1 Microsoft Way |
| Apple iPhone 8Gb | Apple, Inc. | 1 Infinate Loop |
+----------------------------+---------------+-------------------+
</pre>
One key different with the LEFT JOIN is that it will also list rows from the first table for which there is no match in the second table. For example, suppose we have product in our ''product'' table for which there is no matching supplier in the ''supplier'' table. When we run our SELECT statement the row will still be displayed, but with a NULL values for the supplier columns since no such supplier exists:
<pre>
+----------------------------+---------------+-------------------+
| prod_name | supplier_name | supplier_address |
+----------------------------+---------------+-------------------+
| CD-RW Model 4543 | EasyTech | 100 Beltway Drive |
| EasyTech Mouse 7632 | EasyTech | 100 Beltway Drive |
| WildTech 250Gb 1700 | WildTech | 100 Hard Drive |
| Microsoft 10-20 Keyboard | Microsoft | 1 Microsoft Way |
| Apple iPhone 8Gb | Apple, Inc. | 1 Infinate Loop |
| Moto Razr | NULL | NULL |
+----------------------------+---------------+-------------------+
</pre>
The opposite effect can be achieved using a RIGHT JOIN, whereby all the rows in a the second table (i.e our ''supplier'' table) will be displayed regardless of whether that supplier has any products in our ''product'' table:
<pre>
SELECT prod_name, supplier_name, supplier_address FROM product RIGHT JOIN suppliers
ON (product.supplier_id = suppliers.supplier_id);
+--------------------------+-----------------+------------------------+
| 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 |
| NULL | Hewlett Packard | 100 Printer Expressway |
+--------------------------+-----------------+------------------------+
</pre>
== Creating Joins with WHERE and USING ==
The next step is to incorporate some WHERE clauses into our LEFT and RIGHT joins. Say, for example, that we wish to list only products supplied by Microsoft:
<pre>
SELECT prod_name, supplier_name, supplier_address FROM product RIGHT JOIN suppliers
ON (product.supplier_id = suppliers.supplier_id) WHERE supplier_name='Microsoft';
+--------------------------+---------------+------------------+
| prod_name | supplier_name | supplier_address |
+--------------------------+---------------+------------------+
| Microsoft 10-20 Keyboard | Microsoft | 1 Microsoft Way |
+--------------------------+---------------+------------------+
1 row in set (0.00 sec)
</pre>
The ''USING'' clause further simplifies the tasks of creating joins. The purpose of USING is to avoid the use of fully qualified names (such as product.supplier_id and supplier.supplier_id) when reference columns that reside in different tables but have the names. For example, to perform the same join above based on the values of product.supplier_id and supplier.supplier_id we can simply use the following syntax:
<pre>
SELECT prod_name, supplier_name, supplier_address FROM product
LEFT JOIN suppliers USING (supplier_id) WHERE supplier_name='Microsoft';
</pre>
Resulting in the following output:
<pre>
+--------------------------+---------------+------------------+
| prod_name | supplier_name | supplier_address |
+--------------------------+---------------+------------------+
| Microsoft 10-20 Keyboard | Microsoft | 1 Microsoft Way |
+--------------------------+---------------+------------------+
1 row in set (0.00 sec)
</pre>