冰凌汇编

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 45|回复: 0
收起左侧

[关系数据库] 一个很少见但很有用的SQL功能

[复制链接]
JackStar 发表于 2022-8-12 22:57:44

我最近偶然发现了一个标准的SQL特性,令我惊讶的是,这个特性在HSQLDB中实现了。这个关键字是CORRESPONDING ,它可以和所有的集合操作一起使用,包括UNION 、INTERSECT 、和EXCEPT 。

让我们来看看sakila数据库。它有3个表,里面都是人:

[SQL] 纯文本查看 复制代码
CREATE TABLE actor (
    actor_id integer NOT NULL PRIMARY KEY,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    last_update timestamp
);
CREATE TABLE customer (
    customer_id integer NOT NULL PRIMARY KEY,
    store_id smallint NOT NULL,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    email varchar(50),
    address_id smallint NOT NULL,
    create_date date NOT NULL,
    last_update timestamp,
    active boolean
);
CREATE TABLE staff (
    staff_id integer NOT NULL PRIMARY KEY,
    first_name varchar(45) NOT NULL,
    last_name varchar(45) NOT NULL,
    address_id smallint NOT NULL,
    email varchar(50),
    store_id smallint NOT NULL,
    active boolean NOT NULL,
    username varchar(16) NOT NULL,
    password varchar(40),
    last_update timestamp,
    picture blob
);
复制代码

相似,但不相同。如果我们想从我们的数据库中获得所有的 "人 "呢?在任何普通的数据库产品中,有一种方法可以做到这一点:

[SQL] 纯文本查看 复制代码
SELECT first_name, last_name
FROM actor
UNION ALL
SELECT first_name, last_name
FROM customer
UNION ALL
SELECT first_name, last_name
FROM staff
ORDER BY first_name, last_name
复制代码

结果可能看起来像这样:

[SQL] 纯文本查看 复制代码
|first_name|last_name|
|----------|---------|
|AARON     |SELBY    |
|ADAM      |GOOCH    |
|ADAM      |GRANT    |
|ADAM      |HOPPER   |
|ADRIAN    |CLARY    |
|AGNES     |BISHOP   |
|AL        |GARLAND  |
|ALAN      |DREYFUSS |
|...       |...      |
复制代码


使用CORRESPONDING

现在,在HSQLDB中,以及在标准SQL中,你可以使用CORRESPONDING 来完成这种任务。比如说:

[SQL] 纯文本查看 复制代码
SELECT *
FROM actor
UNION ALL CORRESPONDING
SELECT *
FROM customer
UNION ALL CORRESPONDING
SELECT *
FROM staff
ORDER BY first_name, last_name
复制代码

其结果是这样的:

[SQL] 纯文本查看 复制代码
|first_name|last_name|last_update            |
|----------|---------|-----------------------|
|AARON     |SELBY    |2006-02-15 04:57:20.000|
|ADAM      |GOOCH    |2006-02-15 04:57:20.000|
|ADAM      |GRANT    |2006-02-15 04:34:33.000|
|ADAM      |HOPPER   |2006-02-15 04:34:33.000|
|ADRIAN    |CLARY    |2006-02-15 04:57:20.000|
|AGNES     |BISHOP   |2006-02-15 04:57:20.000|
|AL        |GARLAND  |2006-02-15 04:34:33.000|
|ALAN      |DREYFUSS |2006-02-15 04:34:33.000|
|...       |...      |...                    |
复制代码

那么,发生了什么?列FIRST_NAME,LAST_NAME, 和LAST_UPDATE 是这三个表所共有的。换句话说,如果你针对HSQLDB中的INFORMATION_SCHEMA ,运行这个查询:

[SQL] 纯文本查看 复制代码
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'ACTOR'
INTERSECT
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'CUSTOMER'
INTERSECT
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'STAFF'
复制代码

你得到的正是这3个列:

[SQL] 纯文本查看 复制代码
|COLUMN_NAME|
|-----------|
|FIRST_NAME |
|LAST_NAME  |
|LAST_UPDATE|
复制代码

换句话说,CORRESPONDING ,在集合操作的子查询中创建列的交集(即 "共享列"),投影这些,并应用该投影的集合操作。在某种程度上,这类似于一个 [NATURAL JOIN](https://blog.jooq.org/impress-your-coworkers-with-a-sql-natural-full-outer-join/),后者也试图找到列的交集以产生一个连接谓词。然而,NATURAL JOIN ,然后投影所有的列(或列的联合),而不仅仅是共享的列。


使用CORRESPONDING BY

就像NATURAL JOIN ,这是个有风险的操作。只要一个子查询改变了它的投影(例如,由于表的列重命名),所有这些查询的结果也会改变,甚至可能不会产生语法错误,只是结果不同。

事实上,在上面的例子中,我们可能根本不关心那个LAST_UPDATE 列。它被意外地包含在UNION ALL 的集合操作中,就像NATURAL JOIN 会意外地使用LAST_UPDATE 来连接一样。

对于连接,我们可以使用JOIN .. USING (first_name, last_name) ,至少指定我们想通过哪一个共享列名来连接这两个表。使用CORRESPONDING ,我们可以为同样的目的提供可选的BY 子句:

[SQL] 纯文本查看 复制代码
SELECT *
FROM actor
UNION ALL CORRESPONDING BY (first_name, last_name)
SELECT *
FROM customer
UNION ALL CORRESPONDING BY (first_name, last_name)
SELECT *
FROM staff
ORDER BY first_name, last_name;
复制代码

现在,这只产生了两个想要的列:

[SQL] 纯文本查看 复制代码
|first_name|last_name|
|----------|---------|
|AARON     |SELBY    |
|ADAM      |GOOCH    |
|ADAM      |GRANT    |
|ADAM      |HOPPER   |
|ADRIAN    |CLARY    |
|AGNES     |BISHOP   |
|AL        |GARLAND  |
|ALAN      |DREYFUSS |
|...       |...      |
复制代码

事实上,这样一来,我们甚至可以有意义地使用INTERSECT和EXCEPT的语法,例如,找到与某个演员共享名字的客户:

[SQL] 纯文本查看 复制代码
SELECT *
FROM actor
INTERSECT CORRESPONDING BY (first_name, last_name)
SELECT *
FROM customer
ORDER BY first_name, last_name;
复制代码

制作:

[SQL] 纯文本查看 复制代码
|first_name|last_name|
|----------|---------|
|JENNIFER  |DAVIS    |
复制代码



本帖最后由 JackStar 于 2022-8-12 22:59 编辑
冰凌汇编免责声明
以上内容均来自网友转发或原创,如存在侵权请发送到站方邮件9003554@qq.com处理。
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|小黑屋|站点统计|Archiver|小黑屋|RSS|冰凌汇编 ( 滇ICP备2022002049号 滇公网安备 53032102000029号)|网站地图

GMT+8, 2022-10-6 20:52 , Processed in 0.121407 second(s), 8 queries , Redis On.

冰凌汇编 - 建立于2021年12月20日

Powered by Discuz! © 2001-2022 Comsenz Inc.

快速回复 返回顶部 返回列表