欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql/mariadb学习记录——连接查询(JOIN)

程序员文章站 2022-03-17 11:55:38
1.内连接 [inner] join: 内连接也叫连接,或者自然连接: 2.左连接 left [outer] join: 先看左连接的语句与查询结果 通过这个结果我们可以发现lastname为Bush没有orderNo,而lastname,firstname属性是来自persons表,orderNo ......
//本文使用的数据表格
//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。