MySQL数据库之多表查询natural join自然连接

自然连接

• 概念

  • 自动判断条件连接，判断的条件是依据同名字段

• 小结

  • 表连接是通过同名字段来连接的
  • 如果没有同名字段就返回笛卡尔积
  • 同名的连接字段只显示一个,并且将该字段放在最前面

自然内连接(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)`