跳到主要內容

關於 JOIN 耐心總結，學不會你打我系列

現在隨着各種數據庫框架的盛行，在提高效率的同時也讓我們忽略了很多底層的連接過程，這篇文章是對 SQL 連接過程梳理，並涉及到了現在常用的 SQL 標準。



其實標準就是在不同的時間，制定的一些寫法或規範。



從 SQL 標準說起


在編寫 SQL 語句前，需要先了解在不同版本的規範，因為隨着版本的變化，在具體編寫 SQL 時會有所不同。對於 SQL 來說，SQL92 和 SQL99 是最常見的兩個 SQL 標準，92 和 99 對應其提出的年份。除此之外，還存在 SQL86、SQL89、SQL2003、SQL2008、SQL2011，SQL2016等等。


但對我們來說，SQL92 和 SQL99 是最常用的兩個標準，主要學習這兩個就可以了。


為了演示方便，現在數據庫中加入如下三張表：


每個學生屬於一個班級，通過班級的人數來對應班級的類型。


-- ----------------------------
 DROP TABLE IF EXISTS `Student`;
 CREATE TABLE `Student` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(20) NOT NULL DEFAULT '',
   `birth` varchar(20) NOT NULL DEFAULT '',
   `sex` varchar(10) NOT NULL DEFAULT '',
   `class_id` int(11) NOT NULL COMMENT '班級ID',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
 
 -- ----------------------------
 -- Records of Student
 -- ----------------------------
 INSERT INTO `Student` VALUES ('1', '胡一', '1994.1.1', '男', '1');
 INSERT INTO `Student` VALUES ('3', '王阿', '1992.1.1', '女', '1');
 INSERT INTO `Student` VALUES ('5', '王琦', '1993.1.2', '男', '1');
 INSERT INTO `Student` VALUES ('7', '劉偉', '1998.2.2', '女', '1');
 INSERT INTO `Student` VALUES ('11', '張使', '1994.1.1', '男', '3');
 INSERT INTO `Student` VALUES ('13', '王阿', '1992.1.1', '女', '3');
 INSERT INTO `Student` VALUES ('15', '夏琪', '1993.1.2', '男', '3');
 INSERT INTO `Student` VALUES ('17', '劉表', '1998.2.2', '女', '3');
 INSERT INTO `Student` VALUES ('19', '諸葛', '1994.1.1', '男', '3');
 INSERT INTO `Student` VALUES ('21', '王前', '1992.1.1', '女', '3');
 INSERT INTO `Student` VALUES ('23', '王意識', '1993.1.2', '男', '3');
 INSERT INTO `Student` VALUES ('25', '劉等待', '1998.2.2', '女', '3');
 INSERT INTO `Student` VALUES ('27', '胡是一', '1994.1.1', '男', '5');
 INSERT INTO `Student` VALUES ('29', '王阿請', '1992.1.1', '女', '5');
 INSERT INTO `Student` VALUES ('31', '王消息', '1993.1.2', '男', '5');
 INSERT INTO `Student` VALUES ('33', '劉全', '1998.2.2', '女', '5');
 INSERT INTO `Student` VALUES ('35', '胡愛', '1994.1.1', '男', '5');
 INSERT INTO `Student` VALUES ('37', '王表', '1992.1.1', '女', '5');
 INSERT INTO `Student` VALUES ('39', '王華', '1993.1.2', '男', '5');
 INSERT INTO `Student` VALUES ('41', '劉偉以', '1998.2.2', '女', '5');
 INSERT INTO `Student` VALUES ('43', '胡一彪', '1994.1.1', '男', '5');
 INSERT INTO `Student` VALUES ('45', '王阿符', '1992.1.1', '女', '5');
 INSERT INTO `Student` VALUES ('47', '王琦刪', '1993.1.2', '男', '5');
 INSERT INTO `Student` VALUES ('49', '劉達達', '1998.2.2', '女', '5');
 
 -- ----------------------------
 -- Table structure for `Class`
 -- ----------------------------
 DROP TABLE IF EXISTS `Class`;
 CREATE TABLE `Class` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` varchar(20) NOT NULL DEFAULT '',
   `number` int(11) NOT NULL DEFAULT '',
   `class_type_id` int(11) NOT NULL COMMENT '班級類型ID',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
 
 -- ----------------------------
 -- Records of Class
 -- ----------------------------
 INSERT INTO `Class` VALUES ('1', '1年1班', 4, '1');
 INSERT INTO `Class` VALUES ('3', '1年2班', 8, '3');
 INSERT INTO `Class` VALUES ('5', '1年3班', 12, '5');
 
 CREATE TABLE `ClassType`(
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `name` VARCHAR(20) NOT NULL DEFAULT '',
   `minimum_number` int(11) NOT NULL DEFAULT '' COMMENT '最少的班級人數',
   `maximum_number` int(11) NOT NULL DEFAULT '' COMMENT '最多的班級人數',
   PRIMARY KEY(`id`)
 );
 INSERT INTO `ClassType` VALUES ('1', '小班', '1', '4');
 INSERT INTO `ClassType` VALUES ('3', '中班', '5', '8');
 INSERT INTO `ClassType` VALUES ('5', '大班', '9', '12');

SQL92


笛卡爾積（交叉連接）


笛卡爾積是一個數學上的概念，表示如果存在 X，Y 兩個集合，則 X，Y 的笛卡爾積記為 X * Y. 表示由 X，Y 組成有序對的所有情況。


對應在 SQL 中，就是將兩張表中的每一行進行組合。而且在連接時，可以沒有任何限制，可將沒有關聯關係的任意表進行連接。


這裏拿學生表和班級表舉例，在學生表中我們插入了20名學生的數據，課程表中插入三個班級。則學生和班級的笛卡爾結果就是將兩表的每行數據一一組合，最後就是有 24 * 3 = 72 行的結果，如下圖所示。


並且需要知道的是，下面學習的外連接，自連接，等值連接等都是在笛卡爾積的基礎上篩選得到的。


對應的 SQL92 寫法為：


select * from Student, Class;


等值連接（內連接）


等值連接就是將兩張表中都存在的列進行連接，具體來說就是 where 後面通過 = 進行篩選。


比如查詢 Student 和其所屬 Class 信息的關係：


SELECT * FROM Student as s, Class as c where s.class_id = c.id;


非等值連接


非等值連接就是將等值連接中的等號換成其他的過濾條件。


比如這裏查詢每個班級的信息以及所屬的班級類別。


SELECT * FROM Class as c, ClassType t where c.number between t.minimum_number and maximum_number;


外連接


對於 SQL92 的外連接來說，在連接時會將兩張表分為主表和從表，主表显示所有的數據，從表显示匹配到的數據，沒有匹配到的則显示 None. 用 + 表示從表的位置。


左外連接：左表是主表，右表時從表。


SELECT * FROM Student as s , Class as c where s.class_id = c.id(+);

右外連接：左表是從表，右表時主表。


SELECT * FROM Class as c, Student as s  where c.id = s.class_id(+);

注意 SQL92 中並沒有全外連接。


自連接


自連接一般用於連接本身這張表，由於常見的 DBMS 都會對自連接做一些優化，所以一般在子查詢和自連接的情況下都使用自連接。


比如想要查詢比1年1班人數多的班級：


子查詢：


SELECT * FROM Class WHERE number > (SELECT number FROM Class WHERE name="1年1班");

自連接：


SELECT c2.* FROM Class c1, Class c2 WHERE c1.number < c2.number and c1.name = "1年1班";


SQL99


交叉連接


SELECT * FROM Student CROSS JOIN Class;

還可以對多張表進行交叉連接，比如連接 Student，Class，ClassType 三張表，結果為 24 * 3 * 3 = 216 條。


相當於嵌套了三層 for 循環。



自然連接


其實就是 SQL92 中的等值連接，只不過連接的對象是具有相同列名，並且值也相同的內容。


SELECT * FROM Student NATURAL JOIN CLASS;
 

SELECT * FROM Student as s, Class as c where s.id = c.id;

如果想用 NATURAL JOIN 時，建議為兩表設置相同的列名，比如 Student 表中的班級列為 class_id, 則在 Class 表中，id 也應改為 class_id. 這樣連接更合理一些。


如果大家嘗試，自然連接的話，會發現查出來的結果集為空，不要奇怪，下面說一下原因：



這是因為，NATURAL JOIN 會自動連接兩張表中相同的列名，而對於 Student 和 Class 兩張表來說，id 和 name 在這兩張表都是相同的，所以既滿足 id 又滿足 name 的行是不存在的。


相當於 SQL 變成了這樣


SELECT * FROM Student as s, Class as c where s.id = c.id and s.name = c.name;

ON 連接


ON 連接其實對了 SQL92 中的等值連接和非等值連接：


等值連接：


SELECT * FROM Student as s JOIN Class as c ON s.class_id = c.id;
 
 or
 
 SELECT * FROM Student as s INNER JOIN Class as c ON s.class_id = c.id;

非等值連接：


SELECT * FROM Class as c JOIN ClassType t ON c.number between t.minimum_number and maximum_number;

USING 連接


和 NATURAL JOIN 很像，可以手動指定具有相同列名的列進行連接：


SELECT * FROM Student JOIN Class USING(id);


這時就解決了之前列存在重名，無法連接的情況。


外連接


左外連接: 左表是主表，右表時從表。


SELECT * FROM Student as s LEFT JOIN Class as c on s.class_id = c.id;
 OR
 SELECT * FROM Student as s LEFT OUTER JOIN Class as c on s.class_id = c.id;

右外連接：左表是從表，右表時主表。


SELECT * FROM Student as s RIGHT JOIN Class as c on s.class_id = c.id;
 OR
 SELECT * FROM Student as s RIGHT OUTER JOIN Class as c on s.class_id = c.id;

全外連接: 左外連接 + 右外的連接的合集


SELECT * FROM Student as s FULL JOIN Class as c ON s.class_id = c.id;


MySQL 中沒有全外連接的概念。



自連接：


SELECT c2.* FROM Class c1 JOIN Class c2 ON c1.number < c2.number and c1.name = "1年1班";

SQL92 和 SQL99 的對比



  1. SQL92 中的等值連接（內連接），非等值連接，自連接對應了 SQL99 的 ON 連接，用於篩選滿足連接條件的數據行。


  2. SQL92 的笛卡爾積連接，對應了 SQL99 的交叉連接。


  3. SQL92 中的外連接並不包含全外連接，而 SQL99 支持，並且將 SQL92 中 WHERE 換為 SQL99 的 ON. 這樣的好處可以更清晰的表達連接表的過程，更直觀。

    SELECT ...
     FROM table1
         JOIN table2 ON filter_condition
             JOIN table3 ON filter_condition

  4. SQL99 多了自然連接和 USING 連接的過程，兩者的區別是是否需要顯式的指定列名。



總結


我們知道，在 SQL 中，按照年份劃分了不同的標準，其中最為常用的是 SQL-92 和 SQL-99 兩個標準。


接着，對比了 92 和 99 兩者的不同，發現 99 的標準在連接時，更加符合邏輯並且更加直觀。


最後，上一張各種連接的示意圖, 方便梳理複習：



參考


各種連接的不同

本站聲明:網站內容來源於博客園,如有侵權,請聯繫我們,我們將及時處理

【其他文章推薦】



※USB CONNECTOR掌控什麼技術要點? 帶您認識其相關發展及效能



※台北網頁設計公司這麼多該如何選擇?



※智慧手機時代的來臨，RWD網頁設計為架站首選



※評比南投搬家公司費用收費行情懶人包大公開



※回頭車貨運收費標準


Orignal From: 關於 JOIN 耐心總結，學不會你打我系列

留言

張貼留言

這個網誌中的熱門文章

有了四步解題法模板，再也不害怕動態規劃！（看不懂算我輸）

導言 動態規劃問題一直是算法面試當中的重點和難點，並且動態規劃這種通過空間換取時間的算法思想在實際的工作中也會被頻繁用到，這篇文章的目的主要是解釋清楚 什麼是動態規劃 ，還有就是面對一道動態規劃問題，一般的 思考步驟 以及其中的注意事項等等，最後通過幾道題目將理論和實踐結合。 什麼是動態規劃 如果你還沒有聽說過動態規劃，或者僅僅只有耳聞，或許你可以看看 Quora 上面的這個 回答 。 How to explain dynamic 用一句話解釋動態規劃就是 " 記住你之前做過的事 "，如果更準確些，其實是 " 記住你之前得到的答案 "。 我舉個大家工作中經常遇到的例子。 在軟件開發中，大家經常會遇到一些系統配置的問題，配置不對，系統就會報錯，這個時候一般都會去 Google 或者是查閱相關的文檔，花了一定的時間將配置修改好。 過了一段時間，去到另一個系統，遇到類似的問題，這個時候已經記不清之前修改過的配置文件長什麼樣，這個時候有兩種方案，一種方案還是去 Google 或者查閱文檔，另一種方案是借鑒之前修改過的配置，第一種做法其實是萬金油，因為你遇到的任何問題其實都可以去 Google，去查閱相關文件找答案，但是這會花費一定的時間，相比之下，第二種方案肯定會更加地節約時間，但是這個方案是有條件的，條件如下： 之前的問題和當前的問題有着關聯性，換句話說，之前問題得到的答案可以幫助解決當前問題 需要記錄之前問題的答案 當然在這個例子中，可以看到的是，上面這兩個條件均滿足，大可去到之前配置過的文件中，將配置拷貝過來，然後做些細微的調整即可解決當前問題，節約了大量的時間。 不知道你是否從這些描述中發現，對於一個動態規劃問題，我們只需要從兩個方面考慮，那就是 找出問題之間的聯繫 ，以及 記錄答案 ，這裏的難點其實是找出問題之間的聯繫，記錄答案只是順帶的事情，利用一些簡單的數據結構就可以做到。 概念 上面的解釋如果大家可以理解的話，接    動態規劃 算法是通過拆分問題，定義問題狀態和狀態之間的關係，使得問題能夠以遞推（或者說分治）的方式去解決。它的幾個重要概念如下所述。    階段： 對於一個完整的問題過程，適當的切分為若干個相互聯繫的子問題，每次在求解一個子問題...
張貼留言
閱讀完整內容

計算機本地文件快要滅絕了

　　 編者按： 文件是数字世界的基石，是我們基本的工作單位。但是，隨着互聯網的雲化、平台化、服務化，文件日益變得可有可無。這樣一種改變究竟好不好呢？喜歡懷舊的 Simon Pitt 開始回顧各種文件的好處，哪怕這讓他顯得不合時宜。原文發表在 medium 上，標題是：Computer Files Are Going Extinct 　　我喜歡文件。我喜歡對文件重命名、移動、排序，改變它們在文件夾中的显示方式，去備份文件，將之上傳到互聯網，恢復它們，對其進行複製，甚至還可以對文件進行碎片整理。作為信息存儲方式的一種隱喻，在我看來文件是很出色的。我喜歡把文件當作一個工作單位。如果我要寫篇文章，文章會放在文件裏面。如果我要生成圖像，圖像會保存進文件裏面。 　　 謳歌 files.doc 　　文件是擬物化的。這是個很花哨的詞，只是用來表示文件是反映現實物品的一個数字概念。比方說，Word 文檔就像一張紙，躺在你的辦公桌上（desktop）。JPEG 就像一幅畫，等等。它們每個都有一個小圖標，圖標的樣子看起來像它們所代表的現實物品。一堆紙，一個畫框，一個馬尼拉文件夾。真的挺很迷人的。 　　我喜歡文件的一點是，不管裏面有什麼，跟文件的交互方式總是一致的。我上面提到的那些東西——複製、排序、碎片整理——我可以對任何文件進行那些處理。文件可能是圖像、遊戲的一部分、也可能是我最喜歡的餐具清單。碎片整理程序不在乎它是什麼。它不會去判斷內容。 　　自從我開始在 Windows 95 裏面創建文件以來，我就一直都很喜歡文件。但是我注意到我們已經開始慢慢地遠離把文件當作基本工作單位的做法。 Windows95。我的計算機 　　 services.mp3 的興起 　　十幾歲的時候，我開始痴迷於收集和管理数字音樂：我收藏 MP3 文件。一大堆的 128 kbps MP3 文件。如果你足夠幸運，有自己的 CD 刻錄機的話，就可以將它們刻錄到 CD 上，然後在朋友之間傳遞。一張 CD 可以容納 700 MB。這相當於將近 500 張軟盤！ 　　我會仔細端詳我的收藏，然後煞費苦心地給它們添加上 IDv1 和 IDv2 音樂標籤。隨着時間的流逝，大家開始開發可以在雲端自動獲取曲目列表的工具，這樣你就可以檢查和驗證 MP3 的質量。有時候我甚至會去聽那些該死的東西，儘管...
張貼留言
閱讀完整內容

純電動 Mini Cooper SE 將成為中國國產車，年產 16 萬輛

BMW 集團與中國長城汽車合資，將於江蘇建立新廠，專門投入生產 MINI Cooper SE 和部分長城品牌電動車，預計於 2022 年完工並投入生產，每年將可生產 16 萬輛電動車。 靈動可愛的 Mini Cooper，在許多車迷心中都有著特殊的地位，今年 7 月發表了首款純電動版本的 Mini Cooper SE 之後，獲得熱烈迴響，預訂數量已接近 8 萬台，顯示大家對於純電 Mini 的熱愛，因為油電版的 Mini Cooper Countryman 的全球總銷售量也才 3 萬出頭。 Mini Cooper SE 之前公布了官方定價，最低從 27,900 歐元起算，美國售價約 29,900 美元。相比現有的三門款，只貴了一成左右。然而，三年後，中國消費者將有機會買到最便宜的電動 Mini。 電動 Mini Cooper SE 最低價是 27,900 歐元，扣掉全額補助最低可以到 24,400 歐元。 BMW 集團與中國長城汽車集團於 2018 年宣布，將組建合資公司光束汽車，投入在中國的電動車生產計畫，而現在他們正式宣布啟動計畫，於江蘇張家港打造一個新工廠，全部投入電動車的製造，包括了 Mini Cooper SE 和其他長城汽車旗下的電動車。 目前的電動 Mini 只在英國牛津工廠製造，不難想像當產能轉移到中國後，Mini Cooper SE 的價格將有機會進一步調降，來競爭全球最大的電動車市場。這座屬於合資公司光束汽車的新工廠，採用一個新的產銷模式，由 BMW 和長城共同合作開發、設計、製造新產品，但是銷售通路完全沿用原本的品牌渠道。 換句話說，2020 年到 2022 年銷售的電動 Mini，將會是英國製造，而 2022 年後就會有中國製造版本開賣，考量到 Mini 在中國每年約有 30 萬輛的銷售額，同時油電版的 Coutryman 銷量更佔了全球將近五分之一，無怪乎 BMW 會想在最接近主要市場的地方蓋工廠囉。 外型完美復刻油車版 最後，簡單介紹一下 Mini Cooper SE 這台車。Mini 在電動化的路上，盡力保持著跟經典造型一致的設計，畢竟大家愛的就是它的設計。電動版的 Mini 車頭、車身跟車屁股都多了一個黃色的插頭標誌，車頭的氣壩則變成封閉式設計，除此之外，幾乎看不出來差別，連馬達...
張貼留言
閱讀完整內容