MySQL基础之左连接、右连接、内连接、外连接

有2张表 t1 和 t2:

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

mysql> select * from t2;
+------+------+
| a    | b    |
+------+------+
|    1 |  101 |
|    2 |  102 |
|    4 | NULL |
+------+------+
3 rows in set (0.00 sec)

左连接：

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

右连接：

mysql> select * from t1 right join t2 on t1.a = t2.a;
+------+------+------+
| a    | a    | b    |
+------+------+------+
|    1 |    1 |  101 |
|    2 |    2 |  102 |
| NULL |    4 | NULL |
+------+------+------+
3 rows in set (0.00 sec)

内连接：

mysql> select * from t1  join t2 on t1.a = t2.a;
+------+------+------+
| a    | a    | b    |
+------+------+------+
|    1 |    1 |  101 |
|    2 |    2 |  102 |
+------+------+------+
2 rows in set (0.01 sec)

mysql> select * from t1 inner join t2 on t1.a = t2.a;
+------+------+------+
| a    | a    | b    |
+------+------+------+
|    1 |    1 |  101 |
|    2 |    2 |  102 |
+------+------+------+
2 rows in set (0.00 sec)

外连接：

mysql> select * from t1,t2;
+------+------+------+
| a    | a    | b    |
+------+------+------+
|    1 |    1 |  101 |
|    2 |    1 |  101 |
|    3 |    1 |  101 |
|    1 |    2 |  102 |
|    2 |    2 |  102 |
|    3 |    2 |  102 |
|    1 |    4 | NULL |
|    2 |    4 | NULL |
|    3 |    4 | NULL |
+------+------+------+
9 rows in set (0.00 sec)

再备注一次：连接方式，只和on相关，与where条件无关，where是在连接后的数据过滤时生效