学步园

连接：

http://www.w3school.com.cn/sql/sql_join.asp

http://www.cnblogs.com/cy163/archive/2008/10/16/1312920.html

http://mmscau.iteye.com/blog/1024182

http://www.cnblogs.com/elock/archive/2009/12/05/1617706.html

1.inner join内连接
SELECT * FROM t_institution i 
	INNER JOIN t_teller t ON i.inst_no = t.inst_no 
	WHERE i.inst_no = "5801" 	
等价于早期的连接语法 
SELECT * FROM t_institution i, t_teller t 
	WHERE i.inst_no = t.inst_no AND i.inst_no = "5801" 

2.left outer join
SELECT * FROM t_institution i 
	LEFT OUTER JOIN t_teller t ON i.inst_no = t.inst_no 
	AND i.inst_no = “5801” 
	
SELECT * FROM t_institution i 
	LEFT OUTER JOIN t_teller t ON i.inst_no = t.inst_no 
	WHERE i.inst_no = “5801” 

3.right outer join
SELECT * FROM t_institution i 
	RIGHT OUTER JOIN t_teller t ON i.inst_no = t.inst_no 

4.full join
SELECT * FROM t_institution i 
	FULL OUTER JOIN t_teller t ON i.inst_no = t.inst_no 

eg:	
SELECT * FROM t_ums_user_role ur JOIN t_ums_user u ON ur.`USERS_ID` = u.`ID`
	WHERE user_id = 100001
--等价于：	
SELECT * FROM t_ums_user_role ur, t_ums_user u 
	WHERE ur.`USERS_ID` = u.`ID` AND user_id = 100002