//本文使用的数据表格
//persons表中id_p为主键
//orders表中id_o为主键，id_p为外键参考persons表中的id_p
mysql> select * from persons;select * from orders;
+------+----------+-----------+----------------+----------+
| id_p | lastname | firstname | address | city |
+------+----------+-----------+----------------+----------+
| | Adams | John | Oxford Street | London |
| | Bush | George | Fifth Avenue | New York |
| | Carter | Thomas | Changan Street | Beijing |
+------+----------+-----------+----------------+----------+
rows in set (0.05 sec) +------+---------+------+
| id_o | orderNo | id_p |
+------+---------+------+
| | | |
| | | |
| | | |
| | | |
| | | |
+------+---------+------+
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 | |
| Adams | John | |
| Carter | Thomas | |
| Carter | Thomas | |
+----------+-----------+---------+ //使用where达到同样的效果
mysql> select lastname,firstname,orderNo from persons,orders where persons.id_p=orders.id_p;
+----------+-----------+---------+
| lastname | firstname | orderNo |
+----------+-----------+---------+
| Carter | Thomas | |
| Carter | Thomas | |
| Adams | John | |
| Adams | John | |
+----------+-----------+---------+
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 | |
| Adams | John | |
| Bush | George | NULL |
| Carter | Thomas | |
| Carter | Thomas | |
+----------+-----------+---------+
通过这个结果我们可以发现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 | |
| Adams | John | |
| Adams | John | |
| Carter | Thomas | |
| Carter | Thomas | |
+----------+-----------+---------+
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 | |
| Adams | John | |
| Adams | John | |
| Carter | Thomas | |
| Carter | Thomas | |
+----------+-----------+---------+
同理与左连接，结果显示的就是right join 左边表persons表的所有信息，同时把未匹配到的right join右边表orders中的orderNo值置为NULL。
4.全连接full join（不过mysql/mariadb不支持）
全连接则是把两个表的信息全部显示出来，无论两个表的信息匹配与否，未匹配上的信息都置为NULL。