我最近偶然發(fā)現(xiàn)了一個(gè)標(biāo)準(zhǔn)的SQL特性，令我驚訝的是，這個(gè)特性在HSQLDB中實(shí)現(xiàn)了。這個(gè)關(guān)鍵字是CORRESPONDING ，它可以和所有的集合操作一起使用，包括UNION 、INTERSECT 、和EXCEPT 。

讓我們來看看sakila數(shù)據(jù)庫。它有3個(gè)表，里面都是人:

sql復(fù)制代碼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 ); ?

相似，但不相同。如果我們想從我們的數(shù)據(jù)庫中獲得所有的 "人 "呢？在任何普通的數(shù)據(jù)庫產(chǎn)品中，有一種方法可以做到這一點(diǎn):

sql復(fù)制代碼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 ?

結(jié)果可能看起來像這樣:

sql復(fù)制代碼|first_name|last_name| |----------|---------| |AARON ? ? |SELBY ? ?| |ADAM ? ? ?|GOOCH ? ?| |ADAM ? ? ?|GRANT ? ?| |ADAM ? ? ?|HOPPER ? | |ADRIAN ? ?|CLARY ? ?| |AGNES ? ? |BISHOP ? | |AL ? ? ? ?|GARLAND ?| |ALAN ? ? ?|DREYFUSS | |... ? ? ? |... ? ? ?|

使用CORRESPONDING

現(xiàn)在，在HSQLDB中，以及在標(biāo)準(zhǔn)SQL中，你可以使用CORRESPONDING 來完成這種任務(wù)。比如說:

sql復(fù)制代碼SELECT * FROM actor UNION ALL CORRESPONDING SELECT * FROM customer UNION ALL CORRESPONDING SELECT * FROM staff ORDER BY first_name, last_name ?

其結(jié)果是這樣的:

sql復(fù)制代碼|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| |... ? ? ? |... ? ? ?|... ? ? ? ? ? ? ? ? ? ?|

那么，發(fā)生了什么？列FIRST_NAME,LAST_NAME, 和LAST_UPDATE 是這三個(gè)表所共有的。換句話說，如果你針對HSQLDB中的INFORMATION_SCHEMA ，運(yùn)行這個(gè)查詢:

sql復(fù)制代碼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個(gè)列:

lua復(fù)制代碼|COLUMN_NAME| |-----------| |FIRST_NAME | |LAST_NAME ?| |LAST_UPDATE| ?

換句話說，CORRESPONDING ，在集合操作的子查詢中創(chuàng)建列的交集（即 "共享列"），投影這些，并應(yīng)用該投影的集合操作。在某種程度上，這類似于一個(gè) [NATURAL JOIN](https://blog.jooq.org/impress-your-coworkers-with-a-sql-natural-full-outer-join/)，后者也試圖找到列的交集以產(chǎn)生一個(gè)連接謂詞。然而，NATURAL JOIN ，然后投影所有的列（或列的聯(lián)合），而不僅僅是共享的列。

使用CORRESPONDING BY

就像NATURAL JOIN ，這是個(gè)有風(fēng)險(xiǎn)的操作。只要一個(gè)子查詢改變了它的投影（例如，由于表的列重命名），所有這些查詢的結(jié)果也會改變，甚至可能不會產(chǎn)生語法錯(cuò)誤，只是結(jié)果不同。

事實(shí)上，在上面的例子中，我們可能根本不關(guān)心那個(gè)LAST_UPDATE 列。它被意外地包含在UNION ALL 的集合操作中，就像NATURAL JOIN 會意外地使用LAST_UPDATE 來連接一樣。

對于連接，我們可以使用JOIN .. USING (first_name, last_name) ，至少指定我們想通過哪一個(gè)共享列名來連接這兩個(gè)表。使用CORRESPONDING ，我們可以為同樣的目的提供可選的BY 子句:

sql復(fù)制代碼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; ?

現(xiàn)在，這只產(chǎn)生了兩個(gè)想要的列:

sql復(fù)制代碼|first_name|last_name| |----------|---------| |AARON ? ? |SELBY ? ?| |ADAM ? ? ?|GOOCH ? ?| |ADAM ? ? ?|GRANT ? ?| |ADAM ? ? ?|HOPPER ? | |ADRIAN ? ?|CLARY ? ?| |AGNES ? ? |BISHOP ? | |AL ? ? ? ?|GARLAND ?| |ALAN ? ? ?|DREYFUSS | |... ? ? ? |... ? ? ?|

事實(shí)上，這樣一來，我們甚至可以有意義地使用INTERSECT和EXCEPT的語法，例如，找到與某個(gè)演員共享名字的客戶:

sql復(fù)制代碼SELECT * FROM actor INTERSECT CORRESPONDING BY (first_name, last_name) SELECT * FROM customer ORDER BY first_name, last_name; ?

制作:

lua復(fù)制代碼|first_name|last_name| |----------|---------| |JENNIFER ?|DAVIS ? ?| ?