inner join，left join，right join 解析

      最然我们在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

    结果：
   

 

    如果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,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

    结果：
   

    分析下，为什么会有这样的结果，找个诀窍，看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

    结果：
   

    在分析下，看Sql语句的 from 关键字的后面是哪张表(employee 表)，那么结果一定包含employee 表中的所查询的字段(name字段)所关联左表(branch表)的所有数据。
 
 
  c.右连接(right join) :

   与左链接相反！

评论

23 楼 hw361062491 2012-04-11  

wzglovejava 写道

不是吧，楼主的分析很正确，只是，我们平时在项目中就是直接用的where a.id=b.id感觉这样也很好，他实现的效果跟inner join是一样的，从性能的优化程度看，哪个更好些呢？请顶贴。。。

22 楼 J-catTeam 2010-04-07  

J-catTeam 写道

J-catTeam 写道

wangqj 写道

J-catTeam 写道

wander312 写道

select * from a inner join b on a.id=b.id
与
select * from a,b where a.id=b.id
结果是一样, 请教一下, 它们是不是完全一样?

第一句是通用的
第二句只有Oracle可以识别。MySQL不能识别的

不要误导别人

呵呵·你好 不对的地方可以指出来。我也可以学习一下 是吧，刚才验证了下。我确实说错了
这两种都是支持的。所以我也需要研究下

select * from a inner join b on a.id=b.id
几乎所有的数据库都支持的内联方式
select * from a,b where a.id=b.id
主流数据库支持的内联方式
两者执行结果是一样的~

21 楼 J-catTeam 2010-04-07  

ytsmtxxi 写道

J-catTeam 写道

内联
select * from a inner join b on a.id=b.id
外联{
1.全外联-select * from a full outer join b on a.id=b.id  并
2.左外联-select * from a left outer join b on a.id=b.id a去匹配b
3.右外联 -select * from a right outer join b on a.id=b.id b去匹配a

}

是不是有问题，应该是：

2.左外联-select * from a left outer join b on a.id=b.id b去匹配a
3.右外联 -select * from a right outer join b on a.id=b.id a去匹配b

对的~呵呵~

20 楼 ytsmtxxi 2010-04-06  

J-catTeam 写道

内联
select * from a inner join b on a.id=b.id
外联{
1.全外联-select * from a full outer join b on a.id=b.id  并
2.左外联-select * from a left outer join b on a.id=b.id a去匹配b
3.右外联 -select * from a right outer join b on a.id=b.id b去匹配a

}

是不是有问题，应该是：

2.左外联-select * from a left outer join b on a.id=b.id b去匹配a
3.右外联 -select * from a right outer join b on a.id=b.id a去匹配b

19 楼 J-catTeam 2010-04-04  

J-catTeam 写道

wangqj 写道

J-catTeam 写道

wander312 写道

select * from a inner join b on a.id=b.id
与
select * from a,b where a.id=b.id
结果是一样, 请教一下, 它们是不是完全一样?

第一句是通用的
第二句只有Oracle可以识别。MySQL不能识别的

不要误导别人

呵呵·你好 不对的地方可以指出来。我也可以学习一下 是吧，刚才验证了下。我确实说错了
这两种都是支持的。所以我也需要研究下

18 楼 J-catTeam 2010-04-04  

wangqj 写道

J-catTeam 写道

wander312 写道

select * from a inner join b on a.id=b.id
与
select * from a,b where a.id=b.id
结果是一样, 请教一下, 它们是不是完全一样?

第一句是通用的
第二句只有Oracle可以识别。MySQL不能识别的

不要误导别人

呵呵·你好 不对的地方可以指出来。我也可以学习一下 是吧

17 楼 wangqj 2010-04-03  

J-catTeam 写道

wander312 写道

select * from a inner join b on a.id=b.id
与
select * from a,b where a.id=b.id
结果是一样, 请教一下, 它们是不是完全一样?

第一句是通用的
第二句只有Oracle可以识别。MySQL不能识别的

不要误导别人

16 楼 風一樣的男子 2010-04-03  

oracle 的 (+) 写法开始把我搞混了

15 楼 J-catTeam 2010-04-01  

wander312 写道

select * from a inner join b on a.id=b.id
与
select * from a,b where a.id=b.id
结果是一样, 请教一下, 它们是不是完全一样?

第一句是通用的
第二句只有Oracle可以识别。MySQL不能识别的

14 楼 venus224 2010-04-01  

写得还不错!

13 楼 wander312 2010-04-01  

select * from a inner join b on a.id=b.id
与
select * from a,b where a.id=b.id
结果是一样, 请教一下, 它们是不是完全一样?

12 楼 includemain 2010-03-31  

mysql> insert into branch (branch) values ('finance');
Query OK, 1 row affected (0.03 sec)

mysql> insert into branch (branch) values('personnal');
Query OK, 1 row affected (0.03 sec)

mysql> insert into branch (branch) values('development');
Query OK, 1 row affected (0.05 sec)

