Subqueries in SQL

A subquery, also known as a nested query or inner query, is a query within another SQL query. It is used to retrieve data that will be used in the main query as a condition to further restrict the data to be retrieved. Subqueries can be used in SELECT, INSERT, UPDATE, and DELETE statements, as well as in clauses like WHERE, FROM, and HAVING.

​

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name OPERATOR (SELECT column_name FROM table_name WHERE condition);

​

Types of Subqueries

1. Single-row subquery: Returns only one row.
2. Multi-row subquery: Returns more than one row.
3. Correlated subquery: A subquery that depends on the outer query for its values.

​

Single-Row Subqueries

A single-row subquery returns only one row of data. It is often used with comparison operators like =, >, <, >=, <=, and <>.

Example: Find the employee with the highest salary

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

The subquery (SELECT MAX(salary) FROM employees) returns the highest salary from the employees table. The main query then uses this value to find the employee(s) with that salary.

​

Multi-Row Subqueries

A multi-row subquery returns more than one row of data. It is often used with operators like IN, ANY, ALL, and EXISTS.

Example: Find employees who work in the same district as Anand

SELECT name, district
FROM employees
WHERE district IN (SELECT district FROM employees WHERE name = 'Anand');

The subquery (SELECT district FROM employees WHERE name = 'Anand') returns the district where Anand works. The main query then finds all employees who work in that district.

​

Correlated Subqueries

A correlated subquery is a subquery that depends on the outer query for its values. It is executed repeatedly, once for each row that is evaluated by the outer query.

Example: Find employees whose salary is greater than the average salary in their district

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

The subquery (SELECT AVG(salary) FROM employees e2 WHERE e2.district = e1.district) calculates the average salary for each district. The main query then compares each employee’s salary to the average salary in their district.

​

Practical Use Cases

1. Filtering Data: Subqueries can be used to filter data based on conditions that are derived from other tables or the same table.
2. Data Aggregation: Subqueries can be used to perform calculations or aggregations that are then used in the main query.
3. Data Comparison: Subqueries can be used to compare data between different tables or within the same table.

​

Example Database

Let’s assume we have an employees table with the following structure:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    salary DECIMAL(10, 2),
    district VARCHAR(50),
    state VARCHAR(50)
);

Sample Data:

​

Example Queries Using Subqueries

​

1. Single-Row Subquery

Find the employee with the highest salary

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

Output:

The subquery (SELECT MAX(salary) FROM employees) finds the highest salary in the employees table, which is 95000. The main query then retrieves the employee(s) with that salary.

​

2. Multi-Row Subquery

Find employees who work in the same district as Anand

SELECT name, district
FROM employees
WHERE district IN (SELECT district FROM employees WHERE name = 'Anand');

Output:

The subquery (SELECT district FROM employees WHERE name = 'Anand') returns Karaikal, the district where Anand works. The main query then retrieves all employees who work in Karaikal.

​

3. Multi-Row Subquery with IN

Find employees who work in districts located in Tamil Nadu

SELECT name, district
FROM employees
WHERE district IN (SELECT district FROM employees WHERE state = 'Tamil Nadu');

Output:

The subquery (SELECT district FROM employees WHERE state = 'Tamil Nadu') returns all districts in Tamil Nadu. The main query then retrieves employees who work in those districts.

​

4. Correlated Subquery

Find employees whose salary is greater than the average salary in their district

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

Output:

The subquery (SELECT AVG(salary) FROM employees e2 WHERE e2.district = e1.district) calculates the average salary for each district. The main query compares each employee’s salary to the average salary in their district and retrieves those who earn more.

​

5. Subquery in SELECT

Display each employee’s salary along with the average salary of their state

SELECT name, salary, state, 
       (SELECT AVG(salary) FROM employees e2 WHERE e2.state = e1.state) AS avg_state_salary
FROM employees e1;

Output:

The subquery (SELECT AVG(salary) FROM employees e2 WHERE e2.state = e1.state) calculates the average salary for each state. This value is displayed alongside each employee’s details.

​

6. Subquery in HAVING

Find districts where the average salary is greater than the overall average salary

SELECT district, AVG(salary) AS avg_district_salary
FROM employees
GROUP BY district
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

Output:

The subquery (SELECT AVG(salary) FROM employees) calculates the overall average salary. The main query groups employees by district and retrieves districts where the average salary is greater than the overall average.

​

7. Subquery in FROM

Subquery in FROM clause is called as Derived table in SQL Server.

Find the top 3 highest-paid employees

SELECT name, salary
FROM (SELECT name, salary FROM employees ORDER BY salary DESC) AS top_employees
LIMIT 3;

Output:

The subquery (SELECT name, salary FROM employees ORDER BY salary DESC) retrieves all employees sorted by salary in descending order. The main query then limits the result to the top 3 rows.

​

Key Takeaways

1. Subqueries are queries nested inside another query and can be used in SELECT, INSERT, UPDATE, and DELETE statements.
2. Single-row subqueries are useful for comparisons with a single value (e.g., finding the highest salary).
3. Multi-row subqueries are ideal for filtering data based on multiple values (e.g., finding employees in specific districts).
4. Correlated subqueries allow you to compare data within the same table (e.g., comparing salaries within districts).
5. Always test subqueries independently to ensure they return the expected results before integrating them into the main query.