一个很少见却很有用的SQL功能,一般人都不告诉你……

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

 

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

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
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);

 

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

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
SELECT first_name, last_nameFROM actorUNION ALLSELECT first_name, last_nameFROM customerUNION ALLSELECT first_name, last_nameFROM staffORDER BY first_name, last_name

 

结果可能看起来像这样:

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
|first_name|last_name||----------|---------||AARON     |SELBY    ||ADAM      |GOOCH    ||ADAM      |GRANT    ||ADAM      |HOPPER   ||ADRIAN    |CLARY    ||AGNES     |BISHOP   ||AL        |GARLAND  ||ALAN      |DREYFUSS ||...       |...      |

 

一、使用CORRESPONDING

 

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

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
SELECT *FROM actorUNION ALL CORRESPONDINGSELECT *FROM customerUNION ALL CORRESPONDINGSELECT *FROM staffORDER BY first_name, last_name

 

其结果是这样的:

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
|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,运行这个查询:

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
SELECT column_nameFROM information_schema.columnsWHERE table_name = 'ACTOR'INTERSECTSELECT column_nameFROM information_schema.columnsWHERE table_name = 'CUSTOMER'INTERSECTSELECT column_nameFROM information_schema.columnsWHERE table_name = 'STAFF'

 

你得到的正是这3个列:

 

  •  
  •  
  •  
  •  
  •  
|COLUMN_NAME||-----------||FIRST_NAME ||LAST_NAME  ||LAST_UPDATE|

 

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

 

二、使用CORRESPONDING BY

 

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

 

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

 

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

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
SELECT *FROM actorUNION ALL CORRESPONDING BY (first_name, last_name)SELECT *FROM customerUNION ALL CORRESPONDING BY (first_name, last_name)SELECT *FROM staffORDER BY first_name, last_name;

 

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

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
|first_name|last_name||----------|---------||AARON     |SELBY    ||ADAM      |GOOCH    ||ADAM      |GRANT    ||ADAM      |HOPPER   ||ADRIAN    |CLARY    ||AGNES     |BISHOP   ||AL        |GARLAND  ||ALAN      |DREYFUSS ||...       |...      |

 

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

 

  •  
  •  
  •  
  •  
  •  
  •  
SELECT *FROM actorINTERSECT CORRESPONDING BY (first_name, last_name)SELECT *FROM customerORDER BY first_name, last_name;

 

制作:

 

  •  
  •  
  •  
|first_name|last_name||----------|---------||JENNIFER  |DAVIS    |

 

三、其他方言

 

我以前没有在其他方言中多次遇到过这种语法。也许,它在将来会运到PostgreSQL中。Vik Fearing已经在做一个分支了:

 

我有一个git分支,已经很久没有工作了

- Vik Fearing(@pg_xocolatl)2022年1月13日

 

jOOQ可能很快会在API/解析器/翻译器中支持它。

 

作者丨DebugUsery
来源丨网址:https://juejin.cn/post/7126351838349099022
dbaplus社群欢迎广大技术人员投稿,投稿邮箱:editor@dbaplus.cn

活动预告