1️⃣ Single-Row Subqueries

(Returns ONE value)

= Operator

Employee with highest salary

SELECT emp_name, salary

FROM Employee

WHERE salary = (SELECT MAX(salary) FROM Employee);

> Operator

Employees earning more than average salary

SELECT emp_name, salary

FROM Employee

WHERE salary > (SELECT AVG(salary) FROM Employee);

< Operator

Employees earning less than average salary

SELECT emp_name, salary

FROM Employee

WHERE salary < (SELECT AVG(salary) FROM Employee);

>= Operator

SELECT emp_name, salary

FROM Employee

WHERE salary >= (SELECT AVG(salary) FROM Employee);

<= Operator

SELECT emp_name, salary

FROM Employee

WHERE salary <= (SELECT AVG(salary) FROM Employee);

<> or != Operator

Employees not earning minimum salary

SELECT emp_name, salary

FROM Employee

WHERE salary <> (SELECT MIN(salary) FROM Employee);

 2️⃣ Multiple-Row Subqueries

🔹 IN

Employees working in departments located in Pune

SELECT emp_name

FROM Employee

WHERE dept_id IN (

    SELECT dept_id

    FROM Department

    WHERE location = 'Pune'

);

🔹 NOT IN

Employees not in HR department

SELECT emp_name

FROM Employee

WHERE dept_id NOT IN (

    SELECT dept_id

    FROM Department

    WHERE dept_name = 'HR'

);

🔹 ANY

Employees earning more than ANY employee in dept 3

SELECT emp_name, salary

FROM Employee

WHERE salary > ANY (

    SELECT salary

    FROM Employee

    WHERE dept_id = 3

);

🔹 SOME (same as ANY)

SELECT emp_name, salary

FROM Employee

WHERE salary > SOME (

    SELECT salary

    FROM Employee

    WHERE dept_id = 3

);

🔹 ALL

Employees earning more than ALL employees in dept 3

SELECT emp_name, salary

FROM Employee

WHERE salary > ALL (

    SELECT salary

    FROM Employee

    WHERE dept_id = 3

);

 3️⃣ EXISTS / NOT EXISTS

🔹 EXISTS

Employees who have appraisal records

SELECT emp_name

FROM Employee e

WHERE EXISTS (

    SELECT 1

    FROM Appraisal a

    WHERE a.emp_id = e.emp_id

);

🔹 NOT EXISTS

Employees without appraisal records

SELECT emp_name

FROM Employee e

WHERE NOT EXISTS (

    SELECT 1

    FROM Appraisal a

    WHERE a.emp_id = e.emp_id

);

4️⃣ Multiple-Column Subquery

Employees whose (dept_id, salary) matches another employee

SELECT emp_name

FROM Employee

WHERE (dept_id, salary) IN (

    SELECT dept_id, salary

    FROM Employee

);

(Useful when comparing composite values.)

 5️⃣ Correlated Subquery

Employees earning more than their department’s average salary

SELECT emp_name, salary, dept_id

FROM Employee e1

WHERE salary > (

    SELECT AVG(salary)

    FROM Employee e2

    WHERE e2.dept_id = e1.dept_id

);

6️⃣ Nested Subquery

Employees in department located in Mumbai

SELECT emp_name

FROM Employee

WHERE dept_id = (

    SELECT dept_id

    FROM Department

    WHERE location = 'Mumbai'

);