快捷導(dǎo)航

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

更新時間：2022年06月10日 10:37:39 作者：有夢想的攻城獅

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

前言

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

語法解釋

語法

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

說明

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

執(zhí)行過程

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

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

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

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

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

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

使用案例

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

?? mysql版本： 8.0.28

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

學(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, '米來', 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);

班級學(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 '班號',
  `class_name` varchar(100)  DEFAULT '' COMMENT '班級名稱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='班級學(xué)生表';

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

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

常用查詢

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

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

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

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

已分配三年級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);

已分配并且班級是一年級1班和一年級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);

已分配三年級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的效率比較

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

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

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

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

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

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

循環(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í)行原理的說明，我們明白了一個道理：內(nèi)層循環(huán)次數(shù)的多少不會影響到外層的次數(shù)，但是外層循環(huán)的次數(shù)直接會影響到內(nèi)層循環(huán)的次數(shù)，外層循環(huán)每多一次，內(nèi)層循環(huán)就需要多完整的一次循環(huán)，所以我們優(yōu)化的目標(biāo)其實就是使外層的循環(huán)次數(shù)盡量少，總結(jié)來說：小表驅(qū)動大表。小表就是外層循環(huán)，大表就是內(nèi)層循環(huán)，也就是盡量減少外層循環(huán)的次數(shù)