[MYSQL]多表连接的原理

多表连接的方式

//from t1, t2 where . . .
SELECT * FROM 
t1, t2 
WHERE t1.m = t2.m and . . .;

//(inner) join t2 on . . .
SELECT column_name(s) FROM t1
INNER JOIN t2 
ON t1.m = t2.m and t1.m = 2 . . .;

//left join t2 on . . .
SELECT column_name(s) FROM t1
LEFT JOIN t2 
ON t1.m = t2.m

//right join t2 on . . .
SELECT column_name(s) FROM t1
RIGHT JOIN t2 
ON t1.m = t2.m

//full join t2 on . . .
SELECT column_name(s) FROM t1
FULL JOIN t2 
ON t1.m = t2.m and . . .

多表连接的原理

对于两个表连接操作，需要区分驱动表和被驱动表：

• 驱动表：第一个需要查询的表，执行查询的策略与普通单表查询一致；
• 被驱动表：针对上一步骤中从驱动表产生的结果集中的每一条记录，需要分别到被驱动表中查找匹配的记录。

在上面的例子中，驱动表和被驱动表分别是：

1) from t1, t2 where . . .
驱动表: t1或t2	被驱动表: t1或t2

2) (inner) join t2 on . . .
驱动表: t1或t2	被驱动表: t1或t2

3) left join t2 on . . .
驱动表: t1	被驱动表: t2

4) right join t2 on . . .
驱动表: t2	被驱动表: t1

5) full join t2 on . . .
驱动表: t1或t2	被驱动表: t1或t2

一个例子
以下列语句为例，说明（内）连接操作的过程：

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

mysql> SELECT * FROM t1;
+------+------+
| m1   | n1   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
+------+------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+------+
| m2   | n2   |
+------+------+
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
3 rows in set (0.00 sec)

1.假设t1是驱动表，t2是被驱动表。在这个查询中我们指明了这三个过滤条件：

t1.m1 > 1		//仅涉及驱动表的条件， 用于驱动表的查询
t1.m1 = t2.m2	//涉及两个表的条件，   用于被驱动表的查询
t2.n2 < 'd'		//仅涉及被驱动表的条件，用于被驱动表的查询

2.首先要对驱动表t1执行单表查询，其过滤条件是上面三个过滤条件中的t1.m1 > 1，其查询结果为

+------+------+
| m1   | n1   |
+------+------+
|    2 | b    |
|    3 | c    |
+------+------+

3.上一步骤从驱动表中得到了2条记录，分别需要到t2表中查找匹配的记录，即需要查询2次t2表。此时涉及两个表的列的过滤条件t1.m1 = t2.m2就派上用场了：

• 当t1.m1 = 2时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 2，所以此时t2表相当于有了t2.m2 = 2、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询；
• 当t1.m1 = 3时，过滤条件t1.m1 = t2.m2就相当于t2.m2 = 3，所以此时t2表相当于有了t2.m2 = 3、t2.n2 < 'd'这两个过滤条件，然后到t2表中执行单表查询。
  

4.最终的查询结果是：

+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+

内连接与外连接

内连接

SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];
SELECT * FROM t1 , t2 [WHERE 普通过滤条件];

• 驱动表中的记录如果在被驱动表中没有匹配的记录，则不会被加入到结果集；
• 包括：1) from t1, t2 where . . .和2) (inner) join t2 on . . .；
• ON子句和where子句都是可选的；
• ON后面的条件和WHERE后面的条件都是一样的，只需要按照前面所提到的连接原理进行连接即可。
  例如以下几个查询的结果都是一致的：

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';
SELECT * FROM t1  JOIN t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';
SELECT * FROM t1  JOIN t2 ON t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

左外连接

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 连接条件 [WHERE 普通过滤条件];

• 放在左边的表称之为外表或者驱动表，右边的表称之为内表或者被驱动表；
• 必须使用ON子句来指出连接条件，where子句是可选的；
• 首先使用ON子句的条件进行上面的连接过程（驱动表+被驱动表），得到满足ON连接条件的结果集R1；
• 对于驱动表中没有满足ON条件的记录，也加入到结果集R1中形成R2；
• 如果后面有where子句，则再对R2进行筛选形成最终的结果集R。

下面有几个例子：

# 前三条记录满足ON连接条件，后两条记录是驱动表中不满足ON条件的记录，但仍然被加入到最终的结果集中
select * from t1 left join t2 on m1>2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    3 | c    |    2 | b    |
|    3 | c    |    3 | c    |
|    3 | c    |    4 | d    |
|    1 | a    | NULL | NULL |
|    2 | b    | NULL | NULL |
+------+------+------+------+

# 这个查询实际是上一个查询加上WHERE筛选条件的结果
select * from t1 left join t2 on m1>2 where m1 <=2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    1 | a    | NULL | NULL |
|    2 | b    | NULL | NULL |
+------+------+------+------+

# 第一条记录满足ON连接条件，后两条记录是驱动表中不满足ON条件的记录，但仍然被加入到最终的结果集中
select * from t1 left join t2 on m1>2 and m1=m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    3 | c    |    3 | c    |
|    1 | a    | NULL | NULL |
|    2 | b    | NULL | NULL |
+------+------+------+------+

右外连接
与左外连接原理相似。

外连接

• 首先使用ON子句的条件进行上面的连接过程（驱动表+被驱动表），得到满足ON连接条件的结果集R1；
• 对于驱动表和被驱动表中没有满足ON条件的记录，也加入到结果集R1中形成R2；
• 如果后面有where子句，则再对R2进行筛选形成最终的结果集R。