PHP 與 MySQL 入門學習指南

1. PHP與MySQL入門學習指南 第 30 章 資料表結合 凱文瑞克 著

2. MySQL 4.0 支援的 Join 語法 • table_reference, table_reference • table_reference [CROSS] JOIN table_reference • table_reference INNER JOIN table_reference join_condition • table_reference STRAIGHT_JOIN table_reference • table_reference LEFT [OUTER] JOIN table_reference join_condition • table_reference LEFT [OUTER] JOIN table_reference • table_reference NATURAL [LEFT [OUTER]] JOIN table_reference • {table_reference LEFT OUTER JOIN table_reference ON conditional_expr } • table_reference RIGHT [OUTER] JOIN table_reference join_condition • table_reference RIGHT [OUTER] JOIN table_reference • table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

3. JOIN 與 WHERE (1) • SELECT first_name, order_id from customers AS C, orders AS O WHERE C.id=O.id; • SELECT first_name, order_id from customers AS C INNER JOIN orders AS O ON C.id=O.id;

4. 執行結果

5. JOIN 與 WHERE (2) SELECT column1,column2,.. FROM 【table1 INNER JOIN table2】ON 結合準則 WHERE condition_criteria • 舊式【WHERE】語法 SELECT C.first_name, C.last_Name, O.date, O.order_id FROM customers AS C, orders AS O WHERE C.id=O.id AND last_name = 'ponds'; • 新式【JOIN】 語法 SELECT C.first_name, C.last_name, O.date, O.order_id FROM customers AS C INNER JOIN orders AS O ON C.id = O.id WHERE last_name = 'ponds';

6. 執行結果

7. 各類的 JOIN(1) • 笛卡兒結合[CROSS JOIN] • 舊式[WHERE]語法 SELECT * FROM customers AS C,orders AS O; • 新式[JOIN]語法 SELECT * FROM customers AS C CROSS JOIN orders AS O;

8. 執行結果

9. 各類的 JOIN(2) • 左結合 [LEFT JOIN] SELECT * From customers AS C LEFT JOIN orders AS O ON C.id = O.id;

10. 執行結果

11. 各類的 JOIN(3) • 右結合 [RIGHT JOIN] • SELECT * FROM customers AS C RIGHT JOIN orders AS O ON C.id=O.id; • SELECT * FROM orders AS O LEFT JOIN customers AS C ON C.id=O.id;

12. 執行結果

13. 各類的 JOIN(4) • 自然左結合 [NATURAL LEFT JOIN] • SELECT * From customers AS C LEFT JOIN orders AS O ON C.id = O.id; • SELECT * FORM customers AS C NATURAL LEFT JOIN orders AS O;

14. 執行結果