MySql：多表查询

上一篇博客介绍了多表设计，那么既然有了多表设计很自然就有多表查询啦~

首先简单说一下数学中的笛卡尔积~

在数学中，两个 集合 X和 Y的 笛卡儿积（Cartesian product），又称 直积，表示为 X  ×   Y，第一个对象是 X的成员而第二个对象是 Y的所有可能 有序对的其中一 个成员。

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

相信短短两行文字就足以令广大程序员大概明白什么叫做笛卡尔积。

那么跟多表查询有什么关系呢?

在查询中有一种叫做笛卡尔积查询

还是继续上篇的例子~感兴趣的盘友可以看看上一篇：

《MySql:多表设计---外键》 http://blog.csdn.net/javy_codercoder/article/details/49151951

上篇中的两个表：dept与emp，其中emp中的字段dept_id作为外键关联着dept的id~那么如果我有这样的需求：查询员工的同时查询他们的部门~

这很明显就是跨表数据查询~那么怎么查呢~我们先说一下上面提到的笛卡尔积查询：

select *from dept,emp;

+----+--------+----+------+---------+--------+

| id | name   | id | name | dept_id | salary |

+----+--------+----+------+---------+--------+

|  1 | 人事部 |  1 | 曹操 |       1 |   2000 |

|  1 | 人事部 |  2 | 荀彧 |       2 |   2000 |

|  1 | 人事部 |  4 | 郭嘉 |       4 |   2000 |

|  2 | 后勤部 |  1 | 曹操 |       1 |   2000 |

|  2 | 后勤部 |  2 | 荀彧 |       2 |   2000 |

|  2 | 后勤部 |  4 | 郭嘉 |       4 |   2000 |

|  3 | 财务部 |  1 | 曹操 |       1 |   2000 |

|  3 | 财务部 |  2 | 荀彧 |       2 |   2000 |

|  3 | 财务部 |  4 | 郭嘉 |       4 |   2000 |

|  4 | 行政部 |  1 | 曹操 |       1 |   2000 |

|  4 | 行政部 |  2 | 荀彧 |       2 |   2000 |

|  4 | 行政部 |  4 | 郭嘉 |       4 |   2000 |

+----+--------+----+------+---------+--------+

可以看到查询结果跟文章开头所介绍的概念一样：

A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

不过所查询出来的结果明显是不对的，那笔者为什么要提到笛卡尔积呢？

那是因为多表查询是基于笛卡尔积查询的。

内连接查询：

基于笛卡尔积查询的结果进行过滤就可以得到正确的查询结果，那么过滤的条件很明显就是dept_id与dept的id相等了。

select *from dept,emp where dept.id=emp.dept_id;

+----+--------+----+------+---------+--------+

| id | name   | id | name | dept_id | salary |

+----+--------+----+------+---------+--------+

|  1 | 人事部 |  1 | 曹操 |       1 |   2000 |

|  2 | 后勤部 |  2 | 荀彧 |       2 |   2000 |

|  4 | 行政部 |  4 | 郭嘉 |       4 |   2000 |

+----+--------+----+------+---------+--------+

这样就可以正确查出结果了，我们称这种查询叫做内连接查询，内连接查询的是两个表都能找到对应记录的记录。

内连接查询还有另外一种查询方法：

同样是基于上面的需求，

select * from dept inner join emp on dept.id=emp.dept_id;

+----+--------+----+------+---------+--------+

| id | name   | id | name | dept_id | salary |

+----+--------+----+------+---------+--------+

|  1 | 人事部 |  1 | 曹操 |       1 |   2000 |

|  2 | 后勤部 |  2 | 荀彧 |       2 |   2000 |

|  4 | 行政部 |  4 | 郭嘉 |       4 |   2000 |

+----+--------+----+------+---------+--------+

可以看到所查询出来的结果是一致的。

左外连接查询：

