MySQL连表查询

  参考:https://blog.csdn.net/lj1994104/article/details/79671807

  一,MySQL连接查询类型

  A)内连接:join,inner join

  B)外连接:left join,left outer join,right join,right outer join,union

  C)交叉连接:cross join

  二,下面以示例进行分析

  创建两张表,把表当成一个集合那么表中的元素就是集合的一个元素

  创建a表

CREATE TABLE `a` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `age` int(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

   创建b表

CREATE TABLE `b` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

   插入测试数据

MySQL连表查询        MySQL连表查询

 

 

   2.1内连接inner join或者join(等同与inner join)

mysql> select * from a inner join b on a.id=b.id;
+----+------+----+-------+
| id | age  | id | name  |
+----+------+----+-------+
|  2 |   11 |  2 | zhang |
|  3 |   12 |  3 | li    |
|  4 |   13 |  4 | zhou  |
+----+------+----+-------+

   应用场景

MySQL连表查询

 

   这种场景下得到的是满足某一条件的A,B内部的数据;本次需要满足的条件是a.id等于b。id

  正因为得到的是内部共有数据,所以连接方式称为内连接。

  2.2外连接(6种场景)

  2.2.1 left join 或者left outer join(等同于left join)
mysql> select * from a left join b on a.id=b.id;
+----+------+------+-------+
| id | age  | id   | name  |
+----+------+------+-------+
|  2 |   11 |    2 | zhang |
|  3 |   12 |    3 | li    |
|  4 |   13 |    4 | zhou  |
|  1 |   10 | NULL | NULL  |
+----+------+------+-------+

   Table B中不存在的记录填充NULL

  应用场景

MySQL连表查询

 

 

  2.2.2  [left   join 或者left outer join(等同于left join)]  +  [where B.column is null] 
mysql> select * from a left join b on a.id=b.id where b.id is null;
+----+------+------+------+
| id | age  | id   | name |
+----+------+------+------+
|  1 |   10 | NULL | NULL |
+----+------+------+------+

   应用场景

MySQL连表查询

 

   

  这种场景下得到的是A中的所有数据减去"与B满足同一条件 的数据",然后得到的A剩余数据;

  2.2.3  right join 或者fight outer join(等同于right join)
mysql> select * from a right join b on a.id=b.id;
+------+------+----+-------+
| id   | age  | id | name  |
+------+------+----+-------+
|    2 |   11 |  2 | zhang |
|    3 |   12 |  3 | li    |
|    4 |   13 |  4 | zhou  |
| NULL | NULL |  5 | chen  |
+------+------+----+-------+

   应用场景

MySQL连表查询

 

   这种场景下得到的是B的所有数据,和满足某一条件的A的数据;

  2.2.4 [left   join 或者left outer join(等同于left join)]  +  [where A.column is null]
mysql> select * from a right join b on a.id=b.id where a.id is null;
+------+------+----+------+
| id   | age  | id | name |
+------+------+----+------+
| NULL | NULL |  5 | chen |
+------+------+----+------+

   应用场景

MySQL连表查询

  

 

   这种场景下得到的是B中的所有数据减去 "与A满足同一条件 的数据“,然后得到的B剩余数据;

  2.2.5 full join (mysql不支持,但是可以用 left join  union right join代替)
mysql> select * from a left join b on a.id=b.id union select * from a right join b on a.id=b.id;
+------+------+------+-------+
| id   | age  | id   | name  |
+------+------+------+-------+
|    2 |   11 |    2 | zhang |
|    3 |   12 |    3 | li    |
|    4 |   13 |    4 | zhou  |
|    1 |   10 | NULL | NULL  |
| NULL | NULL |    5 | chen  |
+------+------+------+-------+

   union过后,重复的记录会合并(id为2,3,4的三条记录),

  应用场景

MySQL连表查询

 

   这种场景下得到的是满足某一条件的公共记录,和独有的记录

  

  2.2.6 full join + is null(mysql不支持,但是可以用 (left join + is null) union (right join+isnull代替)
mysql> select * from a left join b on a.id=b.id where b.id is null union select * from a right join b on a.id=b.id where a.id is null;
+------+------+------+------+
| id   | age  | id   | name |
+------+------+------+------+
|    1 |   10 | NULL | NULL |
| NULL | NULL |    5 | chen |
+------+------+------+------+

   应用场景

 

 MySQL连表查询  

  这种场景下得到的是A,B中不满足某一条件的记录之和


  注:上面共有其中七(2^3-1)种应用场景,还有一种是全空白,那就是什么都不查,七种情形包含了实际应用所有可能的场景
  2.3交叉连接(cross join)

  2.3.1 实际应用中还有这样一种情形,想得到A,B记录的排列组合,即笛卡儿积,这个就不好用集合和元素来表示了。需要用到cross join:

mysql> select * from a cross join b;                                                                           
+----+------+----+-------+
| id | age  | id | name  |
+----+------+----+-------+
|  1 |   10 |  2 | zhang |
|  2 |   11 |  2 | zhang |
|  3 |   12 |  2 | zhang |
|  4 |   13 |  2 | zhang |
|  1 |   10 |  3 | li    |
|  2 |   11 |  3 | li    |
|  3 |   12 |  3 | li    |
|  4 |   13 |  3 | li    |
|  1 |   10 |  4 | zhou  |
|  2 |   11 |  4 | zhou  |
|  3 |   12 |  4 | zhou  |
|  4 |   13 |  4 | zhou  |
|  1 |   10 |  5 | chen  |
|  2 |   11 |  5 | chen  |
|  3 |   12 |  5 | chen  |
|  4 |   13 |  5 | chen  |
+----+------+----+-------+

   

  2.3.2 还可以为cross  join指定条件 (where):

mysql> select * from a cross join b where a.id=b.id;
+----+------+----+-------+
| id | age  | id | name  |
+----+------+----+-------+
|  2 |   11 |  2 | zhang |
|  3 |   12 |  3 | li    |
|  4 |   13 |  4 | zhou  |
+----+------+----+-------+

   注:这种情况实际上实现了内连接

  

  三 注意事项

  上面仍然存在遗漏,那就是mysql对sql语句的容错问题,即在sql语句不完全符合书写建议的情况,mysql会允许这种情况,尽可能地解释它:

  3.1 一般cross join后面加上where条件,但是用cross join+on也是被解释为cross join+where;

  3.2 一般内连接都需要加上on限定条件,如上面场景2.1;如果不加会被解释为交叉连接;

  3.3 如果连接表格使用的是逗号,会被解释为交叉连接;

  注:sql标准中还有union join和natural  inner join,mysql不支持,而且本身也没有多大意义,其结果可以用上面的几种连接方式得到

 

 



 

MySQL连表查询

上一篇:[已完结]CMU数据库(15-445)实验2-B+树索引实现(下)


下一篇:mysql 按组内顺序 分配序号