SQL外连接

之前我们看到的左连接 (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