A SQL Subquery (also known as a nested query) is a query placed inside another query. It allows you to use the result of one query as input for another, making SQL more flexible and powerful. Subqueries are often used to filter, calculate, or transform data in complex scenarios.
- A subquery is written inside parentheses.
- It can be placed in SELECT, FROM, or WHERE clauses.
- Subqueries help break down complex problems into smaller, logical steps.
The inner query calculates the average salary, and the outer query retrieves salaries above that.
Practice: Write a query to get the sales which are greater than the average sales.
Subquery in SELECT Clause
You can use a subquery in the SELECT clause to add calculated values.
This query will show employee id and company-wide average salary.
Practice: Write a query to get the sales id and the average sales in the company.
Subquery in FROM Clause
A subquery in the FROM clause acts like a temporary table.
The query returns all employee ids who have a salary greater than 80,000.
Practice: Write a query to get all the sales id who have a sale amount greater than 10,000.
Subquery in WHERE Clause
This is the most common use of subqueries: filtering results.
This query will get employees working in departments located in 'New York'.
Practice: Write a query that will get projects assigned to employee with employee_id of 2.
Single-row vs Multi-row Subqueries
- Single-row subqueries return only one value (e.g., =, <, >).
- Multi-row subqueries return multiple values (e.g., IN, ANY, ALL).
This is a single-row query used to find the max salary.
Practice: Write a query to get all the sales who are equal to the max salary of the employees.
This is a multi-row query which returns employees in departments with more than 5 employees. Here, there are no entries in the table which satisfy this query.
Practice: Write a query to get all the projects which have more than 2 persons assigned to it.
ANY, ALL, IN, EXISTS in Subquery
- IN: Matches values from a list.
- ANY: Condition is true if it matches at least one value.
- ALL: Condition must be true for all returned values.
- EXISTS: Checks if subquery returns any result.
Use of EXISTS to find employees who have assigned projects.
The query returns all department IDs located in New York, and the main query fetches employees from those departments
In addition to using operators like IN, EXISTS, ANY, and ALL, you can also apply their negations.
For example, NOT IN finds records that do not match a list of values, while NOT EXISTS checks for the absence of related rows in a subquery.
Similarly, NOT ANY and NOT ALL provide the opposite logic of their counterparts.
These negations are useful when you want to exclude results instead of including them, giving you more flexibility when filtering data with subqueries.
FAQs
1. What is the difference between a subquery and a JOIN in SQL?
- A subquery is a nested query used as input for another query, often for filtering or calculations.
- A JOIN merges rows from multiple tables into a single result set.
Use a subquery when you need derived values or filtering, and a JOIN when you need related data side by side.
2. Are subqueries slower than joins in SQL?
Yes, in many cases. Subqueries can be less efficient because the inner query is executed separately. JOINs are usually faster since databases optimize them better. However, modern SQL engines often optimize subqueries into joins automatically.
3. Can we use ORDER BY inside a subquery?
Yes, but only when combined with LIMIT or when the subquery is in a FROM clause. For example:
SELECT * FROM (SELECT * FROM salaries ORDER BY amount DESC LIMIT 5) AS top_salaries;
This subquery returns only the top 5 highest salaries.
4. What is the difference between correlated and non-correlated subqueries?
- Non-correlated subquery: Runs independently and returns a result used by the outer query.
- Correlated subquery: Depends on the outer query for its values and runs once for each row in the outer query.
5. When should I use EXISTS vs IN in SQL subqueries?
- Use IN when comparing a column to a list of values.
- Use EXISTS when checking if related rows exist.
EXISTS is usually faster on large datasets since it stops at the first match, while IN checks all values.