分享很少見很有用的SQL功能CORRESPONDING
前言
我最近偶然發(fā)現(xiàn)了一個標(biāo)準(zhǔn)的SQL特性，令我驚訝的是，這個特性在HSQLDB中實現(xiàn)了。這個關(guān)鍵字是CORRESPONDING ，它可以和所有的集合操作一起使用，包括UNION 、INTERSECT 、和EXCEPT 。
讓我們來看看sakila數(shù)據(jù)庫。它有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
);相似，但不相同。如果我們想從我們的數(shù)據(jù)庫中獲得所有的 "人 "呢？在任何普通的數(shù)據(jù)庫產(chǎn)品中，有一種方法可以做到這一點:
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é)果可能看起來像這樣:
|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ù)。比如說:
SELECT * FROM actor UNION ALL CORRESPONDING SELECT * FROM customer UNION ALL CORRESPONDING SELECT * FROM staff ORDER BY first_name, last_name
其結(jié)果是這樣的:
|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 是這三個表所共有的。換句話說，如果你針對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 ，在集合操作的子查詢中創(chuàng)建列的交集（即 "共享列"），投影這些，并應(yīng)用該投影的集合操作。在某種程度上，這類似于一個 [NATURAL JOIN](https://blog.jooq.org/impress-your-coworkers-with-a-sql-natural-full-outer-join/)，后者也試圖找到列的交集以產(chǎn)生一個連接謂詞。然而，NATURAL JOIN ，然后投影所有的列（或列的聯(lián)合），而不僅僅是共享的列。
使用CORRESPONDING BY
就像NATURAL JOIN ，這是個有風(fēng)險的操作。只要一個子查詢改變了它的投影（例如，由于表的列重命名），所有這些查詢的結(jié)果也會改變，甚至可能不會產(chǎn)生語法錯誤，只是結(jié)果不同。
事實上，在上面的例子中，我們可能根本不關(guān)心那個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;
現(xiàn)在，這只產(chǎn)生了兩個想要的列:
|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 |
到此這篇關(guān)于分享很少見很有用的SQL功能CORRESPONDING的文章就介紹到這了,更多相關(guān)SQL功能內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了mysql鎖定單個表的方法 ,需要的朋友可以參考下2014-05-05
這篇文章主要介紹了在MySQL中使用序列的簡單教程,是MySQL入門學(xué)習(xí)中的基礎(chǔ)知識,文中給出了基于PHP和Perl腳本的示例,需要的朋友可以參考下2015-05-05
詳解Mysql雙機(jī)熱備和負(fù)載均衡的實現(xiàn)步驟
MySQL數(shù)據(jù)庫沒有增量備份的機(jī)制，但它提供了一種主從備份的機(jī)制，就是把主數(shù)據(jù)庫的所有的數(shù)據(jù)同時寫到備份數(shù)據(jù)庫中。這篇文章主要介紹了Mysql的雙機(jī)熱備和負(fù)載均衡,需要的朋友可以參考下2019-10-10
MySQL?B-tree與B+tree索引數(shù)據(jù)結(jié)構(gòu)剖析
這篇文章主要介紹了MySQL?B-tree與B+tree索引數(shù)據(jù)結(jié)構(gòu)剖析，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-08-08
如何使用MySQL?Explain?分析?SQL?執(zhí)行計劃
MySQL?提供的?EXPLAIN?工具能夠幫助我們深入了解查詢語句的執(zhí)行過程、索引使用情況以及潛在的性能瓶頸,本文將詳細(xì)介紹如何使用?EXPLAIN?分析?SQL?執(zhí)行計劃,并探討其中各個重要字段的含義以及優(yōu)化建議,感興趣的朋友一起看看吧2025-04-04
復(fù)制記錄,采用自查詢方式2009-12-12
MySQL數(shù)據(jù)庫實驗實現(xiàn)簡單數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計
這篇文章主要介紹了MySQL數(shù)據(jù)庫實驗實現(xiàn)簡單數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計，文章通過理解并能運用數(shù)據(jù)庫設(shè)計的常見步驟來設(shè)計滿足給定需求的概念模和關(guān)系數(shù)據(jù)模型展開詳情,需要的朋友可以參考一下2022-06-06
這篇文章主要介紹了MySQL如何實現(xiàn)循環(huán)插入千萬級數(shù)據(jù)，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下2020-09-09
通過存儲過程動態(tài)創(chuàng)建MySQL對象的流程步驟
在當(dāng)今數(shù)據(jù)驅(qū)動的世界中,高效的數(shù)據(jù)庫管理至關(guān)重要,本文將展示如何通過存儲過程自動化地創(chuàng)建各種?MySQL?數(shù)據(jù)庫對象,通過這些方法,我們可以快速響應(yīng)業(yè)務(wù)需求,提高數(shù)據(jù)庫管理的靈活性和效率,需要的朋友可以參考下2024-10-10
這篇文章主要為大家詳細(xì)介紹了Mac os下mysql 5.7.13 安裝配置方法教程，感興趣的小伙伴們可以參考一下2016-06-06