sql语句，左、右、内连接之间的区别
[*] left join(左联接)： 返回包括左表中的所有记录和右表中联结字段相等的记录。
right join(右联接)： 返回包括右表中的所有记录和左表中联结字段相等的记录。
inner join(等值连接)： 只返回两个表中联结字段相等的行。
mysql> select * from customers;
+----+-------+------+
|> +----+-------+------+
|1 | tom | 21 |
|3 | Mike| 24 |
|4 | Jack| 30 |
|5 | Linda | 25 |
|6 | Tom | NULL |
+----+-------+------+
mysql> select * from orders;
+----+----------------+-------+-------------+
|> +----+----------------+-------+-------------+
|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;
+----+-------------+-------+----------+----------------+
|> +----+-------------+-------+----------+----------------+
|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;
+------+-------------+-------+----------+----------------+
|> +------+-------------+-------+----------+----------------+
| 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)
页:
[1]