SQL 使用联结(一)

连载的上一篇我们使用子查询来过滤数据、创建计算字段。但我们也发现使用子查询在性能方面的不足,尤其是在创建计算字段的时候,需要对检索出的每天数据都执行一次子查询。

本篇文章,我们将介绍一种新的方式来完成前一篇文章的检索任务,即 联结SQL 最强大的功能就是在查询时联结(JOIN)表。下面,我们首先来了解一下什么是联结?以及为什么我们要使用联结吧!

什么是联结

1、关系表

关系表设计的思想简单来讲,就是把信息分解成多个表,一类数据一个表,表与表之间通过某些共同的值相互关联,这也是关系型数据库名称的来源。

小鱼以如下的两张关系表为例:第一张表为产品信息表 Products,第二张表为供应商信息表 Venders

其中,Venders 表存储供应商信息,每个供应商占一行,具有唯一的标识,即主键 primary key ,可以是供应商 ID 或任何其他唯一值。

Products 表存储产品信息,除了供应商 IDVenders 表的主键)外,Products 表不存储有关供应商的任何信息。而是利用 Venders 表的主键将 Venders 表与 Products 表关联,根据供应商 IDVenders 表中找出某产品供应商的详细信息。

将信息分来存储,然后利用共同的值进行关联,这样做可以带来如下好处:

  • 在产品表中,供应商信息不重复,节省时间和空间;
  • 如果供应商信息发生改变,只需要更新 Venders 表中的一条记录即可,产品表 Products 不需要做任何更改,保证了数据的一致性。

2、为什么使用联结

将信息分开存储后,提高了存储的时间和空间效率,保证了数据的一致性。但是,如果数据存储在多张表中,那如何用一条 SELECT 语句检索出多张表组合后的信息呢?

这就需要使用联结操作啦!我们使用联结在一条 SELECT 语句中关联表,在运行 SQL 时关联表中正确的行,进而联结多张表返回一组输出。

创建 SQL 联结

1、WHERE 子句

使用 SQL 创建联结的第一种方式非常简单,只需要在 WHERE 子句中,指定要联结的表以及关联它们的方式即可。下面的 SQL 返回各供应商提供的产品以及产品价格:

SELECT
    vend_name,
    prod_name,
    prod_price 
FROM
    Vendors,
    Products 
WHERE
    Vendors.vend_id = Products.vend_id;

检索运行结果:

上述 FROM 子句中列出了两个表:VendersProducts ,即我们要联结的两张表。Venders 表和 Products 表通过 WHERE 子句正确地联结,WHERE 子句指示 DBMSVenders 表中的 vend_idProducts 表中的 vend_id 匹配起来。

注:WHERE 子句中使用了完全限定列名,即使用 表名.列名 的语法来指示列。因为两张表都有 vend_id 字段,如果不指定表名,就无法确定是哪张表的 vend_id 字段。

2、笛卡尔积

小鱼在刚开始学习 SQL 的时候,一直觉得用 WHERE 子句建立联结感觉奇奇怪怪的:WHERE 子句不是过滤吗?为什么又要用来指定联结的条件呢?

其实,这一点也不奇怪。在数据库表的定义中,并没有指出 DBMS 如何对表进行联结,联结的建立完全是根据 SELECT 语句创建的。

在联结两张表时,实际上会将第一张表中的每一行与第二张表中的每一行配对,就好比是计算笛卡尔积一样,检索出的函数将会是第一张表中的行数乘以第二张表中的行数。

这时候,WHERE 子句中的过滤条件就至关重要了,只包含匹配给定条件(联结条件)的行。如果忘记 WHERE 子句,结果就是笛卡尔积的形式,而不管它们逻辑上是否能匹配在一起。下面是忘记 WHERE 子句的联结查询:

SELECT
    vend_name,
    prod_name,
    prod_price 
FROM
    Vendors,
    Products;

部分输出截图:

上述返回的数据,用每个供应商匹配了 Products 表中的所有产品,这样的结果显然不是我们想要的。

注:WHERE 子句在联结查询时直观重要,请不要遗漏 WHERE 子句,否则 DBMS 将返回比想要的数据多得多的数据。

3、内联结

上述的联结基于两个表之间的相等测试,称为等值联结,又叫作内联结 Inner Join 。其实,如果使用 WHERE 子句的方式容易遗忘 WHERE 的话,你也可以选择另外一种更明确的语法,来明确指定联结的类型。

SELECT
    vend_name,
    prod_name,
    prod_price 
FROM
    Products
    INNER JOIN Vendors ON Products.vend_id = Vendors.vend_id;

运行结果:

INNER JOIN 声明内联结,表明 ProductsVenders 都只返回满足条件 Products.vend_id = Vendors.vend_id 的联结记录,其中联结的条件使用 ON 子句给出。

4、联结多张表

SQL 不限制一条 SELECT 语句中允许联结的表的数目,创建联结的基本规则也是可以类推的:先列出所有表,然后定义表之间的关联关系。

下面,我们将关联上述三张表,检索订单 20007 中产品的详细信息及产品供应商的名称。

SELECT
    prod_name,
    vend_name,
    prod_price,
    quantity 
FROM
    OrderItems,
    Products,
    Vendors 
WHERE
    OrderItems.order_num = 20007 
    AND OrderItems.prod_id = Products.prod_id 
    AND Products.vend_id = Vendors.vend_id;

检索结果:

上述 SQL 中的 FROM 子句列出了关联的 3 张表,关联条件为 OrderItems.prod_id = Products.prod_idProducts.vend_id = Vendors.vend_id 。也就是说,联结配对结束后,只有满足订单号为 20007 且 OrderItems 表中的产品 IDProducts 表中的产品 ID 相等,并且 Products 表的厂商 IDVenders 表的厂商 ID 也相等,记录才会被保留。

当然了,也可以使用语法更明确的 INNDER JOIN ... ON 语法:

SELECT
    prod_name,
    vend_name,
    prod_price,
    quantity 
FROM
    OrderItems
    INNER JOIN Products ON OrderItems.prod_id = Products.prod_id
    INNER JOIN Vendors ON Products.vend_id = Vendors.vend_id 
WHERE
    order_num = 20007;

最后,我们来回顾一下连载上一篇文章中,使用子查询检索订购了产品 RANG01 的顾客列表,设计的关系表如下:

使用内联结建立查询:

SELECT
    cust_name,
    cust_contact 
FROM
    Customers,
    Orders,
    OrderItems 
WHERE
    Customers.cust_id = Orders.cust_id 
    AND Orders.order_num = OrderItems.order_num 
    AND OrderItems.prod_id = 'RGAN01';

或者

SELECT
    cust_name,
    cust_contact 
FROM
    Customers
    INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
    INNER JOIN OrderItems ON Orders.order_num = OrderItems.order_num 
WHERE
    OrderItems.prod_id = 'RGAN01';

检索运行结果:

总结

联结是 SQL 中非常重要的特性,本节我们只是学习了联结中的一种:等值联结(内联结),连载的下一篇文章中,小鱼将和大家一起学习其他类型的联结创建。

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 213,254评论 6 492
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,875评论 3 387
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 158,682评论 0 348
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,896评论 1 285
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 66,015评论 6 385
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,152评论 1 291
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,208评论 3 412
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,962评论 0 268
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,388评论 1 304
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,700评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,867评论 1 341
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,551评论 4 335
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,186评论 3 317
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,901评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,142评论 1 267
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,689评论 2 362
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,757评论 2 351

推荐阅读更多精彩内容