-- ============================================

-- IN OPERATOR WITH SUBQUERY

-- ============================================

-- Basic IN: Find customers who placed orders

SELECT

customer_id,

name,

email

FROM customers

WHERE customer_id IN (

SELECT customer_id

FROM orders

);

-- How it works:

-- 1. Subquery returns list: (1, 3, 5, 7, 9, ...)

-- 2. WHERE checks if customer_id is in that list

-- 3. Only matching customers returned

-- IN with filtering: Customers with high-value orders

SELECT name, email

FROM customers

WHERE customer_id IN (

SELECT customer_id

FROM orders

WHERE total > 1000

);

-- IN with multiple conditions

SELECT product_name, price

FROM products

WHERE category_id IN (

SELECT id

FROM categories

WHERE active = true

AND parent_category = 'Electronics'

);

-- ============================================

-- EXISTS OPERATOR WITH SUBQUERY

-- ============================================

-- Basic EXISTS: Find customers who placed orders

SELECT

c.customer_id,