龙都娱乐»龙都娱乐»SQL JOIN 简单介绍

SQL JOIN 简单介绍

来源:gschen 发布时间:2014-05-02 阅读次数:

 前言

  本文还是秉持之前一贯的写作风格,以简单易懂的示例帮助大家了解各种join的区别。

 为什么需要join

  为什么需要join?join中文意思为连接,连接意味着关联即将一个表和多个表之间关联起来。在处理龙都娱乐表的时候,我们经常会发现,需要从多个表中获取信息,将多个表的多个字段数据组装起来再返回给调用者。所以join的前提是这些表之间必须有关联字段。

 join的分类

  join分为两种,inner join和outer join,其中outer join分为三种,left outer join, right outer join, full outer join,另外left outer join又简称为left join即大家所熟知的左连接。

 各种join的区别

  在介绍各种join的区别之前,我们先来看一个简单的示例:

  场景描述:

  互联网时代,大家都喜欢在网上购物,尤其是淘宝和京东,所以我们选择的场景也是大家熟悉的网上购物。这是一个关于一个人和他在商城买了什么商品的一个故事;   针对上述需求,我们建立了两张表,tb_person和tb_order,其中tb_person是关于这个人的描述,tb_order是关于他购买的商品的一个描述。   我们的表结构很简单,tb_person只需要知道这个人是谁就可以了,所以只有三个字段id,firstname(名)和lastname(姓),同样tb_order也很简单,我们只要知道谁买了什么商品,所以只需要3个字段,分别是oid, oname(商品名称), pid(购买者编号)。   tb_person: +-----------+-------------+------+-----+---------+----------------+ | Field     | Type        | Null | Key | Default | Extra          | +-----------+-------------+------+-----+---------+----------------+ | pid       | int(11)     | NO   | PRI | NULL    | auto_increment | | firstname | varchar(50) | YES  |     | NULL    |                | | lastname  | varchar(50) | YES  |     | NULL    |                | +-----------+-------------+------+-----+---------+----------------+   tb_order: +-------+-------------+------+-----+---------+----------------+ | Field | Type        | Null | Key | Default | Extra          | +-------+-------------+------+-----+---------+----------------+ | oid   | int(11)     | NO   | PRI | NULL    | auto_increment | | oname | varchar(50) | YES  |     | NULL    |                | | pid   | int(11)     | YES  |     | NULL    |                | +-------+-------------+------+-----+---------+----------------+   接下来,我们向上述两张表中写入一些示例数据:   data in tb_person: +-----+-----------+----------+ | pid | firstname | lastname | +-----+-----------+----------+ |   1 | andy      | chen     | |   2 | irri      | wan      | |   3 | abby      | sun      | +-----+-----------+----------+   tb_person表中有三位人员,分别是andy Chen, irri Wan, abby Sun;   data in tb_order: +-----+----------+------+ | oid | oname    | pid  | +-----+----------+------+ |   1 | book     |    1 | |   2 | phone    |    1 | |   3 | computer |    4 | +-----+----------+------+   tb_order表中记录了3条数据,人员编号为1也就是andy Chen买了两件商品分别是book和phone,另外还有一个人员编号为4的人买了一件商品computer。关于这个大家可能会产生疑问,为什么tb_person表中没有人员编号为4的人呢?这里我们姑且认为由于注册用户较多,我们采用了用户分表策略,所以人员编号为4的用户可能在另外一张人员表中。   从之前的描述我们知道,表与表之间如果要join则必须要有关联的字段,上述示例我们看到这个关联的字段就是pid。   根据tb_person和tb_order两张表,我们可以看到有三种情形:
  • person表中的人购买了商品,也就是order表中有关于该用户的商品购买记录,我们可以从该表中查询到该用户买了哪些商品,如andy Chen购买了book和phone两种商品,即pid在tb_person和tb_order两种表中都存在;
  • person表中的人未购买商品,如irri Wan和abby Sun两位用户并未购买任何商品,即pid只存在于tb_person表;
  • order表中购买商品的用户在person表中找不到记录,如pid为4的用户购买了一台computer但在tb_person表中没有该用户的记录,即pid只存在于tb_order表;
  •   理解上述三种情形对于我们理解join有非常大的帮助,接下来我们将具体的分析每种join的区别:

      INNER JOIN

      所谓inner join的意思就是我们前面提到的情形1,pid必须在tb_person和tb_order两张表中同时存在; MariaDB [demo]> SELECT p.pid, p.firstname, o.oname     -> FROM tb_person p     -> INNER JOIN tb_order o     -> ON p.pid=o.pid; +-----+-----------+-------+ | pid | firstname | oname | +-----+-----------+-------+ |   1 | andy      | book  | |   1 | andy      | phone | +-----+-----------+-------+

      LEFT JOIN

      tb_person LEFT JOIN tb_order的意思是上述情形1,情形2的并集。LEFT JOIN的结果集不仅包含INNER JOIN的结果,而且还包含所有tb_person中没有购买任何商品的用户集。 MariaDB [demo]> SELECT p.pid, p.firstname, o.oname     -> FROM tb_person p     -> LEFT JOIN tb_order o     -> ON p.pid=o.pid; +-----+-----------+-------+ | pid | firstname | oname | +-----+-----------+-------+ |   1 | andy      | book  | |   1 | andy      | phone | |   2 | irri      | NULL  | |   3 | abby      | NULL  | +-----+-----------+-------+

      RIGHT JOIN

      tb_person RIGHT JOIN tb_order的意思是上述情形1和情形3的并集。RIGHT JOIN的结果集不仅包含INNER JOIN的结果,而且还包含所有tb_order中所有已经购买商品的用户但该用户记录不存在于tb_person表。 MariaDB [demo]> SELECT p.pid, p.firstname, o.oname     -> FROM tb_person p     -> RIGHT JOIN tb_order o     -> ON p.pid=o.pid; +------+-----------+----------+ | pid  | firstname | oname    | +------+-----------+----------+ |    1 | andy      | book     | |    1 | andy      | phone    | | NULL | NULL      | computer | +------+-----------+----------+

      FULL JOIN

      故名思议,FULL JOIN就是上述情形1,2,3的并集了,但是mysql龙都娱乐不支持full join查询,所以我们只能LEFT JOIN union RIGHT JOIN,才能得到FULL JOIN的结果。 MariaDB [demo]> SELECT p.pid, p.firstname, o.oname     -> FROM tb_person p     -> LEFT JOIN tb_order o     -> ON p.pid=o.pid     -> UNION     -> SELECT p.pid, p.firstname, o.oname     -> FROM tb_person p     -> RIGHT JOIN tb_order o     -> ON p.pid=o.pid; +------+-----------+----------+ | pid  | firstname | oname    | +------+-----------+----------+ |    1 | andy      | book     | |    1 | andy      | phone    | |    2 | irri      | NULL     | |    3 | abby      | NULL     | | NULL | NULL      | computer | +------+-----------+----------+   注:我们上述的sql语句全部基于mysql龙都娱乐执行。

     总结

      本文主要描述了sql join的分类以及各种join的区别,通过简单的示例,让大家更清晰的去了解他们。至于什么时候使用join要视具体的情况而定,根据不同的需求采用不同的策略。   非常感谢大家的热心回复,可能有些问题的探讨超出了本文的范畴,但是非常乐意大家提出问题,然后大家一起去探索去发现。

     引用

      NULL

     附件

      demo.sql文件 create database demo; use demo; create table tb_person (     pid int(11) auto_increment,     firstname varchar(50),     lastname varchar(50),     primary key(pid) ); create table tb_order (     oid int(11) auto_increment,     oname varchar(50),     pid int(11),     primary key(oid) ); insert into tb_person(firstname, lastname) values('andy','chen'); insert into tb_person(firstname, lastname) values('irri','wan'); insert into tb_person(firstname, lastname) values('abby','sun'); insert into tb_order(oname, pid) values('book', 1); insert into tb_order(oname, pid) values('phone', 1); insert into tb_order(oname, pid) values('computer', 4);
    QQ群:WEB龙都娱乐官方群(515171538),验证消息:10000
    微信群:加小编微信 849023636 邀请您加入,验证消息:10000
    提示:更多精彩内容关注微信公众号:全栈龙都娱乐中心(fsder-com)
    sql
    网友评论(共1条评论) 正在载入评论......
    理智评论文明上网,拒绝恶意谩骂 发表评论 / 共1条评论
    登录会员中心
    龙都娱乐