一個很少見但很有用的SQL功能

語言: CN / TW / HK

我最近偶然發現了一個標準的SQL特性,令我驚訝的是,這個特性在HSQLDB中實現了。這個關鍵字是CORRESPONDING ,它可以和所有的集合操作一起使用,包括UNIONINTERSECT 、和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_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

```

結果可能看起來像這樣:

|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 actor UNION ALL CORRESPONDING SELECT * FROM customer UNION ALL CORRESPONDING SELECT * FROM staff ORDER 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_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個列:

``` |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 子句:

``` 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;

```

現在,這隻產生了兩個想要的列:

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

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

``` SELECT * FROM actor INTERSECT CORRESPONDING BY (first_name, last_name) SELECT * FROM customer ORDER BY first_name, last_name;

```

製作:

``` |first_name|last_name| |----------|---------| |JENNIFER |DAVIS |

```

其他方言

我以前沒有在其他方言中多次遇到過這種語法。也許,它在將來會運到PostgreSQL中。Vik Fearing已經在做一個分支了:

我有一個git分支,已經很久沒有工作了

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

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