sql 内连接和外连接

测试表

t1 表

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `m1` int(11) DEFAULT NULL,
  `n1` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

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

t2 表

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `m2` int(11) DEFAULT NULL,
  `n2` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

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

inner join

mysql> select * from t1 inner join t2 on t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

可以看到对于 t1 表来说，m1 = 1, n1 = 'a' 这条记录并没有出现在结果集里。但是如果一定要出现在结果集里，怎么办？用外连接。

left outer join

mysql> select * from t1 left  join t2 on t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
|    1 | a    | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

外连接转化为内连接

在外连接查询中，指定的 WHERE 子句中包含被驱动表中的列不为 NULL 值的条件称之为空值拒绝（英文名：reject-NULL）。

被连接表中直接指定某一列不为NULL

mysql> select * from t1 left  join t2 on t1.m1 = t2.m2 where t2.m2 is not null;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

指定 t2.m2 不是 null。此时的 left join 和 inner join 完全一样。

mysql> select * from t1 left  join t2 on t1.m1 = t2.m2 where t2.n2 is not null;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

指定 t2.n2 不是 null。此时的 left join 和 inner join 结果完全一样。也就是这个 left join 可以转化为 inner join，转化为 inner join 就和表的连接顺序无关了。

被连接表中间接指定某一列不为 NULL

1. left join + where 语句指定不为 NULL

mysql> select * from t1 left  join t2 on t1.m1 = t2.m2 where t2.n2 = 'b';
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
+------+------+------+------+
1 row in set (0.00 sec)

2. 此时可以转化为 inner join。

mysql> select * from t1 inner  join t2 on t1.m1 = t2.m2 where t2.n2 = 'b';
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
+------+------+------+------+
1 row in set (0.00 sec)

Reference

1. 基于规则的优化