mysql join表串連

標籤：串連   blog   author   tle   set   語句   .com   learn   where   

1.表串連，就是將兩個表合并起來，被合并的表的記錄要通過中間欄位，一一匹配起來左邊的表的記錄，形成一張臨時的合并的表，並且每條記錄的值都是兩張表一一準確對應的執行個體

嘗試以下執行個體：

[email protected]# mysql -u root -p password;Enter password:*******mysql> use RUNOOB;Database changedmysql> SELECT * FROM tcount_tbl;+-----------------+----------------+| runoob_author | runoob_count |+-----------------+----------------+| mahran          |             20 || mahnaz          |           NULL || Jen             |           NULL || Gill            |             20 || John Poul       |              1 || Sanjay          |              1 |+-----------------+----------------+6 rows in set (0.01 sec)mysql> SELECT * from runoob_tbl;+-------------+----------------+-----------------+-----------------+| runoob_id | runoob_title | runoob_author | submission_date |+-------------+----------------+-----------------+-----------------+|           1 | Learn PHP      | John Poul       | 2007-05-24      ||           2 | Learn MySQL    | Abdul S         | 2007-05-24      ||           3 | JAVA Tutorial  | Sanjay          | 2007-05-06      |+-------------+----------------+-----------------+-----------------+3 rows in set (0.00 sec)mysql>

接下來我們就使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一樣)來串連以上兩張表來讀取runoob_tbl表中所有runoob_author欄位在tcount_tbl表對應的runoob_count欄位值：

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;+-----------+---------------+--------------+| runoob_id | runoob_author | runoob_count |+-----------+---------------+--------------+|         1 | John Poul     |            1 ||         3 | Sanjay        |            1 |+-----------+---------------+--------------+2 rows in set (0.00 sec)

以上 SQL 陳述式等價於：

mysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a, tcount_tbl b WHERE a.runoob_author = b.runoob_author;+-------------+-----------------+----------------+| runoob_id | runoob_author | runoob_count |+-------------+-----------------+----------------+|           1 | John Poul       |              1 ||           3 | Sanjay          |              1 |+-------------+-----------------+----------------+2 rows in set (0.01 sec)mysql>

 

MySQL LEFT JOIN

MySQL left join 與 join 有所不同。 MySQL LEFT JOIN 會讀取左邊資料表的全部資料，即便右邊表無對應資料。

執行個體

嘗試以下執行個體，以 runoob_tbl 為左表，tcount_tbl 為右表，理解MySQL LEFT JOIN的應用：

[email protected]# mysql -u root -p password;Enter password:*******mysql> use RUNOOB;Database changedmysql> SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;+-------------+-----------------+----------------+| runoob_id | runoob_author | runoob_count |+-------------+-----------------+----------------+|           1 | John Poul       |              1 ||           2 | Abdul S         |           NULL ||           3 | Sanjay          |              1 |+-------------+-----------------+----------------+3 rows in set (0.02 sec)

 

 

mysql join表串連