SQL连接查询语句

来源：互联网发布：java项目经验范文编辑：程序博客网时间：2024/06/10 16:21

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