当前位置:首页 > 开发 > 数据库 > 正文

MySQL: Left Join, Right Join and Inner Join, Outer Join

发表于: 2013-04-19   作者:DavyJones2010   来源:转载   浏览次数:
摘要: Example to explain differences of left join and right join : ################# select * from goods; +----------+--------+------------+ | goods_id | cat_id | goods_name | +----------+--------+----

Example to explain differences of left join and right join :

#################
select * from goods;
+----------+--------+------------+
| goods_id | cat_id | goods_name |
+----------+--------+------------+
|        1 |      1 | CDMA Phone |
|        2 |      1 | GSM Phone  |
|        3 |      1 | 3G Phone   |
|        4 |      3 | TP Phone   |
+----------+--------+------------+
select * from cat;
+--------+---------------+
| cat_id | cat_name      |
+--------+---------------+
|      1 | Mobile Phone  |
|      2 | Settled Phone |
+--------+---------------+
#################

#Left join: Use left table as criterion.
#Pay attention to the last row.
select goods.goods_id, goods.cat_id, goods.goods_name, cat.cat_name from goods left join cat on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------------+
| goods_id | cat_id | goods_name | cat_name     |
+----------+--------+------------+--------------+
|        1 |      1 | CDMA Phone | Mobile Phone |
|        2 |      1 | GSM Phone  | Mobile Phone |
|        3 |      1 | 3G Phone   | Mobile Phone |
|        4 |      3 | TP Phone   | NULL         |
+----------+--------+------------+--------------+
select goods.*, cat.* from cat left join goods on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------+---------------+
| goods_id | cat_id | goods_name | cat_id | cat_name      |
+----------+--------+------------+--------+---------------+
|        1 |      1 | CDMA Phone |      1 | Mobile Phone  |
|        2 |      1 | GSM Phone  |      1 | Mobile Phone  |
|        3 |      1 | 3G Phone   |      1 | Mobile Phone  |
| NULL     | NULL   | NULL       |      2 | Settled Phone |
+----------+--------+------------+--------+---------------+

#Right join: Use right table as criterion
select goods.*, cat.* from cat right join goods on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------+--------------+
| goods_id | cat_id | goods_name | cat_id | cat_name     |
+----------+--------+------------+--------+--------------+
|        1 |      1 | CDMA Phone |      1 | Mobile Phone |
|        2 |      1 | GSM Phone  |      1 | Mobile Phone |
|        3 |      1 | 3G Phone   |      1 | Mobile Phone |
|        4 |      3 | TP Phone   | NULL   | NULL         |
+----------+--------+------------+--------+--------------+
select goods.*, cat.* from goods right join cat on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------+---------------+
| goods_id | cat_id | goods_name | cat_id | cat_name      |
+----------+--------+------------+--------+---------------+
|        1 |      1 | CDMA Phone |      1 | Mobile Phone  |
|        2 |      1 | GSM Phone  |      1 | Mobile Phone  |
|        3 |      1 | 3G Phone   |      1 | Mobile Phone  |
| NULL     | NULL   | NULL       |      2 | Settled Phone |
+----------+--------+------------+--------+---------------+

#Comment:
#table A left join table B == table B right join table A
#Both use table A as criterion
#Use the table that actually stands at the LEFT side as criterion

 

Example to explain Inner Join and Outter Join:

#Use the previous table

#Left join
select goods.goods_id, goods.cat_id, goods.goods_name, cat.cat_name from goods left join cat on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------------+
| goods_id | cat_id | goods_name | cat_name     |
+----------+--------+------------+--------------+
|        1 |      1 | CDMA Phone | Mobile Phone |
|        2 |      1 | GSM Phone  | Mobile Phone |
|        3 |      1 | 3G Phone   | Mobile Phone |
|        4 |      3 | TP Phone   | NULL         |
+----------+--------+------------+--------------+

#Right join
select goods.*, cat.* from cat left join goods on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------+---------------+
| goods_id | cat_id | goods_name | cat_id | cat_name      |
+----------+--------+------------+--------+---------------+
|        1 |      1 | CDMA Phone |      1 | Mobile Phone  |
|        2 |      1 | GSM Phone  |      1 | Mobile Phone  |
|        3 |      1 | 3G Phone   |      1 | Mobile Phone  |
| NULL     | NULL   | NULL       |      2 | Settled Phone |
+----------+--------+------------+--------+---------------+

#Inner join
select goods.*, cat.* from goods inner join cat on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------+--------------+
| goods_id | cat_id | goods_name | cat_id | cat_name     |
+----------+--------+------------+--------+--------------+
|        1 |      1 | CDMA Phone |      1 | Mobile Phone |
|        2 |      1 | GSM Phone  |      1 | Mobile Phone |
|        3 |      1 | 3G Phone   |      1 | Mobile Phone |
+----------+--------+------------+--------+--------------+

#Outter join
select goods.*, cat.* from goods left join cat on goods.cat_id = cat.cat_id union select goods.*, cat.* from goods right join cat on goods.cat_id = cat.cat_id;
+----------+--------+------------+--------+---------------+
| goods_id | cat_id | goods_name | cat_id | cat_name      |
+----------+--------+------------+--------+---------------+
|        1 |      1 | CDMA Phone |      1 | Mobile Phone  |
|        2 |      1 | GSM Phone  |      1 | Mobile Phone  |
|        3 |      1 | 3G Phone   |      1 | Mobile Phone  |
|        4 |      3 | TP Phone   | NULL   | NULL          |
| NULL     | NULL   | NULL       |      2 | Settled Phone |
+----------+--------+------------+--------+---------------+

   Comment:

        1) Inner join will not use left table or right table as criterion.

        2) Just think as table A cartesian product table B. And then apply the filter on the result set.

        3) Or regard  Inner Join as the Intersection of Left Join and Right Join.

        4) So how to get the Union of Left Join and Right Join? ----> Outter Join is not supported by MySQL!----> But we can use UNION to combine the result set of left join and right join to realize this!

 

Comments:

    1) How to join more than to tables?---->Will be explained in detail in next charpter.

MySQL: Left Join, Right Join and Inner Join, Outer Join

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
看到某某用了左连接,右连接之类的,但是概念很模糊了 今天特地弄了一下 简单理解如下: 左连接(左
今天偶然间在stackoverflow看到此图,主要讲解mysql left join,right join,inner join和outer joi
最然我们在JavaEE开发中不常用inner join,left join,right jion,由hibernate封装了,但是在最近
写在前面:不总结就会忘记! 先给个通俗的解释吧. 例表a aid adate 1 a1 2 a2<
注意下图当:left join tbl_EmployeeLD c on a.EmpNum = c.EmpNum 注意下图当:left join tbl_Emplo
1、新建两张表,如下图所示: 其中,persons表中的id与orders表中的pid相对应。 2、join select a.*
测试表用户表user: 测试表密码表pwd: 1、创建user表: CREATE TABLE `user` ( `id` int(11) NOT N
CREATE TABLE `a` ( `a1` VARCHAR(10) DEFAULT NULL, `b1` VARCHAR(10) DEFAULT NULL, `c1` VARCHAR
CREATE TABLE `a` ( `a1` VARCHAR(10) DEFAULT NULL, `b1` VARCHAR(10) DEFAULT NULL, `c1` VARCHAR
这是一篇来自Coding Horror的文章。 SQL的Join语法有很多:有inner的,有outer的,有left的,有时候
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号