MariaDB 教程 / 用子查询、EXISTS 和 NOT EXISTS 表达条件

子查询是在另一条 SQL 中提供值或结果集的查询。EXISTS 与 NOT EXISTS 判断相关记录是否存在,适合把每位客户只选一次,或查找没有符合条件的已支付订单的客户。

先确定需要一个值、一组值还是存在性判断

把商品价格与平均价比较,需要一个数值;判断客户是否有已支付订单,只需要知道是否存在匹配记录。应按外层问题选择结果形式,保持外层一行的含义,而不是把同一种子查询写法套在所有任务上。

形式 需要的结果 重点检查
标量子查询 用于表达式中的单个值 必须是一列;多于一行会报错,没有行时为 NULL
IN 子查询 一组供比较的值 检查比较列及其中的 NULL
EXISTS 是否至少存在一条符合条件的行 相关记录应包含正确的关联键
NOT EXISTS 是否完全没有符合条件的行 明确需要不存在的是哪一种记录

规则可对照 MariaDB 官方的 EXISTS 文档和 标量子查询文档。涉及 NOT IN 时,还应结合 NULL 比较规则,不能把所有“没有”都理解成相同的逻辑。

准备订单历史不同的独立样本

以 course_user 连接学习服务器,先检查 SELECT VERSION(), DATABASE(); 与 SHOW TABLES;。本节使用 c29_customers、c29_products、c29_orders、c29_items 四张表;已有同名对象时,先核对表结构与记录,不要覆盖用途未知的数据。

下面包含完整建表与插入,应在 MariaDB 客户端执行。也可保存为 UTF-8 文件,按照 SQL 文件与 SOURCE 教程提供实际路径加载。先建立客户、商品,再建立引用它们的订单与明细。

USE shopcourse;
CREATE TABLE c29_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c29_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c29_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c29_order_customer FOREIGN KEY (customer_id)
  REFERENCES c29_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c29_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c29_item_order FOREIGN KEY (order_id)
  REFERENCES c29_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c29_item_product FOREIGN KEY (product_id)
  REFERENCES c29_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c29_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c29_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c29_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c29_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

姓名、城市与商品名保留原始韩文字符串,用稳定编号解释结果。商品 1~4 分别是笔记本、圆珠笔、马克杯、台灯;客户 1、3 的城市表示首尔,2 表示釜山,4 表示大田。复现时请保持 UTF-8 字面量一致。

客户 1 有两个 paid 订单,客户 2 有一个 paid 订单,客户 3 只有 canceled 订单,客户 4 没有订单。与每单一行的连接查询不同,下面的存在性查询保持外层“每位客户一行”的形式。

用标量聚合结果比较商品价格

SELECT product_id,product_name FROM c29_products
WHERE price > (SELECT AVG(price) FROM c29_products) ORDER BY product_id;

四件商品平均价为 9500,只有编号 4 的台灯高于平均价。AVG 把商品集合汇总成一个值,外层再用各行 price 与这个值比较。可单独执行内层 SELECT AVG(price),确认阈值后再核对外层商品编号。

如果把内层 AVG(price) 改成未聚合的 price,内层返回四行,在这个标量位置会出现错误 1242。应定义真正需要的比较值,而不是随意加 LIMIT 1 隐藏未定义的选择。只有当业务确实规定了唯一的一行及其选择顺序时,才应按该规则取值。

标量子查询没有任何行时,值是 NULL,即使源列本来不允许 NULL。此时普通的大于比较不会变为 TRUE,不能把空结果想当然当作零。排查外层没有结果时,应先确认内层究竟返回零行、一行还是多行。

用相关 EXISTS 选择客户,不扩成订单明细

SELECT c.customer_id,c.customer_name FROM c29_customers c
WHERE EXISTS (SELECT 1 FROM c29_orders o
 WHERE o.customer_id=c.customer_id AND o.status='paid')
ORDER BY c.customer_id;

结果为客户 1、2,各出现一次。客户 1 有两个 paid 订单,但外层并没有把每个订单作为独立结果输出,所以不需要用 DISTINCT 修补重复客户。这里关注的是是否至少有一条匹配,而不是匹配了几条。

o.customer_id=c.customer_id 是关联当前外层客户的关键。如果只删去这个条件,内层会变成检查“全表任何位置是否有 paid 订单”;因为样本中确实存在,四位客户都会通过。核对这个差异后恢复关联键,再确认结果回到 1、2。

SELECT 1 是存在性查询中的占位值,不表示订单金额或数量。即使把它改成 SELECT NULL,只要仍返回一行,EXISTS 就为真;“返回的值为 NULL”与“没有返回行”不同。

相关子查询在逻辑上引用当前外层键,但这不规定优化器必须按某一种物理扫描方法执行。不要从写法直接推断每位客户都必然完整重扫一次订单表。

用 NOT EXISTS 明确哪一种记录不存在

SELECT c.customer_id,c.customer_name FROM c29_customers c
WHERE NOT EXISTS (SELECT 1 FROM c29_orders o
 WHERE o.customer_id=c.customer_id AND o.status='paid')
ORDER BY c.customer_id;

结果为客户 3、4,它们都没有 paid 订单。客户 3 有取消订单,客户 4 完全没下过单,但两者都符合当前问题。只移除状态条件后,问题变为“没有任何状态的订单”,结果应只剩客户 4。

写条件前,可以先完整描述:“选择不存在属于该客户、且状态为 paid 的订单的客户。”这样能同时检查关联键和状态范围。对照 LEFT JOIN 的无匹配查询时,也应保证两种写法回答的是同一个问题。

NOT IN 的内层列表若含 NULL,可能得到未知结果。例如 4 与列表 1、NULL 比较时,NOT IN 不会直接得到 TRUE,而相应的无匹配存在性判断可以为真。还应确定外层 NULL 键是否允许、是否应被保留;不要声称 NOT IN 与 NOT EXISTS 在任意可空数据上都等价。

检查结果形状、关联键与实际执行条件

先单独运行内层查询,检查列数、行数、重复值与 NULL,再把它与一位已知客户对应。若每位客户都得到相同判断,优先检查是否漏掉关联条件;若标量比较报错,先确认内层是否真的只有一个值。

简单存在性测试不需要为了显示订单属性而选出许多列,也不应把没有用途的排序当成逻辑必需条件。选择 EXISTS 不保证一定比 JOIN 更快;应使用代表性数据与实际执行计划评估性能,先保证结果人群和含义正确。

本节所有查询都不改写四张源表。需要重练时,先检查定义与记录,备份需要保留的内容。下面只删除已确认可丢弃的 c29_ 四表,先子表后父表;DROP TABLE 会移除定义与全部记录,普通 ROLLBACK 无法恢复,清理应与事务实验分开。

DROP TABLE shopcourse.c29_items;
DROP TABLE shopcourse.c29_orders;
DROP TABLE shopcourse.c29_products;
DROP TABLE shopcourse.c29_customers;