mysql - join

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables. This means everything you have learned so far can be applied after you’ve created this new, joined table.

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 (thesuppliers table) it is referred to as a 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:

mysql> SELECT * FROM suppliers;
+-------------+---------------+-------------------+------------------+
| supplier_id | supplier_name | supplier_address  | supplier_contact |
+-------------+---------------+-------------------+------------------+
|           1 | Microsoft     | 1 Microsoft Way   | Bill Gates       |
|           2 | Apple, Inc.   | 1 Infinite Loop   | Steve Jobs       |
|           3 | EasyTech      | 100 Beltway Drive | John Williams    |
|           4 | WildTech      | 100 Hard Drive    | Alan Wilkes      |
+-------------+---------------+-------------------+------------------+
4 rows in set (0.00 sec)

And our product table contains the following rows:

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 |
+-----------+----------------------------+-----------------------------+-------------+

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:

+----------------------------+---------------+
| 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      |
+----------------------------+---------------+

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 or more tables based on comparisons 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);

For example, to extract the product name and supplier name for each row in our product table we would use the following command:

SELECT prod_name, supplier_name, supplier_address FROM product, suppliers WHERE (product.supplier_id = suppliers.supplier_id);

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 specifying the table name followed by a dot (.) and then the column name.

The result of the above command is to produces a list of products and the name and address of the supplier for each product:

+--------------------------+---------------+-------------------+
| prod_name                | supplier_name | supplier_address  |
+--------------------------+---------------+-------------------+
| Microsoft 10-20 Keyboard | Microsoft     | 1 Microsoft Way   |
| Apple iPhone 8Gb         | Apple, Inc.   | 1 Infinite 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)

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:

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 Infinite Loop   |
+----------------------------+---------------+-------------------+

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 NULL values for the supplier columns since no such supplier exists:

+----------------------------+---------------+-------------------+
| 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 Infinite Loop   |
| Moto Razr                  | NULL          | NULL              |
+----------------------------+---------------+-------------------+

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 ourproduct table:

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 Infinite 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 |
+--------------------------+-----------------+------------------------+

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:

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)

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:

SELECT prod_name, supplier_name, supplier_address FROM product 
LEFT JOIN suppliers USING (supplier_id) WHERE supplier_name='Microsoft';

Resulting in the following output:

+--------------------------+---------------+------------------+
| prod_name                | supplier_name | supplier_address |
+--------------------------+---------------+------------------+
| Microsoft 10-20 Keyboard | Microsoft     | 1 Microsoft Way  |
+--------------------------+---------------+------------------+
1 row in set (0.00 sec)

출처 - http://webduos.com/mysql-join-syntax/#.UIC1-W8xqAg

===================================================================================

MySQL에서 FULL OUTER JOIN하기

2009/05/13 00:54 Database/MySQL

MySQL에서 나름대로 많은 작업을 해왔지만 MS SQL에서 사용하던 FULL OUTER JOIN이 안된다는 것을 오늘에서야 알았다. 당황스러웠지만 침착하게(?) FULL OUTER JOIN 기능을 사용할 수 있는 방법에 관해서 트릭을 찾아보면서 고민을 좀 하였다.

그러는 중 내가 선택한 방법은 {LEFT | LIGHT} OUTER JOIN과 UNION의 조합이다. UNION을 이용할 때 두 질의의 결과값들이 중복되어 있을 경우, 이는 하나로 표시된다. 다음의 예제를 통해서 살펴보도록 하자.

test1과 test2 테이블을 만들고 다음과 같이 데이터를 넣어보자.

[test1] 테이블

name   age
-------------------
lin        22

sean    111

[test2] 테이블

name   age
-------------------
sean     28
ted        1

다음 질의를 실행하면 [test_result] 테이블이 자동으로 생성되고 그 결과가 들어가 있는 것을 확인할 수 있다.

SELECT t1.name as name, IFNULL(t1.age, 0) AS age1, IFNULL(t2.age, 0) AS age2
INTO test_result
FROM test1 t1
          LEFT OUTER JOIN test2 t2 ON t1.name = t2.name
UNION
SELECT t2.name, IFNULL(t1.age, 0), IFNULL(t2.age, 0)
FROM test1 t1
          RIGHT OUTER JOIN test2 t2 ON t1.name = t2.name

[test_result] 테이블

name   age1   age2
------------------------------
lin        22       0
sean    111      28
ted       0        1

출처 - http://creaplz.tistory.com/97