数据库mysql自然连接_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)`