快捷導(dǎo)航

mysql中關(guān)鍵詞exists的用法實(shí)例詳解

更新時(shí)間：2022年06月10日 10:37:39 作者：有夢(mèng)想的攻城獅

在mysql中exists用于檢查子查詢是否至少會(huì)返回一行數(shù)據(jù),該子查詢實(shí)際上并不返回任何數(shù)據(jù),而是返回true或false,下面這篇文章主要給大家介紹了關(guān)于mysql中關(guān)鍵詞exists用法的相關(guān)資料,需要的朋友可以參考下

前言

在日常開發(fā)中，用mysql進(jìn)行查詢的時(shí)候，有一個(gè)比較少見的關(guān)鍵詞exists，我們今天來(lái)學(xué)習(xí)了解一下這個(gè)exists這個(gè)sql關(guān)鍵詞的用法，這樣在工作中遇到一些特定的業(yè)務(wù)場(chǎng)景就可以有更加多樣化的解決方案

語(yǔ)法解釋

語(yǔ)法

SELECT column1 FROM t1 WHERE [conditions] and EXISTS (SELECT * FROM t2 );

說(shuō)明

括號(hào)中的子查詢并不會(huì)返回具體的查詢到的數(shù)據(jù)，只是會(huì)返回true或者false，如果外層sql的字段在子查詢中存在則返回true，不存在則返回false
即使子查詢的查詢結(jié)果是null，只要是對(duì)應(yīng)的字段是存在的，子查詢中則返回true，下面有具體的例子

執(zhí)行過(guò)程

1、首先進(jìn)行外層查詢，在表t1中查詢滿足條件的column1

2、接下來(lái)進(jìn)行內(nèi)層查詢，將滿足條件的column1帶入內(nèi)層的表t2中進(jìn)行查詢，

3、如果內(nèi)層的表t2滿足查詢條件，則返回true，該條數(shù)據(jù)保留

4、如果內(nèi)層的表t2不滿足查詢條件，則返回false，則刪除該條數(shù)據(jù)

5、最終將外層的所有滿足條件的數(shù)據(jù)進(jìn)行返回

貼個(gè)鏈接，mysql官方對(duì)于這個(gè)命令的說(shuō)明： https://dev.mysql.com/doc/refman/8.0/en/exists-and-not-exists-subqueries.html；喜歡看英文原版說(shuō)明的可以來(lái)這里看一下

使用案例

環(huán)境準(zhǔn)備

?? mysql版本： 8.0.28

?? 數(shù)據(jù)庫(kù)表設(shè)計(jì)：

學(xué)生表： t_student

CREATE TABLE `t_student` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT '' COMMENT '學(xué)生姓名',
  `age` int NOT NULL COMMENT '年齡',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='學(xué)生表';

導(dǎo)入部分?jǐn)?shù)據(jù)

INSERT INTO `t_student` (`id`, `name`, `age`)
VALUES
   (1, '小張', 10),
   (2, 'chenille', 13),
   (3, '小王', 15),
   (4, '小米', 11),
   (5, 'dong', 13),
   (6, 'xi', 12),
   (7, 'chenille', 13),
   (8, '小王地方', 15),
   (9, '米來(lái)', 11),
   (10, 'dong', 13),
   (11, '呵呵', 12),
   (12, 'chenille', 13),
   (13, '小趙', 15),
   (14, '小米-0', 11),
   (15, 'bei', 13),
   (16, 'xi-xx', 12),
   (17, 'chenille', 13),
   (18, '小王-hehe', 15),
   (19, '小米-qian', 11),
   (20, 'dong', 13),
   (21, 'xi', 12),
   (22, 'chenille', 13),
   (23, '小王-1', 15),
   (24, '小米-2', 11),
   (25, 'dong-3', 13),
   (26, 'xi-0', 12),
   (27, 'chenille-4', 13),
   (28, '小王-4', 15),
   (29, '小米-7', 11),
   (30, 'dong-1', 13),
   (31, 'xi-5', 12),
   (32, '貔貅', 10),
   (33, '耄耋', 12),
   (34, '饕餮', 9),
   (35, '龍', 13),
   (36, '青牛', 12);

班級(jí)學(xué)生表：t_class_student

CREATE TABLE `t_class_student` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL COMMENT '學(xué)生ID',
  `class_id` int NOT NULL COMMENT '班號(hào)',
  `class_name` varchar(100)  DEFAULT '' COMMENT '班級(jí)名稱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='班級(jí)學(xué)生表';

導(dǎo)入部分?jǐn)?shù)據(jù)

 INSERT INTO `t_class_student` (`id`, `student_id`, `class_id`, `class_name`)
VALUES
    (1, 1, 1, '一年級(jí)1班'),
    (2, 2, 1, '一年級(jí)1班'),
    (3, 3, 1, '一年級(jí)1班'),
    (4, 4, 1, '一年級(jí)1班'),
    (5, 5, 1, '一年級(jí)1班'),
    (6, 6, 1, '一年級(jí)1班'),
    (7, 7, 1, '一年級(jí)1班'),
    (8, 8, 1, '一年級(jí)1班'),
    (9, 9, 1, '一年級(jí)1班'),
    (10, 10, 1, '一年級(jí)1班'),
    (11, 11, 2, '一年級(jí)2班'),
    (12, 12, 2, '一年級(jí)2班'),
    (13, 13, 2, '一年級(jí)2班'),
    (14, 14, 2, '一年級(jí)2班'),
    (15, 15, 2, '一年級(jí)2班'),
    (16, 16, 2, '一年級(jí)2班'),
    (17, 17, 2, '一年級(jí)2班'),
    (18, 18, 2, '一年級(jí)2班'),
    (19, 19, 2, '一年級(jí)2班'),
    (20, 20, 2, '一年級(jí)2班'),
    (21, 21, 3, '二年級(jí)2班'),
    (22, 22, 3, '二年級(jí)2班'),
    (23, 23, 3, '二年級(jí)2班'),
    (24, 24, 3, '二年級(jí)2班'),
    (25, 25, 3, '二年級(jí)2班'),
    (26, 26, 3, '二年級(jí)2班'),
    (27, 27, 3, '二年級(jí)2班'),
    (28, 28, 3, '二年級(jí)2班'),
    (29, 29, 3, '二年級(jí)2班'),
    (30, 30, 3, '二年級(jí)2班'),
    (31, 31, 4, '三年級(jí)1班');
    (32, 32, 4, null);

