sql语句左外连接和右外连接的区别
来源:互联网 发布:孙鑫的java视频教程 编辑:程序博客网 时间:2024/05/18 07:42
mysql> select * from customers;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 21 |
| 3 | Mike | 24 |
| 4 | Jack | 30 |
| 5 | Linda | 25 |
| 6 | Tom | NULL |
+----+-------+------+
mysql> select * from orders;
+----+----------------+-------+-------------+
| id | order_number | price | customer_id |
+----+----------------+-------+-------------+
| 1 | Tom_Order001 | 100 | 1 |
| 2 | Tom_Order002 | 200 | 1 |
| 3 | Tom_Order003 | 300 | 1 |
| 4 | Mike_Order001 | 100 | 3 |
| 5 | Jack_Order001 | 200 | 4 |
| 6 | Linda_Order001 | 100 | 5 |
| 7 | UnknownOrder | 200 | NULL |
+----+----------------+-------+-------------+
左外连接:
如果用左外连接的话,指查询出来的是在右表中没有对应左表中的记录以及符合条件(c.id=o.customer_id)的数据,比如:
customers为左表,orders为右表
select c.id,o.customer_id,c.name,o.id order_id,order_number from customers c left outer join orders o on c.id=o.customer_id;
这样的结果显示的是在左表中的某些用户在orders表中没有对应的订单
mysql> select c.id,o.customer_id,c.name,o.id order_id,order_number from customers c left outer join orders o on c.id=o.customer_id;
+----+-------------+-------+----------+----------------+
| id | customer_id | name | order_id | order_number |
+----+-------------+-------+----------+----------------+
| 1 | 1 | tom | 1 | Tom_Order001 |
| 1 | 1 | tom | 2 | Tom_Order002 |
| 1 | 1 | tom | 3 | Tom_Order003 |
| 3 | 3 | Mike | 4 | Mike_Order001 |
| 4 | 4 | Jack | 5 | Jack_Order001 |
| 5 | 5 | Linda | 6 | Linda_Order001 |
| 6 | NULL | Tom | NULL | NULL |
+----+-------------+-------+----------+----------------+
7 rows in set (0.00 sec)
右外连接:
正好和左外连接相反,查询出来的结果是在左表中没有对应的项和以及符合条件(c.id=o.customer_id)的数据
mysql> select c.id,o.customer_id,c.name,o.id order_id,order_number from customers c right outer join orders o on c.id=o.customer_id;
+------+-------------+-------+----------+----------------+
| id | customer_id | name | order_id | order_number |
+------+-------------+-------+----------+----------------+
| 1 | 1 | tom | 1 | Tom_Order001 |
| 1 | 1 | tom | 2 | Tom_Order002 |
| 1 | 1 | tom | 3 | Tom_Order003 |
| 3 | 3 | Mike | 4 | Mike_Order001 |
| 4 | 4 | Jack | 5 | Jack_Order001 |
| 5 | 5 | Linda | 6 | Linda_Order001 |
| NULL | NULL | NULL | 7 | UnknownOrder |
+------+-------------+-------+----------+----------------+
7 rows in set (0.00 sec)
=============================================================================
mysql> select name,age,sum(price) from customers c left join orders o on c.id=o.customer_id group by c.id;
+-------+------+------------+
| name | age | sum(price) |
+-------+------+------------+
| tom | 21 | 600 |
| Mike | 24 | 100 |
| Jack | 30 | 200 |
| Linda | 25 | 100 |
| Tom | NULL | NULL |
+-------+------+------------+
5 rows in set (0.00 sec)
- sql左外连接和右外连接的区别
- sql语句左外连接和右外连接的区别
- SQL 左外连接,右外连接,全连接,内连接 4种连接的区别
- SQL 左外连接,右外连接,全连接,内连接 4种连接的区别
- SQL 左外连接,右外连接,全连接,内连接 4种连接的区别 .
- SQL 左外连接,右外连接,全连接,内连接 4种连接的区别
- SQL 左外连接,右外连接,全连接,内连接 4种连接的区别
- SQL语句的内连接、左外连接、右外连接、全连接
- sql中的左连接和右连接的区别
- mysql 数据库查询语句 左外连接 右外连接 和内连接区别
- sql的左连接和右连接
- 左连接和右连接的区别
- SQL左连接右连接内连接区别和总结
- 左连接 和右链接的区别,内连接和外连接的却别!!
- sql语句左外连接与右外连接的写法
- 关于多表查询sql常用的连接语句:左外连接、右外连接、内连接
- SQL语句中的左连接、右连接、交叉连接、全外连接
- 数据库中的左外连接右外连接和内连接的区别?
- 工作时啥人听啥音乐(超搞笑)
- Key Changes From WebWork 2
- 操作系统概念(第六版) 读书笔记6
- 老森的梦,我的梦,有关预言。 (连载1)
- 忙碌的五一
- sql语句左外连接和右外连接的区别
- 音狐990 (YH990) 视频mp3中播放视频的解决方案(原创 by afxid)
- vc 下可以运行在设置项Project->Settings->Debug->Program arguments填参数
- c# 连接各类数据库的集合类
- C# 中常用数据类型与控件类型的命名规则
- u-boot代码分析
- 切身感受Spring AOP的魅力
- 明朝时期中国取得的人类军事史的第一
- Struts2小结