SQL Interview Questions

A subquery, also known as an inner query or nested query, is a query embedded within another SQL query. It is used to return data that will be used in the main query, often referred to as the outer query. Subqueries can be used in various parts of a SQL statement, such as the SELECT, FROM, WHERE, HAVING, and JOIN clauses.

Types of Subqueries

1. Single-Row Subqueries: Returns a single row and is typically used with comparison operators like =, >, <, >=, <=, and <>.
2. Multiple-Row Subqueries: Returns multiple rows and is used with operators like IN, ANY, ALL, and EXISTS.
3. Correlated Subqueries: A subquery that references columns from the outer query. It is evaluated once for each row processed by the outer query.
4. Nested Subqueries: Subqueries within subqueries.

How to Use Subqueries in SQL

1. Subquery in the SELECT Clause

To calculate an aggregated value and include it in the result set:

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

Result:

+---------+------------+
| name    | avg_salary |
+---------+------------+
| Alice   | 62000      |
| Bob     | 62000      |
| Charlie | 62000      |
| David   | 62000      |
| Eve     | 62000      |
+---------+------------+

2. Subquery in the FROM Clause

To use the result of one query as a temporary table for another query:

SELECT dept_id, AVG(salary)
FROM (SELECT dept_id, salary FROM employees WHERE salary > 50000) AS high_salary_employees
GROUP BY dept_id;

Result:

+---------+------------+
| dept_id | avg_salary |
+---------+------------+
| 1       | 55000      |
| 2       | 65000      |
| 3       | 75000      |
+---------+------------+

3. Subquery in the WHERE Clause

To filter rows based on a condition in another query:

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

Result:

+-------+
| name  |
+-------+
| Bob   |
| David |
| Eve   |
+-------+

4. Subquery in the HAVING Clause

To filter groups based on a condition involving aggregated data:

SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

Result:

+---------+------------+
| dept_id | avg_salary |
+---------+------------+
| 2       | 65000      |
| 3       | 75000      |
+---------+------------+

5. Correlated Subquery

To filter rows based on a condition involving the current row of the outer query:

SELECT name
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.dept_id = e2.dept_id);

Result:

+-------+
| name  |
+-------+
| Bob   |
| Eve   |
+-------+

Example Use Case

Suppose we have two tables, employees and departments:

employees
+----+---------+--------+---------+
| id | name    | salary | dept_id |
+----+---------+--------+---------+
| 1  | Alice   | 50000  | 1       |
| 2  | Bob     | 60000  | 2       |
| 3  | Charlie | 55000  | 1       |
| 4  | David   | 70000  | 2       |
| 5  | Eve     | 75000  | 3       |
+----+---------+--------+---------+

departments
+----+-------------+
| id | dept_name   |
+----+-------------+
| 1  | HR          |
| 2  | Engineering |
| 3  | Sales       |
+----+-------------+

To find employees who earn more than the average salary of their department:

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

Result:

+-------+
| name  |
+-------+
| Bob   |
| Eve   |
+-------+

Summary

• Subqueries: Used to perform complex queries, nesting one query inside another.
• Types: Single-row, multiple-row, correlated, and nested subqueries.
• Usage: In SELECT, FROM, WHERE, HAVING, and JOIN clauses to filter, aggregate, and compare data.
• Correlated Subqueries: References columns from the outer query and is evaluated for each row processed by the outer query.

Subqueries are powerful tools in SQL that enable complex data retrieval and manipulation, providing flexibility and depth to SQL query capabilities.