最然我们在JavaEE开发中不常用inner join,left join,right jion,由hibernate封装了
现在有两张表:
1.部门表(branch):
2.员工表(employee):
a.内连接(inner join)
内连接就是 获取 两张表共有的数据。
- SELECT b.branch,e.name FROM branch AS b INNER JOIN employee AS e ON b.id=e.branchid
SELECT b.branch,e.name FROM branch AS b INNER JOIN employee AS e ON b.id=e.branchid
结果:
如果Sql换个写法:
- SELECT b.branch,e.name FROM employee AS e INNER JOIN branch AS b ON b.id=e.branchid
SELECT b.branch,e.name FROM employee AS e INNER JOIN branch AS b ON b.id=e.branchid
结果:
呵呵,发现结果是一样的,不管内连接的哪张表,结果都是获取的两张表的共有数据!
此外,还有一种写法,能达到同样的效果:
- SELECT b.branch,e.name FROM employee AS e,branch AS b WHERE b.id=e.branchid
SELECT b.branch,e.name FROM employee AS e,branch AS b WHERE b.id=e.branchid
结果:
b.左连接(left join) :
- SELECT b.branch,e.name FROM branch AS b LEFT JOIN employee AS e ON b.id=e.branchid
SELECT b.branch,e.name FROM branch AS b LEFT JOIN employee AS e ON b.id=e.branchid
结果:
分析下,为什么会有这样的结果,找个诀窍,看Sql语句的 from 关键字的后面是哪张表(branch表),那么结果一定包含branch表中的所查询的字段(branch字段)所关联左表(employee表)的所有数据,如果左连接表(employee表中)不存在branch中的所查询字段,那么该字段的值为Null。
好了,现在换个写法:
- SELECT b.branch,e.name FROM employee AS e LEFT JOIN branch AS b ON b.id=e.branchid
SELECT b.branch,e.name FROM employee AS e LEFT JOIN branch AS b ON b.id=e.branchid
结果:
在分析下,看Sql语句的 from 关键字的后面是哪张表(employee 表),那么结果一定包含employee 表中的所查询的字段(name字段)所关联左表(branch表)的所有数据。
c.右连接(right join) :
与左链接相反!