1.left join
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.
2.right join
right join(右联接)和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
3.inner join
inner join(相等联接或内联接) ,inner join并不以谁为基础,它只显示符合条件的记录.
4.示例
(a). SELECT * FROM a LEFT JOIN b ON a.aID =b.bID
(b). SELECT * FROM a RIGHT JOING b ON a.aID = b.bID
(c). SELECT * FROM a INNER JOIN b ON a.aID =b.bID
5.一个复杂一点的例子
select TOP 30 T_BREAK_REFS.id,T_CATALOGS.CATALOG_ID,T_HOSTS.HOST_NAME,T_USERS.USER_NAME,
T_BREAK_REFS.BREAK_TAG,
T_BREAK_REFS.BREAK_TIME,T_BREAK_EVENTS.EVENT_CONTENT,T_BREAK_REFS.MANAGER_NAME,
T_BREAK_REFS.START_TIME,T_BREAK_REFS.END_TIME,T_BREAK_REFS.NOTE_ID
FROM T_BREAK_REFS
left join T_CATALOGS ON T_CATALOGS.CATALOG_ID = T_BREAK_REFS.CATALOG_ID
left join T_HOSTS ON T_CATALOGS.HOST_ID = T_HOSTS.HOST_ID
left join T_USERS ON T_CATALOGS.USER_ID = T_USERS.USER_ID
left join T_BREAK_EVENTS ON T_BREAK_REFS.id = T_BREAK_EVENTS.REFS_ID
WHERE T_BREAK_REFS.BREAK_TIME
BETWEEN '2013-06-27 15:00:46' AND '2013-06-29 17:45:46'
ORDER BY OPERATION_TIME DESC