select 字段名稱
from 左表名稱 left join 右表名稱
on 左表字段值=右表字段值;
在一些數據庫中left join稱為left outer join
下面是一個來自http://www.w3school.com.cn/sql/sql_join_left.asp的例子:
mysql> select * from persons;
+------+----------+-----------+----------------+----------+
| Id_P | LastName | FirstName | Address | City |
+------+----------+-----------+----------------+----------+
| 1 | Adams | John | Oxford Street | London |
| 2 | Bush | George | Fifth Street | New York |
| 3 | Carter | Thomas | Changan Street | Beijing |
+------+----------+-----------+----------------+----------+
mysql> select * from orders;
+------+---------+------+
| Id_O | OrderNo | Id_P |
+------+---------+------+
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 1 |
| 4 | 24562 | 1 |
| 5 | 34764 | 65 |
+------+---------+------+
mysql> select persons.LastName,persons.FirstName,Orders.OrderNo
-> from persons left join orders on persons.Id_P=orders.Id_P
-> order by persons.LastName;
+----------+-----------+---------+
| LastName | FirstName | OrderNo |
+----------+-----------+---------+
| Adams | John | 22456 |
| Adams | John | 24562 |
| Bush | George | NULL |
| Carter | Thomas | 77895 |
| Carter | Thomas | 44678 |
+----------+-----------+---------+
其中結果集中的第三條數據 Bush George NULL
Bush George在persons表中有值和對應的Id_P(值為2),但該Id_P在orders表中確不存在
左連接的目的就是盡可能的現實persons表中多的數據,鏈接后右表即使為空就顯示為空