笔者一开始给dept插入的数据有四条，分别是人事部，后勤部，科技部，行政部。但是从上面的查询结果来看，可以看到是少了科技部。

为什么会少了科技部呢？因为emp表中没有人是属于科技部的，而上面提到内连接所查询的是两个表都能找到对应记录的记录。所以科技部没有显示出来。但是你不显示出来别人还以为没有这个部门呢？假如就算没有员工也要显示出来，那么该怎么查呢？看下面查询语句：

select * from dept left join emp on dept.id=emp.dept_id;

+----+--------+------+------+---------+--------+

| id | name   | id   | name | dept_id | salary |

+----+--------+------+------+---------+--------+

|  1 | 人事部 |    1 | 曹操 |       1 |   2000 |

|  2 | 后勤部 |    2 | 荀彧 |       2 |   2000 |

|  4 | 行政部 |    4 | 郭嘉 |       4 |   2000 |

|  3 | 财务部 | NULL | NULL |    NULL |   NULL |

+----+--------+------+------+---------+--------+

这样就可以了。这种查询叫做左外连接查询，左外连接查询是基于内链接查询的。在内链接查询的结果上增加左边表有而右边表没有的记录。这里所指的左边表是指写查询语句的时候写的第一个表，例如：select * from  dept left join  emp on dept.id=emp.dept_id;

其中dept就在左边，emp就在右边，所以dept就是左边的表，emp就是右边的表。

右外连接查询：

既然有左外连接查询那么就有右外连接查询，所谓的右外连接查询跟左外连接查询差不多，右外连接查询同样是基于内链接查询的。在内链接查询的结果上增加右边表有而左边表没有的记录。

select * from dept right  join emp on dept.id=emp.dept_id;

所谓的左和右不过是相对的，就在于查询的时候先写哪个表。这里就不再演示了。

全外连接查询：

在演示这种查询之前，笔者先插入一条记录到emp表：

insert into emp values (null,'荀攸',null,2000);

这个时候emp表里面的记录是这样的：

+----+------+---------+--------+

| id | name | dept_id | salary |

+----+------+---------+--------+

|  1 | 曹操 |       1 |   2000 |

|  2 | 荀彧 |       2 |   2000 |

|  4 | 郭嘉 |       4 |   2000 |

|  5 | 荀攸 |    NULL |   2000 |

+----+------+---------+--------+

现在emp表有dept表没有对应的内容，而dept表也有emp表没有对应的内容。

那么如果我想查询的是：在内连接查询的结果上加上右边表有而左边表没有的记录再加上左边表有而右边表没有的记录呢？

那么这个时候我们就要用全外连接查询了，我们来看一下怎么使用这种查询：

select * from dept full  join emp on dept.id=emp.dept_id;

..........把这条命令敲上去之后发现报错了~~为什么会报错呢？那是因为mysql不支持全外连接查询~呵呵这不是坑爹吗？没错就是坑爹~

想要报复吗？快在文章末尾评论一下随便称赞几句当做对笔者的报答~不对！报复才是！

那么是不是就不能全外连接查询了呢？全外连接查询无非就是左外连接查询与右外连接查询的结合~那么我们接下来使用一个union给两句查询语句合并使用。

select * from dept left join emp on dept.id=emp.dept_id

union

select * from dept right join emp on dept.id=emp.dept_id;

+------+--------+------+------+---------+--------+

| id   | name   | id   | name | dept_id | salary |

+------+--------+------+------+---------+--------+

|    1 | 人事部 |    1 | 曹操 |       1 |   2000 |

|    2 | 后勤部 |    2 | 荀彧 |       2 |   2000 |

|    4 | 行政部 |    4 | 郭嘉 |       4 |   2000 |

|    3 | 财务部 | NULL | NULL |    NULL |   NULL |

| NULL | NULL   |    5 | 荀攸 |    NULL |   2000 |

+------+--------+------+------+---------+--------+

好~“拼凑版全外连接查询”大功告成！