常用查詢

已分配班級(jí)的學(xué)生名單 ??

select * from t_student as s where exists (select student_id from t_class_student where student_id = s.id);

未分配班級(jí)的學(xué)生名單 ??

select * from t_student as s where not exists (select student_id from t_class_student where student_id = s.id);

已分配三年級(jí)1班的學(xué)生名單 ??

select * from t_student as s where exists (select student_id from t_class_student where student_id = s.id and class_id = 4);

已分配并且班級(jí)是一年級(jí)1班和一年級(jí)2班的學(xué)生名單 ??

select * from t_student as s where exists (select student_id from t_class_student where student_id = s.id and class_id in (1, 2) );

查詢到的字段為null，但是子查詢返回的結(jié)果為true ??

select * from t_student as s where exists (select class_name from t_class_student where student_id = s.id and class_id = 4);

查詢?nèi)繉W(xué)生名單 ??

select * from t_student as s where exists (select student_id from t_class_student where 1=1);

已分配三年級(jí)1班的并且年齡大于10歲的學(xué)生名單 ??

select * from t_student as s where age > 10 and exists (select student_id from t_class_student where student_id = s.id and class_id = 4);

exists與in的效率比較

上面的這些查詢其實(shí)也可以通過(guò) in 關(guān)鍵字來(lái)實(shí)現(xiàn)，下面我們寫一下 in 關(guān)鍵字對(duì)應(yīng)的查詢語(yǔ)句，

通過(guò) in 實(shí)現(xiàn)已分配班級(jí)的學(xué)生名單 ??

select * from t_student as s where id in (select student_id from t_class_student where student_id = s.id);

通過(guò) in 實(shí)現(xiàn)未分配班級(jí)的學(xué)生名單 ??

select * from t_student as s where id not in (select student_id from t_class_student where student_id = s.id);

下面我們來(lái)分析一下這兩個(gè)關(guān)鍵字使用效率到底那個(gè)更高呢？

循環(huán)嵌套查詢執(zhí)行原理

?? 循環(huán)由外向內(nèi)，外層循環(huán)執(zhí)行一次，內(nèi)層循環(huán)則需要完整的執(zhí)行一次，內(nèi)層執(zhí)行執(zhí)行完后返回執(zhí)行結(jié)果，外層循環(huán)繼續(xù)執(zhí)行，直到外層循環(huán)完全執(zhí)行完成

循環(huán)優(yōu)化策略

?? 有了上面的執(zhí)行原理的說(shuō)明，我們明白了一個(gè)道理：內(nèi)層循環(huán)次數(shù)的多少不會(huì)影響到外層的次數(shù)，但是外層循環(huán)的次數(shù)直接會(huì)影響到內(nèi)層循環(huán)的次數(shù)，外層循環(huán)每多一次，內(nèi)層循環(huán)就需要多完整的一次循環(huán)，所以我們優(yōu)化的目標(biāo)其實(shí)就是使外層的循環(huán)次數(shù)盡量少，總結(jié)來(lái)說(shuō)：小表驅(qū)動(dòng)大表。小表就是外層循環(huán)，大表就是內(nèi)層循環(huán)，也就是盡量減少外層循環(huán)的次數(shù)

exists和in查詢?cè)淼膮^(qū)別

?? exists ：外表先進(jìn)行循環(huán)查詢，將查詢結(jié)果放入exists的子查詢中進(jìn)行條件驗(yàn)證，確定外層查詢數(shù)據(jù)是否保留

?? in ：先查詢內(nèi)表，將內(nèi)表的查詢結(jié)果當(dāng)做條件提供給外表查詢語(yǔ)句進(jìn)行比較

結(jié)論

通過(guò)上面的優(yōu)化策略分析和exists和in的查詢?cè)淼姆治?，將這兩塊內(nèi)容結(jié)合起來(lái)其實(shí)就得出了我們想要的一個(gè)結(jié)論：

外層小表，內(nèi)層大表（或者將sql從左到由來(lái)看：左面小表，右邊大表）： exists 比 in 的效率高
外層大表，內(nèi)層小表（或者將sql從左到由來(lái)看：左面大表，右邊小表）： in 比 exists 的效率高

參考資料

https://www.bilibili.com/video/BV1V64y1q7yi?spm_id_from=333.337.search-card.all.click

總結(jié)

上面我們簡(jiǎn)單介紹了一下平時(shí)我們比較少用到的一個(gè)exists的關(guān)鍵字，通過(guò)一些使用的實(shí)例，大家也可以基本上了解了它的使用方法，在以后的日常工作中，我們?cè)谂龅揭恍┎樵儐?wèn)題的時(shí)候，這個(gè)時(shí)候就有了更加多樣化的選擇方案啦

到此這篇關(guān)于mysql中關(guān)鍵詞exists用法的文章就介紹到這了,更多相關(guān)mysql exists用法內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: