SQL JOIN 简单介绍

转自http://www.admin10000.com/　

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:

1 2 3 4 5 6 7

+ -----------+-------------+------+-----+---------+----------------+ | 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:

1 2 3 4 5 6 7

+ -------+-------------+------+-----+---------+----------------+ | 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:

1 2 3 4 5 6 7

+ -----+-----------+----------+ | pid | firstname | lastname | + -----+-----------+----------+ |   1 | andy      | chen     | |   2 | irri      | wan      | |   3 | abby      | sun      | + -----+-----------+----------+

　　tb_person表中有三位人员，分别是andy Chen, irri Wan, abby Sun；

　　data in tb_order:

1 2 3 4 5 6 7

+ -----+----------+------+ | 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两张表，我们可以看到有三种情形：

1. person表中的人购买了商品，也就是order表中有关于该用户的商品购买记录，我们可以从该表中查询到该用户买了哪些商品，如andy Chen购买了book和phone两种商品，即pid在tb_person和tb_order两种表中都存在；

2. person表中的人未购买商品，如irri Wan和abby Sun两位用户并未购买任何商品，即pid只存在于tb_person表；

3. order表中购买商品的用户在person表中找不到记录，如pid为4的用户购买了一台computer但在tb_person表中没有该用户的记录，即pid只存在于tb_order表；

　　理解上述三种情形对于我们理解join有非常大的帮助，接下来我们将具体的分析每种join的区别：

　　INNER JOIN

　　所谓inner join的意思就是我们前面提到的情形1，pid必须在tb_person和tb_order两张表中同时存在；

1 2 3 4

MariaDB [demo]>  SELECT  p.pid, p.firstname, o.oname      ->  FROM  tb_person p      ->  INNER  JOIN  tb_order o      ->  ON  p.pid=o.pid;

1 2 3 4 5 6

+ -----+-----------+-------+ | 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中没有购买任何商品的用户集。

1 2 3 4

MariaDB [demo]>  SELECT  p.pid, p.firstname, o.oname      ->  FROM  tb_person p      ->  LEFT  JOIN  tb_order o      ->  ON  p.pid=o.pid;

1 2 3 4 5 6 7 8

+ -----+-----------+-------+ | 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表。

1 2 3 4

MariaDB [demo]>  SELECT  p.pid, p.firstname, o.oname      ->  FROM  tb_person p      ->  RIGHT  JOIN  tb_order o      ->  ON  p.pid=o.pid;

1 2 3 4 5 6 7

+ ------+-----------+----------+ | 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的结果。

1 2 3 4 5 6 7 8 9

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;

1 2 3 4 5 6 7 8 9

+ ------+-----------+----------+ | 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文件

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

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);