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 = 1 這條記錄並沒有出現在結果集裡。但是如果一定要出現在結果集裡，怎麼辦？用外連線。

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)

被連線表中間接指定某一列不為 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. 基於規則的優化