MySQL Not Exists

2025年03月17日 | 阅读 9 分钟

MySQL 不存在简介

SQL MySQL 中的 Not Exists 操作符是一个有效的查询语句，用于测试子查询是否没有满足特定条件的对应行。这种操作符尤其有用，例如，当您需要从单个表中获取记录，而这些记录在其他表中没有关联记录时。

Not Exists 查询本质上是通过在 WHERE 子句中编写一个子查询，并查找不存在的满足特定标准的行来完成的。子查询会产生行，如果没有找到匹配的行，外层查询会将它们包含在输出集中。

例如，Not Exists 的一个应用场景是，当您想删除满足相邻数据库表中某个标准的记录，同时又想在 SQL 查询中简洁地表达否定时。

示例

SQL

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
INSERT INTO customers VALUES (1, 'Customer A');
INSERT INTO customers VALUES (2, 'Customer B');
INSERT INTO customers VALUES (3, 'Customer C');
INSERT INTO orders VALUES (101, 1, '2023-01-01');
INSERT INTO orders VALUES (102, 2, '2023-01-02');
INSERT INTO orders VALUES (103, 1, '2023-01-03');

SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

输出

在这里，该查询列出了所有没有相关交易的客户，展示了 Not Exists 清晰表达复杂条件的能力。理解和应用 Not Exists 对于在 MySQL 数据库中进行有效的查询和获取信息至关重要。

理解 Not Exists

概念和语法

MySQL 还提供了一个强大的工具，用于查询子选择中不存在记录的情况，称为 Not Exists。它包括确定子查询的输出是否为 null 或非 null。如果为 null，则将其行包含在主查询中。

语法

SQL

SELECT columns
FROM table1
WHERE Not Exists (
SELECT 1
FROM table2
WHERE condition
);

在这种情况下，table1 代表主表，Not Exists 子句中的子查询用于验证根据已识别的条件，table2 中不存在任何记录。

Not Exists 与 <> 或 NOT IN 的对比。

虽然 Not Exists 实现了与 <> (不等于) 操作符和 NOT IN 子句相似的结果，但存在关键差异。

NULL 值的处理

Not Exists 通常在处理 NULL 值时更直接。如果不存在 null 值，它会评估为 true，因为子查询没有返回任何内容。然而，<> 和 NOT IN 可能需要进一步考虑 Null 处理。

性能

在某些情况下，Not Exists 可能效果更好，特别是当我们在处理大量数据时。SQL 查询规划器对其性能的优化优于 <> 或 NOT IN。

相关子查询

在相关子查询的情况下，通常最好使用 Not Exists，其中子查询引用了外层查询的列。在这种情况下，NOT IN 和 <> 可能看起来不那么直观，或者需要比表面上更多的逻辑。

Not Exists 的用例

MySQL 中的 Not Exists 操作符是一个非常有用的工具，用于描述关于子查询中记录不存在的复杂条件。以下是 Not Exists 证明无价的常见用例：

在子查询中检查不存在性

例如，考虑您想查找所有非客户的情况。Not Exists 操作符提供了一种简洁的方式来解决这个问题。

SQL

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
   
);
INSERT INTO customers VALUES (1, 'Customer A');
INSERT INTO customers VALUES (2, 'Customer B');
INSERT INTO customers VALUES (3, 'Customer C');
INSERT INTO orders VALUES (101, 1, '2023-01-01');
INSERT INTO orders VALUES (102, 2, '2023-01-02');
INSERT INTO orders VALUES (103, 1, '2023-01-03');
SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

输出

在此查询中，将能够显示所有在“orders”表中没有订单记录的客户，并能有效表示查询中不存在的概念。

带 Not Exists 的相关子查询

使用 Not Exists 进行相关查询的一种更高级的方法是可能的，因为它能应用更广泛的条件，尤其是在将它们与外部问题中的列关联时。例如，查找未超出部门预算的员工。

SQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    department_id INT,
    employee_name VARCHAR(50)
   
);
CREATE TABLE budgets (
    budget_id INT PRIMARY KEY,
    department_id INT,
    amount_spent DECIMAL(10, 2),
    budget_limit DECIMAL(10, 2)
  
);
INSERT INTO employees VALUES (1, 101, 'Employee A');
INSERT INTO employees VALUES (2, 102, 'Employee B');
INSERT INTO employees VALUES (3, 101, 'Employee C');

INSERT INTO budgets VALUES (201, 101, 1500.00, 2000.00);
INSERT INTO budgets VALUES (202, 102, 1800.00, 2500.00);
INSERT INTO budgets VALUES (203, 101, 1000.00, 1500.00);
SELECT *
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM budgets b
    WHERE b.department_id = e.department_id
    AND b.amount_spent > b.budget_limit
);

输出

在这种情况下，Not Exists 与外层查询相关联，以便将员工的支出与部门预算限制进行比较。

与其他技术的比较

MySQL 提供了几种通过不存在性删除记录的方法，例如 Not Exists、<> 或 NOT IN。让我们来比较这些方法。

Not Exists 与 NOT IN

Not Exists

SQL

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
   
);
INSERT INTO customers VALUES (1, 'Customer A');
INSERT INTO customers VALUES (2, 'Customer B');
INSERT INTO customers VALUES (3, 'Customer C');
INSERT INTO orders VALUES (101, 1, '2023-01-01');
INSERT INTO orders VALUES (102, 2, '2023-01-02');
INSERT INTO orders VALUES (103, 1, '2023-01-03');
SELECT *
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

输出

不在其中

SQL

CREATE TABLE products (
    product_id INT,
    product_name VARCHAR(50)
);

INSERT INTO products (product_id, product_name) VALUES
    (1, 'Laptop'),
    (2, 'Smartphone'),
    (3, 'Headphones'),
    (4, 'Camera');

CREATE TABLE orders (
    order_id INT,
    product_id INT,
    quantity INT,
    sale_date DATE
);

INSERT INTO orders (order_id, product_id, quantity, sale_date) VALUES
    (101, 1, 2, '2023-02-01'),
    (102, 3, 1, '2023-02-15'),
    (103, 2, 3, '2023-02-20'),
    (104, 4, 1, '2023-03-05'),
    (105, 1, 1, '2023-03-10');

SELECT *
FROM products
WHERE product_id NOT IN (
    SELECT product_id
    FROM orders
    WHERE sale_date >= DATE_SUB(NOW(), INTERVAL 1 MONTH)
);

输出

比较

很多时候，Not Exists 更容易理解，并且能恰当地处理 NULL 值。
处理使用大型数据集时 NOT IN 可能出现的性能问题，以及小心管理 NULL 值至关重要。

关键注意事项

当清晰度、性能和处理 NULL 值很重要时，请使用 Not Exists。
对于更简单的场景，使用 <> 或 NOT IN，但要注意 NULL 处理。

高级示例

示例 1：检索没有订单的客户

SQL

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
    
);
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
    
);
INSERT INTO customers VALUES (1, 'Customer A');
INSERT INTO customers VALUES (2, 'Customer B');
INSERT INTO customers VALUES (3, 'Customer C');
INSERT INTO orders VALUES (101, 1, '2023-01-01');
INSERT INTO orders VALUES (102, 1, '2023-02-01');
INSERT INTO orders VALUES (103, 2, '2023-02-15');
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

输出

说明

在此，通过 LEFT JOIN 将客户与其订单进行匹配。检查 order_id 列中的 NULL 意味着 Not Exists，显示为没有订单。

示例 2：查找没有分配项目的员工

SQL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50)
  
);


CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    employee_id INT,
    project_name VARCHAR(50)
    
);


