SQL语句LEFT JOIN

SQL LEFT JOIN 关键字

LEFT JOIN关键字会从左表(TABLE_NAME1)那里返回所有的行，即使右表(TABLE_NAME2)中没有匹配的行。
LEFT JOIN 关键字语法

SELECT COLUMN_NAME(S) FROM TABLE_NAME1 LEFT JOIN TABLE_NAME2 ON TABLE_NAME1.COLUMN_NAME=TABLE_NAME2.COLUMN_NAME

注释:在某些数据库中,LEFT JOIN 称为 LEFT OUTER JOIN。

DUCK表:

DUCK_ID	NAME	YEAR
HK234	DUCK01	2016
DK244	DUCK02	2016
KK134	DUCK03	2016

MEAT表:

MEAT_ID	KIND	PRODUCTION_YEAR	DUCK_ID
LO23	DUCK	2016	KK134
LO93	DUCK	2016	DK244
LO03	DUCK	2016	HK234
LO66	DUCK	2016	CO134

---

左连接(LEFT JOIN)实例
列出所有MEAT和MEAT的来源

SELECT M.MEAT_ID,D.DUCK_ID,M.KIND,M.PRODUCTION_YEAR,D.YEAR FROM MEAT M LEFT JOIN DUCK D ON M.DUCK_ID=D.DUCK_ID ORDER BY M.MEAT_ID;

结果:

MEAT_ID	DUCK_ID	KIND	PRODUCTION_YEAR	YEAR
LO03	HK234	DUCK	2016	2015
LO23	KK134	DUCK	2016	2016
LO66		DUCK	2016
LO93	DK244	DUCK	2016	2016

LEFT JOIN关键字会从左表（MEAT）那里返回所有行，即使右表（DUCK）中没有匹配行。