Subqueries Introduction
Learn how to use a query inside another SQL query to retrieve and analyze related data.
What is a Subquery?
A Subquery is a query written inside another SQL query. The inner query is called the subquery, and the outer query is called the main query.
Subqueries are useful when the result of one query is required as input for another query.
Basic Structure of a Subquery
SELECT column_name
FROM table_name
WHERE column_name = (
SELECT column_name
FROM another_table
WHERE condition
);
The inner query executes first and its result is then used by the outer query.
Simple Subquery Example
Suppose we have an Employees table:
EmployeeId EmployeeName Salary
----------- ------------- -------
1 Rahul 30000
2 Priya 45000
3 Amit 50000
4 Sneha 40000
Now suppose we want to find employees whose salary is greater than the average salary.
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees
);
How it works
- The inner query calculates the average salary.
- The outer query compares each employee's salary with that average.
- Employees whose salary is greater than the average are returned.
Subquery with WHERE
A subquery is commonly used with the WHERE clause to filter records based on the result of another query.
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > (
SELECT Salary
FROM Employees
WHERE EmployeeName = 'Rahul'
);
In this example, the inner query finds Rahul's salary. The outer query then returns employees whose salary is greater than Rahul's salary.
Subquery with IN
The IN operator can be used when a subquery returns multiple values.
SELECT EmployeeName
FROM Employees
WHERE DepartmentId IN (
SELECT DepartmentId
FROM Departments
WHERE Location = 'Pune'
);
The inner query returns department IDs for departments located in Pune. The outer query then returns employees belonging to those departments.
Types of Subqueries
| Type | Description |
|---|---|
| Single-Row Subquery | Returns a single value or single row. |
| Multiple-Row Subquery | Returns multiple rows or values. |
| Correlated Subquery | Depends on a value from the outer query. |
| Subquery with DML | Subqueries can be used with SELECT, INSERT, UPDATE and DELETE. |
Important Points
- A subquery is enclosed within parentheses.
- The inner query is written inside the outer query.
- A subquery can be used with WHERE, HAVING, FROM and other SQL clauses.
- The type of operator used depends on whether the subquery returns one value or multiple values.
- Subqueries are useful for solving complex SQL problems.
Summary
A subquery is a query inside another SQL query. It allows the result of one query to be used by another query. Subqueries are commonly used with WHERE and IN and are useful for filtering and analyzing related data.