我们要知道每一区 (region_name) 的营业额 (sales)。 Geography 这个表格告诉我们每一区有哪些店,而 Store_Information 告诉我们每一个店的营业额。若我们要知道每一区的营业额,我们需要将这两个不同表格中的资料串联起来。当我们仔细了解这两个表格后,我们会发现它们可经由一个相同的栏位,store_name,连接起来。我们先将 SQL 句列出, 之后再讨论每一个子句的意义:
SELECT A1.region_name REGION, SUM(A2.Sales) SALES FROM Geography A1, Store_Information A2 WHERE A1.store_name = A2.store_name GROUP BY A1.region_name
等同于:
select a.region_name as "地区",
sum(b.sales) as "总和"
from Geography a inner join Store_Information b
on a.store_name=b.store_name
group by a.region_name
Subquery 查询:(我们可以在一个 SQL 语句中放入另一个 SQL 语句)
SELECT "栏位1" FROM "表格" WHERE "栏位2" [比较运算素] (SELECT "栏位1" FROM "表格" WHERE [条件])
我们要运用 subquery 来找出所有在西部的店的营业额。我们可以用下面的 SQL 来达到我们的目的:
SELECT SUM(Sales) FROM Store_Information WHERE Store_name IN (SELECT store_name FROM Geography WHERE region_name = 'West')
UNION 查询:(目的是将两个 SQL 语句的结果合并起来。从这个角度来看, UNION 跟 JOIN 有些许类似)
[SQL 语句 1] UNION [SQL 语句 2]
select store_name from Geography
union
select store_name from Store_Information
UNION ALL 查询:(UNION 与 UNION ALL 不同在于,一个重复,一个不重复)
select store_name from Geography
union ALLs
select store_name from Store_Information
INTERSECT 查询:(UNION 与 UNION ALL、INTERSECT 不同在于,一个不重复,一个留下重复,一个去掉重复)
select store_name from Geography
INTERSECT
select store_name from Store_Information