之前我们看到的左连接 (left join),又称内部连接 (inner join)。在这个情况下,要两个表格内都有同样的值,那一笔资料才会被选出。那如果我们想要列出一个表格中每一笔的资料,无论它的值在另一个表格中有没有出现,那该怎么办呢?在这个时候,我们就需要用到 OUTER JOIN (外部连接) 的指令。
内连接: 只连接匹配的行
左外连接: 包含左边表的全部行(不管右边的表中是否存在与它们匹配的行),以及右边表中全部匹配的行
右外连接: 包含右边表的全部行(不管左边的表中是否存在与它们匹配的行),以及左边表中全部匹配的行
/*==============================================================*/
/* DBMS name: MySQL 5.0 */
/* Created on: 2012/8/19 8:45:30 */
/*==============================================================*/
drop table if exists store_information;
drop table if exists geography;
/*==============================================================*/
/* Table: store_information */
/*==============================================================*/
create table store_information
(
no int not null,
store_name varchar(20),
sales decimal(10,2),
date date,
primary key (no)
);
/*==============================================================*/
/* Table: geography */
/*==============================================================*/
create table geography
(
no int not null,
region_name varchar(20),
store_name varchar(20),
primary key (no)
);
delete from store_information;
delete from geography;
insert store_information(no, store_name, sales, date) values(1, "Los Angeles", 150.2, '2008.12.01');
insert store_information(no, store_name, sales, date) values(2, "San Diego", 250.3, '2008.01.01');
insert store_information(no, store_name, sales, date) values(3, "Los Angeles", 20.2, '2008.02.01');
insert store_information(no, store_name, sales, date) values(4, "Boston", 700.2, '2008.06.11');
insert store_information(no, store_name, sales, date) values(5, "Guangzhou", 790.25, '2012.06.11');
insert geography(no, region_name, store_name) values(1, "East", "Boston");
insert geography(no, region_name, store_name) values(2, "East", "New York");
insert geography(no, region_name, store_name) values(3, "West", "Los Angeles");
insert geography(no, region_name, store_name) values(4, "West", "San Diego");
select * from store_information;
select * from geography;
mysql> select * from store_information;
+----+-------------+--------+------------+
| no | store_name | sales | date |
+----+-------------+--------+------------+
| 1 | Los Angeles | 150.20 | 2008-12-01 |
| 2 | San Diego | 250.30 | 2008-01-01 |
| 3 | Los Angeles | 20.20 | 2008-02-01 |
| 4 | Boston | 700.20 | 2008-06-11 |
| 5 | Guangzhou | 790.25 | 2012-06-11 |
+----+-------------+--------+------------+
5 rows in set (0.00 sec)
mysql> select * from geography;
+----+-------------+-------------+
| no | region_name | store_name |
+----+-------------+-------------+
| 1 | East | Boston |
| 2 | East | New York |
| 3 | West | Los Angeles |
| 4 | West | San Diego |
+----+-------------+-------------+
/* 右外连接 */
select a1.store_name, a2.sales sales
from geography a1 right outer join store_information a2
on a1.store_name = a2.store_name
+-------------+--------+
| store_name | sales |
+-------------+--------+
| Los Angeles | 150.20 |
| San Diego | 250.30 |
| Los Angeles | 20.20 |
| Boston | 700.20 |
| NULL | 790.25 |
+-------------+--------+
/* 左外连接 */
select a1.store_name, a2.sales sales
from geography a1 left outer join store_information a2
on a1.store_name = a2.store_name
+-------------+--------+
| store_name | sales |
+-------------+--------+
| Boston | 700.20 |
| New York | NULL |
| Los Angeles | 150.20 |
| Los Angeles | 20.20 |
| San Diego | 250.30 |
+-------------+--------+
/* 每一间店的营业额 */
select a1.store_name, sum(a2.sales) sales
from geography a1 right outer join store_information a2
on a1.store_name = a2.store_name
group by a1.store_name