JOIN之完全用法
外联接。外联接可以是左向外联接、右向外联接或完整外部联接。在 FROM 子句中指定外联接时，可以由下列几组关键字中的一组指定：
LEFT JOIN 或 LEFT OUTER JOIN。
左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
RIGHT JOIN 或 RIGHT OUTER JOIN。
右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
FULL JOIN 或 FULL OUTER JOIN。
完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回 FROM 子句中提到的至少一个表或视图的所有行，只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左向外联接引用的左表的所有行，以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。
通俗的讲：
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif A left join B 的连接的记录数与A表的记录数同
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif A right join B 的连接的记录数与B表的记录数同
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif A left join B 等价B right join A
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif table A:
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif Field_K, Field_A
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 1 a
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 3 b
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 4 c
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif table B:
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif Field_K, Field_B
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 1 x
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 2 y
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 4 z
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif select a.Field_K, a.Field_A, b.Field_K, b.Field_B
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif from a left join b on a.Field_K=b.Field_K
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif Field_K Field_A Field_K Field_B
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif ---------- ---------- ---------- ----------
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 1 a 1 x
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 3 b NULL NULL
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 4 c 4 z
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif select a.Field_K, a.Field_A, b.Field_K, b.Field_B
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif from a right join b on a.Field_K=b.Field_K
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif Field_K Field_A Field_K Field_B
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif ---------- ---------- ---------- ----------
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 1 a 1 x
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif NULL NULL 2 y
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 4 c 4 z --
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif举个例子：
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 假设ａ表和ｂ表的数据是这样的。
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif a b
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif id name id stock
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 1 a 1 15
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 2 b 2 50
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 3 c
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif select * from a inner join b on a.id=b.id
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 这个语法是连接查询中的内连接，它产生的结果是
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 两个表相匹配的记录出现在结果列表中。
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 根据上面的表，出现的结果是这样的
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif a.id name b.id stock
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 1 a 1 15
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 2 b 2 50
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif ----------------------------
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif select * from a,b where a.id=b.id
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 这个语法是内连接的另外一种写法，其执行结果与inner join 一样
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif --------------------------------
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif select * from a left/right join b on a.id=b.id
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 这个是外连接语法中的左外连接或右外连接
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 如果是左外连接的话，它将显示ａ表的所有记录，
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif select a.*,b.* from a left join b on a.id=b.id
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 查询的结果是这样的：
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif a.id name b.id stock
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 1 a 1 15
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 2 b 2 50
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 3 c null null
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif --------------------------------------------
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 如果是右外连接的话，它将显示ｂ表的所有记录，
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif select a.*,b.* from a right join b on a.id=b.id
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 查询的结果是这样的：
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif a.id name b.id stock
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 1 a 1 15
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif 2 b 2 50
页:
[1]