mysql左聯右聯內聯

標籤：

在MySQL中由於效能的關係，常常要將子查詢（Sub-Queries）用串連（join）來卻而代之，能夠更好地使用表中索引提高查詢效率。

下面介紹各種join的使用，先：

 

 

我們MySQL常用的為左串連（left join）、右串連（right join）和內串連（inner join）其他，餘下的full join我們MySQL不支援，可用用左右串連和UNION做替代（下面舉例介紹）。

 

1、先建立測試表兩張：

CREATE TABLE  a (

aID int( 1 ) AUTO_INCREMENT PRIMARY KEY , 

aNum char( 20 ) 

) ;

CREATE TABLE b( 

bID int( 1 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , 

bName char( 20 )  

) ;

2、插入測試資料：

INSERT INTO a 

VALUES ( 1, ‘a20050111‘ ) , ( 2, ‘a20050112‘ ) , ( 3, ‘a20050113‘ ) , ( 4, ‘a20050114‘ ) , ( 5, ‘a20050115‘ ) ; 

INSERT INTO b 

VALUES ( 1, ‘2006032401‘ ) , ( 2, ‘2006032402‘ ) , ( 3, ‘2006032403‘ ) , ( 4, ‘2006032404‘ ) , ( 8, ‘2006032408‘ ) ; 

 

 

兩表資料如下：

 

 

 

mysql> select * from a;

+-----+-----------+

| aID | aNum      |

+-----+-----------+

|   1 | a20050111 |

|   2 | a20050112 |

|   3 | a20050113 |

|   4 | a20050114 |

|   5 | a20050115 |

+-----+-----------+

 

 

mysql> select * from b;

+-----+------------+

| bID | bName      |

+-----+------------+

|   1 | 2006032401 |

|   2 | 2006032402 |

|   3 | 2006032403 |

|   4 | 2006032404 |

|   8 | 2006032408 |

+-----+------------+

 

 

實驗如下: 

1.left join(左聯結) 

 

sql語句如下:  

SELECT * FROM a 

LEFT JOIN  b  

ON a.aID =b.bID 

 

結果如下: 

aID        aNum                   bID           bName 

1            a20050111         1              2006032401 

2            a20050112         2              2006032402 

3            a20050113         3              2006032403 

4            a20050114         4              2006032404 

5            a20050115         NULL       NULL 

（所影響的行數為 5 行） 

 

結果說明: 

        left join是以A表的記錄為基礎的,A可以看成左表,B可以看成右表,left join是以左表為準的. 

換句話說,左表(A)的記錄將會全部表示出來,而右表(B)只會顯示符合搜尋條件的記錄(例子中為: A.aID = B.bID). 

B表記錄不足的地方均為NULL. 

 

2.right join(右聯結) 

 

sql語句如下:  

SELECT  * FROM a 

RIGHT JOING b  

ON a.aID = b.bID 

 

結果如下: 

aID        aNum                   bID           bName 

1            a20050111         1              2006032401 

2            a20050112         2              2006032402 

3            a20050113         3              2006032403 

4            a20050114         4              2006032404 

NULL    NULL                   8              2006032408 

（所影響的行數為 5 行） 

 

結果說明: 

        仔細觀察一下,就會發現,和left join的結果剛好相反,這次是以右表(B)為基礎的,A表不足的地方用NULL填充. 

 

3.inner join(相等聯結或內聯結) 

 

sql語句如下:  

SELECT * FROM  a 

INNER JOIN  b 

ON a.aID =b.bID 

 

等同於以下SQL句: 

SELECT *  

FROM a,b 

WHERE a.aID = b.bID 

 

結果如下: 

aID        aNum                   bID           bName 

1            a20050111         1              2006032401 

2            a20050112         2              2006032402 

3            a20050113         3              2006032403 

4            a20050114         4              2006032404 

 

結果說明: 

        很明顯,這裡只顯示出了 A.aID = B.bID的記錄.這說明inner join並不以誰為基礎,它只顯示合格記錄等同於直接where條件的兩表聯查. 

 

4.full join(全聯結)

MySQL不支援，需要用左右串連和UNION方法替代，，效果為取得a表和b表資料的並集。

 

mysql> select * from a left join b on a.aID = b.bID union all select * from a right join b on a.aID = b.bID;

+------+-----------+------+------------+

| aID  | aNum      | bID  | bName      |

+------+-----------+------+------------+

|    1 | a20050111 |    1 | 2006032401 |

|    2 | a20050112 |    2 | 2006032402 |

|    3 | a20050113 |    3 | 2006032403 |

|    4 | a20050114 |    4 | 2006032404 |

|    5 | a20050115 | NULL | NULL       |

|    1 | a20050111 |    1 | 2006032401 |

|    2 | a20050112 |    2 | 2006032402 |

|    3 | a20050113 |    3 | 2006032403 |

|    4 | a20050114 |    4 | 2006032404 |

| NULL | NULL      |    8 | 2006032408 |

+------+-----------+------+------------+

 

如果想求得a表和b表的差集，中右下角所示，則需要分別加條件。

 

mysql> select * from a left join b on a.aID = b.bID  where b.bID is null  union all select * from a right join b on a.aID = b.bID where a.aID is null ;

+------+-----------+------+------------+

| aID  | aNum      | bID  | bName      |

+------+-----------+------+------------+

|    5 | a20050115 | NULL | NULL       |

| NULL | NULL      |    8 | 2006032408 |

+------+-----------+------+------------+

mysql左聯右聯內聯