- // 本文使用的数据表格
- //persons 表中 id_p 为主键
- //orders 表中 id_o 为主键, id_p 为外键参考 persons 表中的 id_p
- mysql> select * from persons;select * from orders;
- +------+----------+-----------+----------------+----------+
- | id_p | lastname | firstname | address | city |
- +------+----------+-----------+----------------+----------+
- | 1 | Adams | John | Oxford Street | London |
- | 2 | Bush | George | Fifth Avenue | New York |
- | 3 | Carter | Thomas | Changan Street | Beijing |
- +------+----------+-----------+----------------+----------+
- 3 rows in set (0.05 sec)
- +------+---------+------+
- | id_o | orderNo | id_p |
- +------+---------+------+
- | 1 | 77895 | 3 |
- | 2 | 44678 | 3 |
- | 3 | 22456 | 1 |
- | 4 | 24562 | 1 |
- | 5 | 34764 | 65 |
- +------+---------+------+
- 5 rows in set (0.05 sec)
1. 内连接 [inner] join:
内连接也叫连接, 或者自然连接:
//inner 可删
mysql> select lastname,firstname,orderNo from persons inner join orders on persons.id_p=orders.id_p order by lastname;
- +----------+-----------+---------+
- | lastname | firstname | orderNo |
- +----------+-----------+---------+
- | Adams | John | 24562 |
- | Adams | John | 22456 |
- | Carter | Thomas | 77895 |
- | Carter | Thomas | 44678 |
- +----------+-----------+---------+
- // 使用 where 达到同样的效果
- mysql> select lastname,firstname,orderNo from persons,orders where persons.id_p=orders.id_p;
- +----------+-----------+---------+
- | lastname | firstname | orderNo |
- +----------+-----------+---------+
- | Carter | Thomas | 77895 |
- | Carter | Thomas | 44678 |
- | Adams | John | 22456 |
- | Adams | John | 24562 |
- +----------+-----------+---------+
2. 左连接 left [outer] join:
先看左连接的语句与查询结果
//outer 可删去
mysql> select lastname,firstname,orderNo from persons left join orders on persons.id_p=orders.id_p order by lastname;
- +----------+-----------+---------+
- | lastname | firstname | orderNo |
- +----------+-----------+---------+
- | Adams | John | 24562 |
- | Adams | John | 22456 |
- | Bush | George | NULL |
- | Carter | Thomas | 77895 |
- | Carter | Thomas | 44678 |
- +----------+-----------+---------+
通过这个结果我们可以发现 lastname 为 Bush 没有 orderNo, 而 lastname,firstname 属性是来自 persons 表, orderNo 属性是来自 orders 表可见左连接的作用时把在 left join 左边 persons 表格的所有信息都显示出来, 即使是没有和 left join 右边的表 orders 中字段相匹配的信息也显示出来, 并且给 orderNo 的值填为 NULL.
如果把 left join 两侧的 persons 和 orders 位置互换那么结果显示的就是 left join 左边表 orders 表的所有信息, 同时把未匹配到的 left join 右边表 persons 中的 lastname 与 firstname 值置为 NULL, 这个结果与未交换 join 两个表名位置的右连接的结果一致.
// 将 left join 两侧的 persons 和 orders 位置互换
mysql> select lastname,firstname,orderNo from orders left join persons on persons.id_p=orders.id_p order by lastname;
- +----------+-----------+---------+
- | lastname | firstname | orderNo |
- +----------+-----------+---------+
- | NULL | NULL | 34764 |
- | Adams | John | 24562 |
- | Adams | John | 22456 |
- | Carter | Thomas | 44678 |
- | Carter | Thomas | 77895 |
- +----------+-----------+---------+
3. 右连接 right [outer] join:
//outer 可删去
mysql> select lastname,firstname,orderNo from persons right outer join orders on persons.id_p=orders.id_p order by lastname;
- +----------+-----------+---------+
- | lastname | firstname | orderNo |
- +----------+-----------+---------+
- | NULL | NULL | 34764 |
- | Adams | John | 22456 |
- | Adams | John | 24562 |
- | Carter | Thomas | 77895 |
- | Carter | Thomas | 44678 |
- +----------+-----------+---------+
同理与左连接, 结果显示的就是 right join 左边表 persons 表的所有信息, 同时把未匹配到的 right join 右边表 orders 中的 orderNo 值置为 NULL.
4. 全连接 full join(不过 mysql/mariadb 不支持)
全连接则是把两个表的信息全部显示出来, 无论两个表的信息匹配与否, 未匹配上的信息都置为 NULL.
来源: http://www.bubuko.com/infodetail-2589091.html