What is SQL subqueries?

 

SQL subqueries (also called inner queries or nested queries) are queries written inside another SQL query.
They help you fetch data that depends on the result of another query.

---

✅ Definition

A subquery is a query inside:

• SELECT
• FROM
• WHERE
• HAVING

The outer query uses the result of the inner query.

---

✅ Why use subqueries?

• To filter results based on another table
• To compare values
• To replace joins in some cases
• To calculate aggregated values (MAX, MIN, AVG, COUNT) and use them in conditions

---

✅ Types of Subqueries

1. Subquery in WHERE clause

Used to filter data based on another query.

Example:

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Find employees earning more than the average salary.

---

2. Subquery in SELECT clause

Used to show extra calculated values.

SELECT name,
       (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

---

3. Subquery in FROM clause (Inline view)

Treats a subquery like a temporary table.

SELECT dept, avg_salary
FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg;

---

4. Correlated Subquery

Runs row-by-row — It depends on the outer query.

SELECT name
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department
);

---

✅ Simple Example

Find students who scored above the class average.

SELECT student_name
FROM students
WHERE marks > (SELECT AVG(marks) FROM students);

---

⭐ Key Points

• Subquery must be enclosed in ( ) brackets
• Can return single or multiple values
• Can be used with operators like:
  IN, NOT IN, ANY, ALL, EXIST.

Here is a deeper, clearer, and more structured explanation of SQL subqueries with more examples and practice.

---

📘 SQL Subqueries — Detailed Notes

A subquery is a query written inside another query to help perform advanced filtering, comparison, or data extraction.

---

🔹 How Subqueries Return Data

Subqueries return data in three forms:

1. Single value (Scalar Subquery)

Returns one row, one column.

Example:

SELECT name
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee);

---

2. Multiple values (Column Subquery)

Returns one column, multiple rows, used with IN or ANY.

Example:

SELECT name
FROM employee
WHERE department IN (SELECT department FROM departments WHERE location = 'Delhi');

---

3. Table result (Table Subquery)

Returns multiple rows and columns, used inside the FROM clause.

Example:

SELECT *
FROM (SELECT name, salary FROM employee WHERE salary > 50000) AS high_paid;

---

🔵 Types of SQL Subqueries

1. Subquery in WHERE clause

Used for filtering.

SELECT name
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);

---

2. Subquery in SELECT clause

Used to add a calculated value to each row.

SELECT name,
       (SELECT COUNT(*) FROM employee) AS total_employees
FROM employee;

---

3. Subquery in FROM clause

Used to create a temporary table.

SELECT dept, avg_salary
FROM (
    SELECT department AS dept, AVG(salary) AS avg_salary
    FROM employee
    GROUP BY department
) AS temp;

---

4. Correlated Subquery

🔸 Runs for each row of the outer query
🔸 Depends on the outer query
🔸 Slower but more powerful

Example: Find employees earning more than the average salary of their department:

SELECT name
FROM employee e
WHERE salary > (
    SELECT AVG(salary)
    FROM employee
    WHERE department = e.department
);

---

🟢 Subquery with EXISTS

EXISTS checks if the subquery returns at least one row.

Example: Show customers who have placed an order:

SELECT customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

---

🟣 Subquery with IN

IN checks if a value matches a list from the subquery.

SELECT name
FROM employee
WHERE department IN (SELECT dept_name FROM departments);

---

🟠 Subquery with ANY / ALL

ANY

Condition must match at least one value.

Example: Employees whose salary is greater than any manager’s salary:

SELECT name
FROM employee
WHERE salary > ANY (SELECT salary FROM managers);

ALL

Condition must match all values.

SELECT name
FROM employee
WHERE salary > ALL (SELECT salary FROM managers);

---

📋 When to Use Subqueries?

Use subqueries when:

• You need results that depend on another result
• You want to avoid joins
• You need complex filtering (like top-performing items)
• You need row-by-row comparison (correlated subquery)

---

⭐ Quick Real-Life Examples

1. Find products priced above category average

SELECT product_name
FROM products p
WHERE price > (
    SELECT AVG(price)
    FROM products
    WHERE category = p.category
);

2. Find students scoring above class average

SELECT student_name
FROM students
WHERE marks > (SELECT AVG(marks) FROM students);

3. Find employees who never made a sale

SELECT name
FROM employee e
WHERE NOT EXISTS (
    SELECT 1
    FROM sales s
    WHERE s.emp_id = e.emp_id
);

---

💡 Advantages of Subqueries

✔ Easy to understand
✔ Makes complex queries simple
✔ Helps in dynamic conditions (AVG, MAX, etc.)
✔ Useful when comparing with other tables

⚠️ Disadvantages

✘ Sometimes slower than JOIN
✘ Hard to optimize for large datasets

---

📝 Practice Questions

Want answers too? Just tell me.

1. Find employees earning more than the minimum salary in the company.
2. Find the second highest salary using subquery.
3. List students whose marks are above class average.
4. Find departments where more than 5 employees work using a subquery.
5. Show customers who did not place any order.