mysql join操作

join的類型

1．  內連接：將兩個表中存在連接關係的欄位符合連接關係的那些記錄形成記錄集的連接。

2．  外連接：分為外左連接和外右連接。

 

案例背景

create table java (name varchar(255));insert into java values ('java1'),('java2'),('blue'); create table mysql (name varchar(255));insert into mysql values ('mysql1'),('mysql2'),('blue'); 

 

內連接

select * from java,mysql where java.name=mysql.name;SELECT * FROM java  JOIN mysql ON java.name=mysql.name;SELECT * FROM java INNER JOIN mysql ON java.name=mysql.name;SELECT * FROM java CROSS JOIN mysql ON java.name=mysql.name;SELECT * FROM java  STRAIGHT_JOIN  mysql ON java.name=mysql.name;

這四個語句都是內連接，返回結果都是

+------+------+| name | name |+------+------+| blue | blue |+------+------+

• table_reference條目中的每個逗號都看作相當於一個內部聯合
• 預設的JOIN都是INNER JOIN
• CROSS JOIN從文法上說與INNER JOIN等同
• STRAIGHT_JOIN與JOIN相同。除了有一點不一樣，左表會在右表之前被讀取。STRAIGH_JOIN可以被用於這樣的情況，即聯合最佳化符以錯誤的順序排列表。

內連接的文法如下:

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

 

外連接

左連接

SELECT * FROM java LEFT JOIN mysql ON java.name=mysql.name;

結果是

+-------+------+| name  | name |+-------+------+| java1 | NULL || java2 | NULL || blue  | blue |+-------+------+

所以從上面結果看出，因為java表中的java1和java2記錄的name沒有在mysql表中有對應name，因此為空白，但java 所有的列欄仍有java1和java2記錄，mysql表所有列欄為NULL。而剩下的blue的那條記錄就是java表和mysql表內串連的結果。

 

如果對於在LEFT JOIN中的ON或USING部分中的右表沒有匹配的記錄，則所有列被設定為NULL的一個行被用於右表。如果一個表在其它表中沒有對應部分，您可以使用這種方法在這種表中尋找記錄：

SELECT * FROM java LEFT JOIN mysql ON java.name=mysql.name WHERE mysql.name IS NULL; 

該sql找出在java的人，但是不在mysql裡的人，這裡明顯是人員'java1'和'java2'符合要求。

 

右連接

SELECT * FROM java RIGHT JOIN mysql ON java.name=mysql.name;

返回結果是

+------+--------+| name | name   |+------+--------+| NULL | mysql1 || NULL | mysql2 || blue | blue   |+------+--------+

右連接和左聯結結果類似，只是這次是mysql表儲存所有的結果集。

外連接的文法

join_table:| 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

 

USING(column_list)子句

用於為一系列的列進行命名，這些列必須同時在兩個表中存在

SELECT java.*,mysql.* FROM java LEFT JOIN mysql USING (name); 

結果返回

+-------+------+| name  | name |+-------+------+| java1 | NULL || java2 | NULL || blue  | blue |+-------+------+

 

連接的運算順序

SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c);--相當於SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

括弧對於join的順序的影響

SELECT t1.id,t2.id,t3.id FROM t1,t2 LEFT JOIN t3 ON (t3.id=t1.id) WHERE t1.id=t2.id;--實際上這麼執行SELECT t1.id,t2.id,t3.id FROM t1,(  t2 LEFT JOIN t3 ON (t3.id=t1.id)  ) WHERE t1.id=t2.id;--應該這麼寫SELECT t1.id,t2.id,t3.id FROM (t1,t2) LEFT JOIN t3 ON (t3.id=t1.id) WHERE t1.id=t2.id;

在這裡括弧是相當重要的，因此以後在寫這樣的查詢的時候我們不要忘記了多寫幾個括弧，至少這樣能避免很多錯誤