Here is a complete list of Subqueries and all operators used with subqueries in WHERE clause, clearly separated 👇

🔹 1️⃣ Types of Subqueries

Single-row subquery

Multiple-row subquery

Multiple-column subquery

Correlated subquery

Nested subquery

🔹 2️⃣ Single-Row Subquery Operators

(Used when subquery returns only ONE value)

= 

> 

< 

>= 

<= 

<> 

!=

Example:

WHERE salary = (SELECT MAX(salary) FROM employees)

🔹 3️⃣ Multiple-Row Subquery Operators

(Used when subquery returns MULTIPLE values)

IN

NOT IN

ANY

SOME

ALL

IN

WHERE department_id IN 

(SELECT department_id FROM departments WHERE location = 'New York')

NOT IN

WHERE department_id NOT IN 

(SELECT department_id FROM departments WHERE location = 'New York')

ANY

Means: Compare with at least one value

WHERE salary > ANY 

(SELECT salary FROM employees WHERE department_id = 10)

SOME

Same as ANY

WHERE salary > SOME 

(SELECT salary FROM employees WHERE department_id = 10)

ALL

Means: Compare with all values

WHERE salary > ALL 

(SELECT salary FROM employees WHERE department_id = 10)

🔹 4️⃣ EXISTS Operators

EXISTS

NOT EXISTS

EXISTS

Returns TRUE if subquery returns any row.

WHERE EXISTS 

(SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id)

NOT EXISTS

WHERE NOT EXISTS 

(SELECT 1 FROM orders WHERE orders.customer_id = customers.customer_id)

🔹 5️⃣ Multiple-Column Subquery

Used with multiple columns.

WHERE (department_id, job_id) IN 

(SELECT department_id, job_id FROM employees)

🔹 6️⃣ Correlated Subquery

Subquery depends on outer query.

WHERE salary > 

(SELECT AVG(salary) 

 FROM employees e2 

 WHERE e2.department_id = e1.department_id)

🔹 7️⃣ Complete List of Subquery Operators (Quick View)

=, >, <, >=, <=, <>, !=

IN, NOT IN

ANY, SOME, ALL

EXISTS, NOT EXISTS