自然连接
-
概念
- 自动判断条件连接，判断的条件是依据同名字段
-
小结
- 表连接是通过同名字段来连接的
- 如果没有同名字段就返回笛卡尔积
- 同名的连接字段只显示一个,并且将该字段放在最前面
自然内连接(natural join)
MariaDB [sel]> select * from grades natural join resume;
+-------+---------+------+----+-----------+
| name | chinese | math | id | skill |
+-------+---------+------+----+-----------+
| Sunny | 93 | 96 | 1 | php |
| Jerry | 97 | 91 | 3 | php,mysql |
+-------+---------+------+----+-----------+
# `2 rows in set (0.023 sec)`
MariaDB [sel]> select * from grades;
+-------+---------+------+
| name | chinese | math |
+-------+---------+------+
| Sunny | 93 | 96 |
| Jerry | 97 | 91 |
| Marry | 95 | 94 |
| Tommy | 98 | 94 |
+-------+---------+------+
# `4 rows in set (0.000 sec)`
MariaDB [sel]> select * from resume;
+----+-------+-----------+
| id | name | skill |
+----+-------+-----------+
| 1 | Sunny | php |
| 2 | Kimmy | php |
| 3 | Jerry | php,mysql |
+----+-------+-----------+
# `3 rows in set (0.000 sec)`
自然左外连接(natural left join)
MariaDB [sel]> select * from grades natural left join resume;
+-------+---------+------+------+-----------+
| name | chinese | math | id | skill |
+-------+---------+------+------+-----------+
| Sunny | 93 | 96 | 1 | php |
| Jerry | 97 | 91 | 3 | php,mysql |
| Marry | 95 | 94 | NULL | NULL |
| Tommy | 98 | 94 | NULL | NULL |
+-------+---------+------+------+-----------+
# `4 rows in set (0.001 sec)`
自然右外连接(natural right join)
MariaDB [sel]> select * from grades natural right join resume;
+-------+----+-----------+---------+------+
| name | id | skill | chinese | math |
+-------+----+-----------+---------+------+
| Sunny | 1 | php | 93 | 96 |
| Jerry | 3 | php,mysql | 97 | 91 |
| Kimmy | 2 | php | NULL | NULL |
+-------+----+-----------+---------+------+
# `3 rows in set (0.000 sec)`