MySQL连接操作

MySQL 连接可分为内连接、外连接和自然连接，外连接又分为左连接和右连接。

1. 内连接

可以通过 inner join 关键字或通过逗号分隔多张表来实现内连接操作。

当不指定连接条件时，内连接的结果是输入表的笛卡尔积。

输入表：

MariaDB [mydb]> select * from t1;
+------+------+------+
| c1   | c2   | c3   |
+------+------+------+
|    1 |    2 |    3 |
|    4 |    5 |    6 |
+------+------+------+
2 rows in set (0.00 sec)

MariaDB [mydb]> select * from t2;
+------+------+------+
| c1   | c2   | c4   |
+------+------+------+
|   10 |   20 |   40 |
|   40 |   50 |   70 |
+------+------+------+
2 rows in set (0.00 sec)

内连接：

MariaDB [mydb]> select * from t1 inner join t2;
+------+------+------+------+------+------+
| c1   | c2   | c3   | c1   | c2   | c4   |
+------+------+------+------+------+------+
|    1 |    2 |    3 |   10 |   20 |   40 |
|    4 |    5 |    6 |   10 |   20 |   40 |
|    1 |    2 |    3 |   40 |   50 |   70 |
|    4 |    5 |    6 |   40 |   50 |   70 |
+------+------+------+------+------+------+
4 rows in set (0.00 sec)

MariaDB [mydb]> select * from t1, t2;
+------+------+------+------+------+------+
| c1   | c2   | c3   | c1   | c2   | c4   |
+------+------+------+------+------+------+
|    1 |    2 |    3 |   10 |   20 |   40 |
|    4 |    5 |    6 |   10 |   20 |   40 |
|    1 |    2 |    3 |   40 |   50 |   70 |
|    4 |    5 |    6 |   40 |   50 |   70 |
+------+------+------+------+------+------+
4 rows in set (0.00 sec)

可以通过 on 从句来指定连接条件：

MariaDB [mydb]> select * from t1 inner join t2 on t2.c1=t1.c1*10;
+------+------+------+------+------+------+
| c1   | c2   | c3   | c1   | c2   | c4   |
+------+------+------+------+------+------+
|    1 |    2 |    3 |   10 |   20 |   40 |
|    4 |    5 |    6 |   40 |   50 |   70 |
+------+------+------+------+------+------+
2 rows in set (0.00 sec)

on 指定如何连接表，where 限定哪些行可以进入结果集。

using 从句指定的多个列必须存在于每个输入表中，连接时会比较对应的列，值相等时才会连接。

MariaDB [mydb]> insert into t2 values (1, 2, 40);
Query OK, 1 row affected (0.00 sec)

MariaDB [mydb]> select * from t1 inner join t2 using(c1, c2);
+------+------+------+------+
| c1   | c2   | c3   | c4   |
+------+------+------+------+
|    1 |    2 |    3 |   40 |
+------+------+------+------+
1 row in set (0.00 sec)

2. 外连接

在执行左连接时，如果右表不存在使条件成立的行，则该行所有列将被设为 NULL（右连接类似）。同样的情况在内连接中，则是不连接该行。

MariaDB [mydb]> select * from t1 left join t2 on t1.c1=t2.c1;
+------+------+------+------+------+------+
| c1   | c2   | c3   | c1   | c2   | c4   |
+------+------+------+------+------+------+
|    1 |    2 |    3 |    1 |    2 |   40 |
|    4 |    5 |    6 | NULL | NULL | NULL |
+------+------+------+------+------+------+
2 rows in set (0.00 sec)

MariaDB [mydb]> select * from t1 inner join t2 on t1.c1=t2.c1;
+------+------+------+------+------+------+
| c1   | c2   | c3   | c1   | c2   | c4   |
+------+------+------+------+------+------+
|    1 |    2 |    3 |    1 |    2 |   40 |
+------+------+------+------+------+------+
1 row in set (0.00 sec)

左连接过程中，左表的每一行会根据指定的条件与右表的每一行进行比较，如果条件成立，则连接它们；如果右表中不存在使条件成立的行，则左表的该行会与所有列设都为 NULL 的行进行连接。

MariaDB [mydb]> insert into t2 values (1, 2, 80);
Query OK, 1 row affected (0.00 sec)

MariaDB [mydb]> select * from t1 left join t2 on t1.c1=t2.c1;
+------+------+------+------+------+------+
| c1   | c2   | c3   | c1   | c2   | c4   |
+------+------+------+------+------+------+
|    1 |    2 |    3 |    1 |    2 |   40 |
|    1 |    2 |    3 |    1 |    2 |   80 |
|    4 |    5 |    6 | NULL | NULL | NULL |
+------+------+------+------+------+------+
3 rows in set (0.00 sec)

左连接结果集的行数至少等于左表的行数。

3. 自然连接

natural join/natural left join 等价于 inner join/left join 中使用 using 指定输入表中所有共同存在的列。

MariaDB [mydb]> select * from t1 natural join t2;
+------+------+------+------+
| c1   | c2   | c3   | c4   |
+------+------+------+------+
|    1 |    2 |    3 |   40 |
|    1 |    2 |    3 |   80 |
+------+------+------+------+
2 rows in set (0.00 sec)

MariaDB [mydb]> select * from t1 inner join t2 using(c1, c2);
+------+------+------+------+
| c1   | c2   | c3   | c4   |
+------+------+------+------+
|    1 |    2 |    3 |   40 |
|    1 |    2 |    3 |   80 |
+------+------+------+------+
2 rows in set (0.00 sec)