MySQL中的各種JOIN(CROSS JOIN, INNER JOIN, LEFT [OUTER] JOIN)

  MySQL中的各種JOIN

1. 笛卡爾積(交叉串連)
在MySQL中可以為CROSS JOIN或者省略CROSS即JOIN，或者使用','
如
SELECT * FROM table1 CROSS JOIN table2
SELECT * FROM table1 JOIN table2
SELECT * FROM table1,table2

由於其返回的結果為被串連的兩個資料表的乘積，因此當有WHERE, ON或USING條件的時候一般不建議使用，因為當資料表項目太多的時候，會非常慢。
一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN

2. 內串連INNER JOIN
在MySQL中把INNER JOIN叫做等值串連，即需要指定等值串連條件
在MySQL中CROSS和INNER JOIN被劃分在一起，不明白。
參看MySQL協助手冊
http://dev.mysql.com/doc/refman/5.0/en/join.html
join_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_condition]

3. MySQL中的外串連，分為左外串連和右串連，
即除了返回符合串連條件的結果之外，還要返回左表(左串連)或者右表(右串連)中不符合串連條件的結果，相對應的使用NULL對應。

a. LEFT [OUTER] JOIN
SELECT column_name FROM table1 LEFT [OUTER] JOIN table2 ON table1.column=table2.column
除了返回符合串連條件的結果之外，還需要顯示左表中不符合串連條件的資料列，相對應使用NULL對應

b. RIGHT [OUTER] JOIN
SELECT column_name FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column=table2.column
RIGHT與LEFT JOIN相似不同的僅僅是除了顯示符合串連條件的結果之外，還需要顯示右表中不符合串連條件的資料列，相應使用NULL對應

--------------------------------------------
添加顯示條件WHERE, ON, USING
1. WHERE子句
2. ON
3. USING子句，如果串連的兩個表串連條件的兩個列具有相同的名字的話可以使用USING
例如
SELECT <column_name> FROM <table1> LEFT JOIN <table2> USING (<column_name>)

串連多餘兩個表的情況
舉例：
mysql> SELECT  artists.Artist, cds.title, genres.genre
    -> FROM cds
    -> LEFT JOIN genres
    -> ON cds.genreID = genres.genreID
    -> LEFT JOIN artists
    -> ON cds.artistID = artists.artistID;
或者
mysql> SELECT artists.Artist, cds.title, genres.genre
    -> FROM cds
    -> LEFT JOIN genres
    -> ON cds.genreID = genres.genreID
    -> LEFT JOIN artists
    -> ON cds.artistID = artists.artistID
    -> WHERE (genres.genre = 'Pop');
--------------------------------------------

另外需要注意的地方

在MySQL中涉及到多表查詢的時候，需要根據查詢的情況，想好使用哪種串連方式效率更高。
1. 交叉串連(笛卡爾積)或者內串連
[INNER | CROSS] JOIN
2. 左外串連LEFT [OUTER] JOIN或者右外串連RIGHT [OUTER] JOIN

注意指定串連條件WHERE, ON，USING.

--------------------------------------------
看懂MySQL手冊定義的MySQL各種JOIN的用法：
//看懂如下的定義方式

table_references:
table_reference [, 
table_reference] ...

//不同的JOIN EXPRESSION之間使用','分割
A table reference is also known as a join expression.


table_reference:
table_factor
  | join_table


//每個JOIN EXPRESSION由資料表table_factor以及JOIN運算式構成join_table 

table_factor:

tbl_name [[AS] alias] [index_hint)]
  | ( table_references )
  | { OJ 
table_reference LEFT OUTER JOIN table_reference
        ON conditional_expr }


//資料表table_factor，注意其遞迴定義的table_references


join_table:
table_reference [INNER | CROSS] JOIN 
table_factor [join_condition]
  | table_reference STRAIGHT_JOIN 
table_factor
  | table_reference STRAIGHT_JOIN table_factor ON condition
  | 
table_reference LEFT [OUTER] JOIN table_reference join_condition
  | 
table_reference NATURAL [LEFT [OUTER]] JOIN table_factor
  | table_reference RIGHT [OUTER] JOIN 
table_reference join_condition
  | table_reference NATURAL [RIGHT [OUTER]] JOIN table_factor


//資料表的串連運算式join_table 

join_condition:
    ON conditional_expr

  | USING (column_list)

//串連運算式的串連條件定義使用ON或者USING 

index_hint:
    USE {INDEX|KEY} [FOR JOIN] (index_list
)
  | IGNORE {INDEX|KEY} [FOR JOIN] (index_list)
  | FORCE {INDEX|KEY} [FOR JOIN] (index_list)


index_list:
index_name [, index_name] ...

MySQL手冊中提到的JOIN需要注意的地方：

1.
In MySQL, CROSS JOIN is a syntactic equivalent to INNER JOIN (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.
手冊中提到
標準SQL中CROSS JOIN交叉串連(笛卡爾積)和內串連INNER JOIN不同，但是MySQL中兩者是相同的，即有[CROSS | INNER] JOIN，兩者可以互相替代，而且可以只使用JOIN

2. A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:

SELECT t1.name, t2.salary
  FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name; 
可以對資料表使用別名

3. ,運算子
例如
SELECT * FROM table1,table2 
由於在MySQL中INNER JOIN與CROSS JOIN相同，INNER JOIN和 , 在MySQL也相同，都是產生兩個表的笛卡爾積Cartesian Product
(等於兩個表格的行數乘積)

但是,號的優先順序要低於INNER JOIN, CROSS JOIN, LEFT JOIN

因此
If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur.

4. 什麼時候使用ON，什麼時候使用WHERE
ON應該使用者資料表串連的時候指定串連條件；

WHERE用於使用者限制所選取的列

例如ON a.column=b.column
WHERE a.column='hello'

5. 可以使用LEFT JOIN查看，兩個串連的表中，不符合串連條件的部分，因為不合格部分LEFT JOIN之後會顯示為NULL 
 If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table:


SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

This example finds all rows in left_tbl with an id value that is not present in right_tbl (that is, all rows in left_tbl with no corresponding row in right_tbl). This assumes that right_tbl.id is declared NOT NULL. 


6.
當別串連的表指定串連條件的列舉有相同的名稱的時候，不需要
ON a.column=b.column不同的時候才使用ON a.column_a=b.column_b
可以使用USING (column)
當然也可以使用多個USING (c1,c2,c3)

The USING(column_list) clause names a list of columns that must exist in both tables. If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:


a LEFT JOIN b USING (c1,c2,c3)

7.
其他的：
#
The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.

#
RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.
#
The { OJ ... LEFT OUTER JOIN ...} syntax shown in the join syntax description exists only for compatibility with ODBC. The curly braces in the syntax should be written literally; they are not metasyntax as used elsewhere in syntax descriptions.

#
STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. This can be used for those (few) cases for which the join optimizer puts the tables in the wrong order.