INSERT INTO employees VALUES (1, 'Employee A');
INSERT INTO employees VALUES (2, 'Employee B');
INSERT INTO employees VALUES (3, 'Employee C');


INSERT INTO projects VALUES (101, 1, 'Project X');
INSERT INTO projects VALUES (102, 1, 'Project Y');
INSERT INTO projects VALUES (103, 2, 'Project Z');


SELECT e.employee_id, e.employee_name
FROM employees e
LEFT JOIN projects p ON e.employee_id = p.employee_id
WHERE p.project_id IS NULL;

输出

说明

在这种情况下，查询通过左连接指定了所有未被分配到任何相关项目的员工，以及 Not Exists 条件，该条件省略了缺少任何相关项目对应记录的条目。条件逻辑和 Not Exists 子句。

示例 3：列出没有持续培训的员工。

SQL

CREATE TABLE cricketers (
    cricketer_id INT PRIMARY KEY,
    cricketer_name VARCHAR(50)
    
);


CREATE TABLE training_sessions (
    session_id INT PRIMARY KEY,
    cricketer_id INT,
    session_date DATE
    
);
INSERT INTO cricketers VALUES (1, 'Cricketer A');
INSERT INTO cricketers VALUES (2, 'Cricketer B');
INSERT INTO cricketers VALUES (3, 'Cricketer C');
INSERT INTO training_sessions VALUES (101, 1, CURDATE() - INTERVAL 1 DAY);
INSERT INTO training_sessions VALUES (102, 1, CURDATE() + INTERVAL 1 DAY);
INSERT INTO training_sessions VALUES (103, 2, CURDATE() - INTERVAL 2 DAY);
SELECT c.cricketer_id, c.cricketer_name
FROM cricketers c
WHERE NOT EXISTS (
    SELECT 1
    FROM training_sessions t
    WHERE t.cricketer_id = c.cricketer_id
    AND t.session_date >= CURDATE()
);

输出

说明

在此示例中，该查询识别出没有当前正在进行的训练的板球运动员，因为他们的 Not Exists 子查询中包含基于日期的条件。

优化 Not Exists 查询

因此，必须优化 Not Exists 查询，以提高数据库效率并减少其影响。以下是优化此类查询的策略和最佳实践：

索引策略

索引子查询中的列

确保 Not Exists 条件引用的所有列都已建立索引。这将对子查询的性能产生显著的改进。

SQL

索引主查询中的列

同样，如果主查询涉及列过滤，索引这些列也可以提高整体查询响应速度。

SQL

避免常见陷阱

NULL 处理

请小心处理 NULL 值。考虑使用 NULL 值的情况。Not Exists 可能更直观，但请务必确保子查询正确地处理了 NULL 值。

相关子查询

在使用相关子查询时，要仔细考虑它们对性能的影响。优化子查询，并在适用时利用其他选项，例如 JOIN。

子查询优化

子查询优化技术

将子查询重写为 JOIN，或使用 EXPLAIN 语句检查子查询执行计划并进行优化。

查询结果缓存

如果可能，请在 MySQL 中使用查询缓存，并将其与您的数据库/应用程序结合使用。它们通过减少后续查询的执行时间来提供很大帮助，而这些查询如果不使用缓存，执行时间会相对较高。

持续监控

使用一些性能分析工具频繁检查此查询。这还包括识别瓶颈，确保执行计划得到良好阐释，并在必要时进行改进。

Not Exists 的最佳实践

在 MySQL 查询中使用 Not Exists 应遵循最佳实践，以提高查询速度、可读性和整体效率。以下是需要考虑的关键最佳实践：

索引子查询中的列

应对 Not Exists 子查询的 WHERE 子句中使用的列建立索引。采用此方法将显著提高子查询的速度。

SQL

索引主查询中的列

同样，对主查询条件中的相关列建立索引，以提高整体效率。

SQL

考虑 NULL 值

在使用 Not Exists 和条件时，NULL 值是值得关注的。应在子查询条件中正确处理 NULL 值。

