SQL Subqueries

A subquery is a SQL query placed inside another SQL query.

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

Here, the inner query calculates the average marks and the outer query finds students having marks greater than the average.

A subquery is normally written inside parentheses.

SELECT column_name
FROM table_name
WHERE column_name = (
    SELECT column_name
    FROM another_table
);

The inner query executes to provide a value or set of values to the outer query.

Subqueries are commonly used inside the WHERE clause.

SELECT name, marks
FROM students
WHERE marks > (
    SELECT AVG(marks)
    FROM students
);

This returns students whose marks are above the average marks.

A scalar subquery returns a single value.

SELECT name
FROM students
WHERE marks = (
    SELECT MAX(marks)
    FROM students
);

The inner query returns one value: the highest marks.

The IN operator can be used when a subquery returns multiple values.

SELECT name
FROM students
WHERE course_id IN (
    SELECT id
    FROM courses
    WHERE fee > 10000
);

This finds students enrolled in courses whose fee is greater than ₹10,000.

NOT IN can be used to exclude values returned by a subquery.

SELECT name
FROM students
WHERE course_id NOT IN (
    SELECT id
    FROM courses
    WHERE fee > 10000
);

This returns students whose course is not among the courses having fees above ₹10,000.

ANY compares a value with any value returned by the subquery.

SELECT name, marks
FROM students
WHERE marks > ANY (
    SELECT marks
    FROM students
    WHERE course_id = 2
);

The condition is true when the student's marks are greater than at least one value returned by the subquery.

ALL compares a value with every value returned by the subquery.

SELECT name, marks
FROM students
WHERE marks > ALL (
    SELECT marks
    FROM students
    WHERE course_id = 2
);

The condition is true only when the marks are greater than all returned values.

EXISTS checks whether the subquery returns at least one record.

SELECT name
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM payments p
    WHERE p.student_id = s.id
);

This returns students who have at least one payment record.

NOT EXISTS checks whether the subquery returns no records.

SELECT name
FROM students s
WHERE NOT EXISTS (
    SELECT 1
    FROM payments p
    WHERE p.student_id = s.id
);

This can be used to find students who have no payment records.

A scalar subquery can be used with operators such as =, >, <, >=, <=.

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

This finds employees earning more than the average salary.

A subquery can be placed in the FROM clause.

SELECT *
FROM (
    SELECT name, marks
    FROM students
) AS student_data;

The result of the subquery is treated like a temporary table.

A subquery used in the FROM clause is called a derived table.

SELECT course_id, AVG(marks) AS average_marks
FROM (
    SELECT course_id, marks
    FROM students
) AS data
GROUP BY course_id;

The derived table provides temporary data for the outer query.

A subquery can also be used in the SELECT list.

SELECT
    name,
    marks,
    (SELECT AVG(marks) FROM students) AS average_marks
FROM students;

The average marks are displayed along with every student.

A correlated subquery depends on a value from the outer query.

SELECT s.name, s.marks
FROM students s
WHERE s.marks > (
    SELECT AVG(s2.marks)
    FROM students s2
    WHERE s2.course_id = s.course_id
);

Here, the inner query uses the course_id from the outer query.

A non-correlated subquery works independently of the outer query.

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

The inner query does not depend on the current row of the outer query.

Aggregate functions such as COUNT() can be used inside subqueries.

SELECT name
FROM courses
WHERE id IN (
    SELECT course_id
    FROM students
    GROUP BY course_id
    HAVING COUNT(*) > 10
);

This finds courses having more than 10 students.

AVG() can calculate an average value inside a subquery.

SELECT name, marks
FROM students
WHERE marks > (
    SELECT AVG(marks)
    FROM students
);

This returns students whose marks are above the overall average.

MAX() and MIN() can be used to compare records with the highest or lowest value.

SELECT name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

This returns the employee or employees having the highest salary.

A subquery can contain GROUP BY to produce grouped results.

SELECT *
FROM (
    SELECT course_id, COUNT(*) AS total_students
    FROM students
    GROUP BY course_id
) AS course_summary;

This creates a temporary summary of students by course.

HAVING can filter grouped results inside a subquery.

SELECT *
FROM (
    SELECT course_id, AVG(marks) AS average_marks
    FROM students
    GROUP BY course_id
    HAVING AVG(marks) > 60
) AS result;

This returns courses whose average marks are greater than 60.

A subquery can be used with an UPDATE statement.

UPDATE students
SET status = 'Excellent'
WHERE marks > (
    SELECT AVG(marks)
    FROM (
        SELECT marks
        FROM students
    ) AS temp
);

This example updates students whose marks are above the average.

A subquery can help identify records to delete.

DELETE FROM students
WHERE course_id IN (
    SELECT id
    FROM courses
    WHERE status = 'Inactive'
);

Always test the subquery with SELECT before performing a DELETE operation.

A subquery can be used with INSERT INTO ... SELECT.

INSERT INTO top_students (name, marks)
SELECT name, marks
FROM students
WHERE marks > (
    SELECT AVG(marks)
    FROM students
);

This copies students having above-average marks into another table.

Subqueries and joins can be used together.

SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c
ON s.course_id = c.id
WHERE s.marks > (
    SELECT AVG(marks)
    FROM students
);

This displays course information for students whose marks are above average.

A subquery can contain another subquery.

SELECT name
FROM students
WHERE course_id IN (
    SELECT id
    FROM courses
    WHERE fee > (
        SELECT AVG(fee)
        FROM courses
    )
);

Here, one subquery is nested inside another subquery.

• Forgetting parentheses around a subquery.
• Using = when the subquery returns multiple values.
• Using IN when a single value is expected without understanding the result.
• Forgetting an alias for a derived table.
• Using a subquery that returns incompatible columns or data types.

Always test the inner query separately before combining it with the outer query.

Both subqueries and joins can solve many similar problems, but they are written differently.

-- Using a subquery
SELECT name
FROM students
WHERE course_id IN (
    SELECT id
    FROM courses
    WHERE fee > 10000
);

-- Using a JOIN
SELECT s.name
FROM students s
INNER JOIN courses c
ON s.course_id = c.id
WHERE c.fee > 10000;

The best approach depends on the query requirement, readability and database design.

Suppose we have a students table with name, marks and course_id.

SELECT name, marks
FROM students
WHERE marks > (
    SELECT AVG(marks)
    FROM students
    WHERE course_id = 1
);

This finds students whose marks are greater than the average marks of students in course 1.

Consider two tables: students and courses.

CREATE TABLE courses (
    id INT PRIMARY KEY,
    course_name VARCHAR(100),
    fee DECIMAL(10,2)
);

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    marks INT,
    course_id INT
);

Now find students enrolled in courses whose fee is greater than the average course fee:

SELECT name, marks
FROM students
WHERE course_id IN (
    SELECT id
    FROM courses
    WHERE fee > (
        SELECT AVG(fee)
        FROM courses
    )
);

This example demonstrates nested subqueries and shows how the result of one query can be used by another query.