SQL - 连接表（多表查询）

1、创建简单连接

现在有这样一个需求，我们要求返回员工名字和所在部门的名字，在设计数据库时，员工信息是存储在员工表，而部门信息是存储在部门表的，那么我们需要同时这回这两个字段，就要使用联结两张表进行查询。

SELECT tbl_dept.deptName, tbl_emp.name
FROM tbl_dept, tbl_emp
WHERE tbl_dept.id = tbl_emp.deptId

分析：SELECT语句与前面所有语句一样指定要检索的列。不过这里最好使用完全限定列名，因为是在两张表之间进行查询，那么查询的列名就有可能会一样的情况，产生歧义达不到我们想要的数据，WHERE子句同理。

注意：在联结两个表时，实际上做的是将第一个表中的每一行与第二个表中的每一行配对。如果我们没有写WHERE子句，那么将会返回去全部数据，即有两张表（假设联结两张表，实际上可以联结多张表）的行数相乘。假设a表有10条数据，b表有5条数据，就会返回50条数据。也称为笛卡儿积，具体可以百度。

2、内连接

前面我们所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。其实，对于这种还有另一种写法：

SELECT tbl_dept.deptName, tbl_emp.name
FROM tbl_dept INNER JOIN tbl_emp
on tbl_dept.id = tbl_emp.deptId

分析：此语句中的SELECT与前面的SELECT语句相同，但FROM子句不同。这里，两个表之间的关系是FROM子句的组成部分，以INNER JOIN指定。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。

3、自连接

自连接可以看成内连接的一种，只是连接的表是自身而已，比如一张员工表，包含员工姓名和员工所属部门，要找出与张三处在同一部门的所有员工姓名。

SELECT el.name
FROM tbl_emp AS el INNER JOIN tbl_emp AS e2
ON el.deptId = e2.deptId
      AND e2.name = "张三"

分析：此查询中需要的两个表实际上是相同的表，因此tbl_emp表在FROM子句中出现了两次。这虽然没什么问题，但是语句会引起歧义，所以就得指定别名，格式：真实表名 AS 别名。

4、自然连接

自然连接无需指定连接列，SQL会检查两个表中是否相同名称的列，且假设他们在连接条件中使用，并且在连接条件中仅包含一个连接列。不允许使用ON语句，不允许指定显示列，显示列只能用*表示（ORACLE环境下测试的）。对于每种连接类型（除了交叉连接外），均可指定NATURAL

5、外连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接。

SELECT *
FROM tbl_dept NATURAL INNER JOIN tbl_emp;

5.1 左外连接

左外连接就是保留左表没有关联的行。

SELECT tbl_emp.name,tbl_dept.deptName
FROM tbl_dept LEFT OUTER JOIN tbl_emp
ON tbl_emp.deptId = tbl_dept.id

分析：这条sql会查出tbl_emp表的所有数据，然后每一条与tbl_dept表的所有数据进行匹配，当ON子句生效时，将返回数据。类似的外连接还有右外连接，与左外连接相反，可自行测试。

5.2 全外连接

MySQL是不支持全外的连接的，这里给出的写法适合Oracle和DB2。但是可以通过左外和右外求合集来获取全外连接的查询结果。

SELECT tbl_emp.name,tbl_dept.deptName
FROM tbl_dept FULL OUTER JOIN tbl_emp
ON tbl_emp.deptId = tbl_dept.id