Subqueries of SQL, Unite!
SQL Quickies #2
One of SQL’s great features is the use of subqueries, ie nesting one query inside another. Subqueries allows us to make significantly dynamic and complex queries. In this blog post, we’ll explore how we can write them, how they work, and some common use cases.
What is a Subquery?
A subquery, also known as an inner query or nested query, is a query embedded within another SQL query. The result of the subquery (or the inner query) is then utilised by the outer query to perform additional operations.
Subqueries can be found in various SQL clauses, such as SELECT, FROM, and WHERE. However, we would mainly be using them in WHERE since we would be typically interested in filtering data through such subqueries.
Get Prathik C’s stories in your inbox
Join Medium for free to get updates from this writer.
A simple syntax of a subquery looks like:
SELECT column_name1,column_name2,...
FROM table1
WHERE column_name OPERATOR (
SELECT column_name
FROM table2
WHERE condition
);- Subqueries are enclosed within parenthesis
(). - The data returned by the subquery must be compatible with the outer query, lest we run into errors.
- One should note that the performance and efficiency could be affected negatively if the complexity of the subquery rises.
Examples of Various Subqueries
Subqueries can be classified into different types based on their use case and how they interact with the outer query. We will now understand these using an example database which have the following tables: employees, departments and locations. The columns and their data types of each table is given below:
1. Single-Row Subqueries
These return a single value and are often used with comparison operators like =, <, or >.
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary) FROM employees
)
ORDER BY employee_id DESC;This query retrieves all employees earning more than the average salary and orders them in the descending order according to employee_id.
2. Multi-Row Subqueries
These return multiple rows and are used with operators like IN, ANY, or ALL.
SELECT name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE department = 'Data Science'
);Here, we fetch the names of employees working in the Data Sciencedepartment.
3. Correlated Subqueries
A correlated subquery is one that references columns from the outer query, meaning it is executed once for each row processed by the outer query. If that seems like it could potentially become cumbersome, you are right!
SELECT name, salary
FROM employees AS e1
WHERE salary > (
SELECT AVG(salary)
FROM employees AS e2
WHERE e1.department_id = e2.department_id
);This query finds employees earning more than the average salary within their respective departments.
4. Nested Subqueries
These involve multiple levels of subqueries within a single SQL statement.
SELECT name
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE location_id = (
SELECT location_id
FROM locations
WHERE city = 'Delhi'
)
);This retrieves employees working in a department located in Delhi.
Advantages and Limitations of Subqueries
Advantages:
- Improves query readability by breaking complex logic into smaller parts.
- Allows for dynamic filtering based on subquery results.
- Helps in writing queries without requiring temporary tables.
Limitations:
- May have performance issues if not optimized well.
- Correlated subqueries can be slow due to repeated execution.
- Some database systems have restrictions on subquery usage in certain clauses.
Whether you are filtering data, computing aggregate values, or nesting queries for complex operations, subqueries are the way to go. Make sure that you understand the order of execution, especially in the case of correlated subqueries. I hope this helped you see how subqueries are a valuable feature of SQL. Until next time! :)
