跳到主要內容

關於 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 耐心總結，學不會你打我系列

留言

張貼留言

這個網誌中的熱門文章

架構設計 | 異步處理流程，多種實現模式詳解

本文源碼：GitHub·點這裏 || GitEE·點這裏 一、異步處理 1、異步概念 異步處理不用阻塞當前線程來等待處理完成，而是允許後續操作，直至其它線程將處理完成，並回調通知此線程。 必須強調一個基礎邏輯，異步是一種設計理念，異步操作不等於多線程，MQ中間件，或者消息廣播，這些是可以實現異步處理的方式。 同步處理和異步處理相對，需要實時處理並響應，一旦超過時間會結束會話，在該過程中調用方一直在等待響應方處理完成並返回。同步類似電話溝通，需要實時對話，異步則類似短信交流，發送消息之後無需保持等待狀態。 2、異步處理優點 雖然異步處理不能實時響應，但是處理複雜業務場景，多數情況都會使用異步處理。 異步可以解耦業務間的流程關聯，降低耦合度； 降低接口響應時間，例如用戶註冊，異步生成相關信息表； 異步可以提高系統性能，提升吞吐量； 流量削峰即把請求先承接下來，然後在異步處理； 異步用在不同服務間，可以隔離服務，避免雪崩； 異步處理的實現方式有很多種，常見多線程，消息中間件，發布訂閱的廣播模式，其根據邏輯在於先把請求承接下來，放入容器中，在從容器中把請求取出，統一調度處理。 注意 ：一定要監控任務是否產生積壓過度情況，任務如果積壓到雪崩之勢的地步，你會感覺每一片雪花都想勇闖天涯。 3、異步處理模式 異步流程處理的實現有好多方式，但是實際開發中常用的就那麼幾種，例如： 基於接口異步響應，常用在第三方對接流程； 基於消息生產和消費模式，解耦複雜流程； 基於發布和訂閱的廣播模式，常見系統通知 異步適用的業務場景，對數據強一致性的要求不高，異步處理的數據更多時候追求的是最終一致性。 二、接口響應異步 1、流程描述 基於接口異步響應的方式，有一個本地業務服務，第三方接口服務，流程如下： 本地服務發起請求，調用第三方服務接口； 請求包含業務參數，和成功或失敗的回調地址； 第三方服務實時響應流水號，作為該調用的標識； 之後第三方服務處理請求，得到最終處理結果； 如果處理成功，回調本地服務的成功通知接口； 如果處理失敗，回調本地服務的失敗通知接口； 整個流程基於部分異步和部分實時的模式，完整處理； 注意 ：如...
張貼留言
閱讀完整內容

.NET Core前後端分離快速開發框架(Core.3.0+AntdVue)

.NET Core前後端分離快速開發框架(Core.3.0+AntdVue) 目錄 引言 時間真快，轉眼今年又要過去了。回想今年，依次開源發布了 Colder.Fx.Net.AdminLTE(254Star) 、 Colder.Fx.Core.AdminLTE(335Star) 、 DotNettySocket(82Star) 、 IdHelper(47Star) ，這些框架及組件都是本着以實際出發，實事求是的態度，力求提高開發效率（我自己都是第一個使用者），目前來看反響不錯。但是隨着前端和後端技術的不斷變革，尤其是前端，目前大環境已經是前後端完全分離為主的開發模式，在這樣的大環境和必然趨勢之下，傳統的MVC就顯得有些落伍了。在這樣的背景下，一款前後端分離的.NET開發框架就顯得尤為必要，由此便定了框架的升級目標： 前後端分離 。 首先後端技術的選擇，從目前的數據來看，.NET Core的發展遠遠快於.NET Framework，最簡單的分析就是Colder.Fx.Core.AdminLTE發布比Colder.Fx.Net.AdminLTE晚，但是星星卻後來居上而且比前者多30%，並且這個差距在不斷擴大，由點及面的分析可以看出我們廣大.NET開發人員學習的熱情和积極向上的態度，並不是某些人所認為的那麼不堪（ 走自己的路，讓別人說去吧 ）。大環境上微軟积極擁抱開源，大力發展.NET Core， 可以說前途一片光明。因此後端決定採用 .NET Core3.0 ，不再浪費精力去支持.NET Framework。 然後是前端技術選擇，首選是三大js框架選擇，也是從實際出發，Vue相對其它而言更加容易上手，並且功能也毫不遜色，深得各種大小公司喜歡，如果偏要說缺點的話，那就是對TS支持不行，但是即將發布Vue3.0肯定會改變這一缺陷。選擇了Vue之後，然後就是UI框架的選擇了，這裏的選擇更多了，我選擇了Ant Design Vue，理由便是簡潔方便，十分符合我的設計理念。 技術選型完畢之後便...
張貼留言
閱讀完整內容

台北市住宅、社區建創儲能設備 最高可獲600萬元補助

為了推廣分散式發電，台北市環保局預計補助1億元供住宅社區設置創能、儲能設備，計有3種方案可供選擇。環保局說明，每案補助額度不超過建制總經費49％，社區每案最高可獲200萬至600萬元補助，住宅每案補助上限100萬元，5月1日起開放申請。 環保局說明，台北市溫室氣體排放量7成以上來自住商部門，其中以使用電力造成間接溫室氣體排放為大宗，台北市平均年用電量約159.86億度，1度電約等同排放0.5公斤二氧化碳，若想達成2050年淨零排放目標，僅靠節能減碳無法達成，必須發展綠色創能、儲能，並且參考歐洲、日本的做法，採分散式發電方式，推廣到社區、住家、商辦，達到供電自給自足目標。 因此，環保局推出「台北市住宅社區創能儲能及節能補助計畫」，補助對象為台北市轄內房屋所有權人及社區管理委員會，補助方案共計3種，每一申請人或每一場址僅能獲1次補助，每案補助額度不超過建置總經費49％為限，5月1日到7月31日開放申請，但補助經費用完即停止申請。 環保局說明，方案A補助對象以社區為主，公共區域申請創能儲能及節能項目，每案補助上限新台幣600萬元；方案B分為住宅或社區公共區域申請創能搭配儲能項目（創能或儲能方案不得單獨申請），社區每案補助上限新台幣400萬元，住宅每案補助上限100萬元。方案C補助對象也是社區，公共區域申請節能項目，每案補助上限新台幣200萬元。 網頁設計 最專業,超強功能平台可客製,窩窩以「數位行銷」「品牌經營」「網站與應用程式」「印刷品設計」等四大主軸，為每一位客戶客製建立行銷脈絡及洞燭市場先機,請問 台中電動車 哪裡在賣比較便宜可以到台中景泰電動車門市去看看總店：臺中市潭子區潭秀里雅潭路一段102-1號。 電動車補助 推薦評價好的 iphone維修 中心擁有專業的維修技術團隊，同時聘請資深iphone手機維修專家，現場說明手機問題，快速修理，沒修好不收錢住家的頂樓裝 太陽光電 聽說可發揮隔熱功效一線推薦東陽能源擁有核心技術、產品研發、系統規劃設置、專業團隊的太陽能發電廠商。 網頁設計 一頭霧水該從何著手呢? 回頭車 貨運收費標準宇安交通關係企業，自成立迄今，即秉持著「以誠待人」、「以實處事」的企業信念 台中搬家公司 教你幾個打包小技巧,輕鬆整理裝箱!還在煩惱搬家費用要多少哪？台中大展搬家線上試算搬家費用，從此不再擔心「物品怎麼計費」、「多...
張貼留言
閱讀完整內容