SQL 语法

SELECT column1, column2, ...
FROM table1
WHERE NOT EXISTS (
   
    SELECT 1
    FROM table2
    WHERE condition
);

评估替代方法

考虑 JOIN 和其他操作符

评估 JOIN 或任何其他选项是否可以在特定情况下提高可读性和性能。在某些情况下，替代方案可能效果更好或更快。

优先考虑可读性

在编写 Not Exists 查询时，请确保每个查询都易于理解和执行。简单易懂的查询可以很容易地进行维护或在发生故障时进行跟踪。

持续监控

使用常规的性能分析工具对 Not Exists 查询进行分析。识别可能的瓶颈点，检查执行计划，并在适用时进行必要的修改。

结论

总之，理解如何有效地使用 Not Exists 对于开发高效、易懂且快速的数据交换至关重要。仔细选择索引、正确处理缺失的属性以及在必要时使用替代方法是创建能够顺利运行的查询的关键。

Not Exists 在需要识别不存在匹配子查询记录的实例中展现了其灵活性，无论是在客户忠诚度分析还是在库存管理中。开发人员和 DBA 需要了解 Not Exists 的复杂性，并利用这一实践来提高 MySQL 数据库的效率，方法是加快所需查询的速度。

下一主题MySQL 在 Xampp 中无法启动

← 上一个下一个 →

MySQL Not Exists

MySQL 不存在简介

示例

理解 Not Exists

概念和语法

Not Exists 与 <> 或 NOT IN 的对比。

Not Exists 的用例

在子查询中检查不存在性

带 Not Exists 的相关子查询

与其他技术的比较

Not Exists 与 NOT IN

比较

高级示例

示例 1：检索没有订单的客户

示例 2：查找没有分配项目的员工

示例 3：列出没有持续培训的员工。

优化 Not Exists 查询

索引策略

避免常见陷阱

子查询优化

Not Exists 的最佳实践

索引子查询中的列

索引主查询中的列

考虑 NULL 值

评估替代方法

结论

联系信息

关注我们

教程

面试题

在线编译器

Python

Java

.Net Framework

AI, ML and Data Science

Cloud Technology

B.Tech and MCA

Web Technology

PHP

Software Testing

Technical Interview

Java Interview

Python

Web Interview

Database Interview

B.Tech / MCA

Important Interview

Software Testing Interview

Company Interviews

Online Compilers

Multiple Choice Questions

MySQL 教程

MySQL 数据库

表 & 视图

MySQL 查询

MySQL 子句

MySQL 条件

MySQL Join

MySQL Key

MySQL 权限

MySQL 索引

MySQL Triggers

用户管理

MySQL Workbench

FULLTEXT 搜索

正则表达式

MySQL 全球化

面试题

聚合函数

控制流函数

MSQL 字符串函数

MySQL 数学函数

MySQL 日期/时间函数

差异

MySQL 杂项

MySQL Not Exists

MySQL 不存在简介

示例

理解 Not Exists

概念和语法

Not Exists 与 <> 或 NOT IN 的对比。

Not Exists 的用例

在子查询中检查不存在性

带 Not Exists 的相关子查询

与其他技术的比较

Not Exists 与 NOT IN

比较

高级示例

示例 1：检索没有订单的客户

示例 2：查找没有分配项目的员工

示例 3：列出没有持续培训的员工。

优化 Not Exists 查询

索引策略

避免常见陷阱

子查询优化

Not Exists 的最佳实践

索引子查询中的列

索引主查询中的列

考虑 NULL 值

评估替代方法

结论

相关帖子

删除 MySQL 表中的所有数据

MySQL Said Documentation Cannot Connect to Invalid Settings

CRUD Android Studio MySQL

MySQL Alias

MySQL 中的复制是如何工作的

MySQL On Delete Cascade

MySQL SET

MySQL 排名函数

免费 MySQL 数据库