表连接查询INNER JOIN/OUTER JOIN/CROSS JOIN

多表查询常涉及到到表连接的问题，主要涉及到内连接：INNER JOIN，外连接：LEFT JOIN(or LEFT OUTER JOIN),RIGHT JOIN(or RIGHT OUTER JOIN),FULL JOIN和交叉连接：CROSS JOIN。INNER JOIN也叫等值链接。内连接具有排他性，而OUTER JOIN却是包容性的。

表连接进行查询都是会产生一张中间的临时表，然后再将这张临时表返回给用户。通用的SQL语句：SELECT (待查询字段名) FROM 表一 (JOIN TYPE) 表二ON (JOIN CONDITION) [其他对临时表记录进行过滤的条件]；

几种连接方式查询结果区别大致如下：

INNER JOIN:只返回两个表中连接字段相同的行（即符合条件的行）；

LEFT JOIN: 返回包括左表中的所有记录和右表中联结字段相等的记录;

RIGHT JOIN: 返回包括右表中的所有记录和左表中联结字段相等的记录;

FULL JOIN:两个连接表的所有记录；

CROSS JOIN：不带WHERE子句，返回所有数据行的笛卡尔乘积.

           

表一:TAB_EMP

ID	EMP_NAME	DEPT
1	Eric Huang	1
2	Alice	3
3	Peter	1
4	Green

 

表二：TAB_DEPT

ID	DEPT_NAME
1	研发部
2	财务部
3	国际事业部
4	综合管理部
5	销售部

 

INNER JOIN 方式：

SQL>SELECT t1.emp_name, t2.dept_name FROM tab_emp t1 INNER JOIN tab_dept t2 ON t1.dept=t2.id;

执行结果：

 

分析：可以看出采用INNER JOIN方式时，只会列出满足连接条件的记录；

 

LEFT JOIN 方式：

SQL>SELECT t1.emp_name, t2.dept_name FROM tab_emp t1 LEFT JOIN tab_dept t2 ON t1.dept=t2.id;

执行结果：

 

 

分析：可以看出采用LEFT JOIN方式时，左表中的记录和右表中联结字段相等的记录会全部列出，而右表中连接字段不相等的字段全部为null；

 

RIGHT JOIN 方式：

SQL>SELECT t1.emp_name, t2.dept_name FROM tab_emp t1 RIGHT JOIN tab_dept t2 ON t1.dept=t2.id;

执行结果：

 

 

分析：可以看出采用RIGHT JOIN方式时，返回包括右表中的所有记录和左表中联结字段相等的记录;，若左表中连接字段不相等的字段全部为null；

 

FULL JOIN 方式：

SQL>SELECT t1.emp_name, t2.dept_name FROM tab_emp t1 FULL JOIN tab_dept t2 ON t1.dept=t2.id;

执行结果：

 

 

分析：可以看出采用FULL JOIN方式时，左表的所有记录会全部返回，右表的所有记录也会全部返回，匹配的就直接列出，不匹配的某一边的记录值为null就行；

 

CROSS JOIN方式：

值得注意的是该方式语法上和其他几种有点差别，不能用on子句。

SELECT * FROM [表一] CROSS JOIN [表二] 或者

SELECT * FROM [表一], [表二]

SQL> SELECT t1.emp_name, t2.dept_name FROM tab_emp t1 CROSS JOIN tab_dept t2;

 

分析：最后返回的记录数实际上就是两张表记录数的乘积。该类型的JOIN句法不需要连接条件，一个表中的所有行分别都会连接另一个表的所有行，得到的结果被称为一个笛卡尔积。

 

            最后总结一下，实际项目开发中CROSS JOIN用得比较少，它是不能使用ON关键字的，其它都要用。一般先进行连接获取到需要的新表数据，然后使用其他子句如where子句进行过滤，group by子句进行聚合，order by子句进行排序等等操作。

采用JOIN方式多表连接查询时，如果如果用到了GROUP BY，GROUP BY 的排序顺序必须和SELECT中列出的字段顺序的一致. 这可能是由于GROUP BY 决定了表的排序方式, 如果是左外连接, 如果右边没有对应的话,还要填补空值. 如果排序方式对应不起来, 就会错乱。

评论

2 楼 javaeyetodj 2010-11-08  

学习了理解了  ！

1 楼 zl-2577 2010-08-17  

不错，说的很详细。