mysql> select * from branch;
+----+-------------+
| id | branch      |
+----+-------------+
|  1 | finance     |
|  2 | personnal   |
|  3 | development |
+----+-------------+
3 rows in set (0.00 sec)

mysql> insert into employee values('zhang', 1);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into employee(name, branchid) values('zhang', 1);
Query OK, 1 row affected (0.03 sec)

mysql> insert into employee(name, branchid) values('huang', 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into employee(name, branchid) values('wang', 3);
Query OK, 1 row affected (0.02 sec)

mysql> insert into employee(name, branchid) values('tan', 3);
Query OK, 1 row affected (0.03 sec)

mysql> insert into employee(name, branchid) values('xx', 2);
Query OK, 1 row affected (0.02 sec)

mysql> select * from branch;
+----+-------------+
| id | branch      |
+----+-------------+
|  1 | finance     |
|  2 | personnal   |
|  3 | development |
+----+-------------+
3 rows in set (0.00 sec)

mysql> select * from employee
    -> ;
+----+-------+----------+
| id | name  | branchid |
+----+-------+----------+
|  1 | zhang |        1 |
|  2 | huang |        1 |
|  3 | wang  |        3 |
|  4 | tan   |        3 |
|  5 | xx    |        2 |
+----+-------+----------+
5 rows in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_qdao |
+----------------+
| branch         |
| employee       |
| people         |
| t_life         |
| t_people       |
| t_user         |
+----------------+
6 rows in set (0.00 sec)

mysql> select b.branch, e.name from employee as e innser join branch as b on b.i
d = e.branchid;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'innse
r join branch as b on b.id = e.branchid' at line 1
mysql> select b.branch, e.name from employee as e inner join branch as b on b.id
 = e.branchid;
+-------------+-------+
| branch      | name  |
+-------------+-------+
| finance     | zhang |
| finance     | huang |
| development | wang  |
| development | tan   |
| personnal   | xx    |
+-------------+-------+
5 rows in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_qdao |
+----------------+
| branch         |
| employee       |
| people         |
| t_life         |
| t_people       |
| t_user         |
+----------------+
6 rows in set (0.00 sec)

mysql> select * from employee;
+----+-------+----------+
| id | name  | branchid |
+----+-------+----------+
|  1 | zhang |        1 |
|  2 | huang |        1 |
|  3 | wang  |        3 |
|  4 | tan   |        3 |
|  5 | xx    |        2 |
+----+-------+----------+
5 rows in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_qdao |
+----------------+
| branch         |
| employee       |
| people         |
| t_life         |
| t_people       |
| t_user         |
+----------------+
6 rows in set (0.02 sec)

mysql> select b.branch, e.name from employee as e inner join branch as b on b.id
 = e.branchid;
+-------------+-------+
| branch      | name  |
+-------------+-------+
| finance     | zhang |
| finance     | huang |
| development | wang  |
| development | tan   |
| personnal   | xx    |
+-------------+-------+
5 rows in set (0.00 sec)

mysql> select b.branch , e.name from employee as e , branch as b where e.branchi
d = b.id;
+-------------+-------+
| branch      | name  |
+-------------+-------+
| finance     | zhang |
| finance     | huang |
| development | wang  |
| development | tan   |
| personnal   | xx    |
+-------------+-------+
5 rows in set (0.00 sec)

mysql> select b.branch , e.name from branch as b left join employee as e on b.id
 = e.branchid;
+-------------+-------+
| branch      | name  |
+-------------+-------+
| finance     | zhang |
| finance     | huang |
| personnal   | xx    |
| development | wang  |
| development | tan   |
+-------------+-------+
5 rows in set (0.00 sec)

mysql> insert into branch values('advertisement');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into branch (branch) values('advertisement');
Query OK, 1 row affected (0.03 sec)

mysql> select b.branch , e.name from branch as b left join employee as e on b.id
 = e.branchid;
+---------------+-------+
| branch        | name  |
+---------------+-------+
| finance       | zhang |
| finance       | huang |
| personnal     | xx    |
| development   | wang  |
| development   | tan   |
| advertisement | NULL  |
+---------------+-------+
6 rows in set (0.00 sec)

mysql> insert into employee(name,branchid) values('yy', 5);
Query OK, 1 row affected (0.03 sec)

mysql> select * from employee;
+----+-------+----------+
| id | name  | branchid |
+----+-------+----------+
|  1 | zhang |        1 |
|  2 | huang |        1 |
|  3 | wang  |        3 |
|  4 | tan   |        3 |
|  5 | xx    |        2 |
|  6 | yy    |        5 |
+----+-------+----------+
6 rows in set (0.00 sec)

mysql> select * from branch as b left join employee as e;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 1
mysql> select * from branch as b left join employee as e on 1= 1;
+----+---------------+------+-------+----------+
| id | branch        | id   | name  | branchid |
+----+---------------+------+-------+----------+
|  1 | finance       |    1 | zhang |        1 |
|  1 | finance       |    2 | huang |        1 |
|  1 | finance       |    3 | wang  |        3 |
|  1 | finance       |    4 | tan   |        3 |
|  1 | finance       |    5 | xx    |        2 |
|  1 | finance       |    6 | yy    |        5 |
|  2 | personnal     |    1 | zhang |        1 |
|  2 | personnal     |    2 | huang |        1 |
|  2 | personnal     |    3 | wang  |        3 |
|  2 | personnal     |    4 | tan   |        3 |
|  2 | personnal     |    5 | xx    |        2 |
|  2 | personnal     |    6 | yy    |        5 |
|  3 | development   |    1 | zhang |        1 |
|  3 | development   |    2 | huang |        1 |
|  3 | development   |    3 | wang  |        3 |
|  3 | development   |    4 | tan   |        3 |
|  3 | development   |    5 | xx    |        2 |
|  3 | development   |    6 | yy    |        5 |
|  4 | advertisement |    1 | zhang |        1 |
|  4 | advertisement |    2 | huang |        1 |
|  4 | advertisement |    3 | wang  |        3 |
|  4 | advertisement |    4 | tan   |        3 |
|  4 | advertisement |    5 | xx    |        2 |
|  4 | advertisement |    6 | yy    |        5 |
+----+---------------+------+-------+----------+
24 rows in set (0.00 sec)

mysql> select * from branch as b left join employee as e on b.id= e.branchid;
+----+---------------+------+-------+----------+
| id | branch        | id   | name  | branchid |
+----+---------------+------+-------+----------+
|  1 | finance       |    1 | zhang |        1 |
|  1 | finance       |    2 | huang |        1 |
|  2 | personnal     |    5 | xx    |        2 |
|  3 | development   |    3 | wang  |        3 |
|  3 | development   |    4 | tan   |        3 |
|  4 | advertisement | NULL | NULL  |     NULL |
+----+---------------+------+-------+----------+
6 rows in set (0.00 sec)

mysql> select b.branch,e.name from branch as b left join employee as e on b.id=
e.branchid;
+---------------+-------+
| branch        | name  |
+---------------+-------+
| finance       | zhang |
| finance       | huang |
| personnal     | xx    |
| development   | wang  |
| development   | tan   |
| advertisement | NULL  |
+---------------+-------+
6 rows in set (0.00 sec)

mysql> select e.name,b.branch from employee as e left join branch as b where e.b
ranchid = b.id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'where
 e.branchid = b.id' at line 1
mysql> select e.name,b.branch from employee as e left join branch as b on e.bran
chid = b.id;
+-------+-------------+
| name  | branch      |
+-------+-------------+
| zhang | finance     |
| huang | finance     |
| wang  | development |
| tan   | development |
| xx    | personnal   |
| yy    | NULL        |
+-------+-------------+
6 rows in set (0.00 sec)

mysql>

11 楼 banfry 2010-03-31  

不错不错，看完这篇文章我相信我在也不会把 这些join搞混....

10 楼 J-catTeam 2010-03-30  

外联的话话 左边那张表会全扫的 右边的sql优化器会自己选择
这句说错了 呵呵

如果是全外联的话 都会扫
左外联全扫左边那张表
反之亦然

9 楼 zjshan 2010-03-30  

     

8 楼 J-catTeam 2010-03-30  

wzglovejava 写道

不是吧，楼主的分析很正确，只是，我们平时在项目中就是直接用的where a.id=b.id感觉这样也很好，他实现的效果跟inner join是一样的，从性能的优化程度看，哪个更好些呢？请顶贴。。。

wzglovejava 写道

不是吧，楼主的分析很正确，只是，我们平时在项目中就是直接用的where a.id=b.id感觉这样也很好，他实现的效果跟inner join是一样的，从性能的优化程度看，哪个更好些呢？请顶贴。。。

where a.id=b.id 把它看成内联select * from a inner join b on a.id=b.id
如果没有索引,两个表都会全部扫描的。如果有索引的话·a表是会全扫的。b表不会全扫，因为已经建立了索引了。这个是数据库去决定怎么走的
外联的话话 左边那张表会全扫的 右边的sql优化器会自己选择

这种性能相关的 其实我们也决定不了·（在sql写正确的情况下）

7 楼 wzglovejava 2010-03-30  

不是吧，楼主的分析很正确，只是，我们平时在项目中就是直接用的where a.id=b.id感觉这样也很好，他实现的效果跟inner join是一样的，从性能的优化程度看，哪个更好些呢？请顶贴。。。

6 楼 chenchuan 2010-03-29  

很少用者个了 现在hibernate都封装好    但是效率上。。
  还是原生sql效率高

5 楼 J-catTeam 2010-03-29  

smiky 写道

我悲惨了,平时写sql都直接用=连接的,从来没用过left,right的,什么a.id=b.id(+)之类的,看来以后要这样写.......

看你什么数据库
Mysql和Oracle的写法不一样的
我写那个应该是通用的

4 楼 smiky 2010-03-29  

我悲惨了,平时写sql都直接用=连接的,从来没用过left,right的,什么a.id=b.id(+)之类的,看来以后要这样写.......