快捷導(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，我們今天來學(xué)習(xí)了解一下這個(gè)exists這個(gè)sql關(guān)鍵詞的用法，這樣在工作中遇到一些特定的業(yè)務(wù)場(chǎng)景就可以有更加多樣化的解決方案

語法解釋

語法

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

說明

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

執(zhí)行過程

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

2、接下來進(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è)命令的說明： 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è)計(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, '米來', 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í)也可以通過 in 關(guān)鍵字來實(shí)現(xiàn)，下面我們寫一下 in 關(guān)鍵字對(duì)應(yīng)的查詢語句，

通過 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);

通過 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);

下面我們來分析一下這兩個(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í)行原理的說明，我們明白了一個(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é)來說：小表驅(qū)動(dòng)大表。小表就是外層循環(huán)，大表就是內(nèi)層循環(huán)，也就是盡量減少外層循環(huán)的次數(shù)