MariaDB Tutorial / Use Subqueries, EXISTS, and NOT EXISTS

A subquery provides a value or row set inside another SQL statement. EXISTS and NOT EXISTS test whether related rows do or do not exist, making them useful for selecting each customer once or finding customers without a qualifying paid order.

Decide whether the outer query needs a value, list or existence test

A product-price comparison with the average needs one number. A customer qualification needs only to establish whether a related paid order exists. Choose the required output shape and preserve the outer row’s meaning instead of applying one subquery form to every problem. See Compare NULL Correctly with IS NULL and COALESCE for NULL inside NOT IN. Testing existence differs from combining result lists vertically with Combine Query Results with UNION and UNION ALL.

Check the MariaDB documentation for the syntax and conditions used here: subqueries and exists.

Prepare distinct customer histories

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating c29_customers, c29_products, c29_orders, c29_items. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

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

The SQL retains Korean sample names and city strings, while the explanations use stable customer and product IDs. Products 1–4 are a notebook, pen, mug and lamp. Customers 1 and 3 have the Seoul city value; customers 2 and 4 have Busan and Daejeon values. Preserve the supplied UTF-8 literals when reproducing the results.

Customer 1 has two paid orders, customer 2 one paid order, customer 3 only a canceled order and customer 4 none. These examples select a customer once instead of returning a separate row for every matching order.

Compare a price with a scalar aggregate

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

The four-product average is 9500, so only the lamp, ID 4, is above it. AVG turns the product population into one value. Returning several unaggregated prices at this scalar position causes a multiple-row error. Define the intended comparison value rather than hide an undefined choice with LIMIT 1.

Use correlated EXISTS without multiplying customers

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;

Customers 1 and 2 each appear once. SELECT 1 supplies a value for the existence test, not an order amount. Customer 1’s second order does not duplicate the outer customer row. Without o.customer_id=c.customer_id, the subquery would instead test whether any paid order exists anywhere.

A correlated subquery references the current outer key. That logical explanation does not dictate the optimizer’s physical strategy; do not assume every execution fully rescans the inner table for every outer row.

Define the absence condition with 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;

Customers 3 and 4 have no paid orders. A canceled-only customer and a customer with no orders both qualify. Removing the status condition instead asks for no order of any status, which returns only customer 4. Describe the absence in a sentence before writing the filter.

NOT IN can behave differently when its inner list contains NULL. Review nullable comparison keys and whether outer NULL rows should qualify rather than assume a universal equivalence with NOT EXISTS. Nor does choosing EXISTS guarantee better performance than a JOIN.

Inspect subquery shape, correlation and the actual plan

Run the inner query separately to inspect values, duplicates and NULLs, then connect it to one known outer example. If every customer gets the same outcome, inspect the correlation condition. Avoid unnecessary selected columns or ordering in a simple existence test. Evaluate performance using representative data and the actual execution plan rather than a rule that one syntax is always faster.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

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