|
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)
|