? MySQL SubQuery with EXISTS & NOT EXISTS

? Quick Overview

A subquery is a query nested inside another query. In MySQL, EXISTS and NOT EXISTS are used to check whether a related record exists or not.

? Key Concepts

• Subquery execution depends on outer query rows
• EXISTS checks presence of rows
• NOT EXISTS checks absence of rows

? Syntax / Theory

// Basic EXISTS subquery syntax
SELECT column1
FROM table1
WHERE EXISTS (
SELECT 1
FROM table2
WHERE condition
);

? Tables Used

// Customers master data
customer_id | customer_name
1 | Alice
2 | Bob
3 | Carol

// Orders placed by customers
order_id | customer_id | amount
1 | 1 | 100
2 | 2 | 200
3 | 1 | 300

⚙️ EXISTS Example

// Find customers having at least one order
SELECT customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);

? Output Explanation

This query returns Alice and Bob because both appear in the orders table.

⚙️ NOT EXISTS Example

// Find customers without any orders
SELECT customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);

? Interactive Logic Flow

For each customer row, MySQL checks the subquery. If a match is found, EXISTS returns true. If no match is found, NOT EXISTS returns true.

? Use Cases

• Finding missing relationships
• Validating data presence
• Conditional updates and deletes