Get Arhaan Behlim’s stories in your inbox
Join Medium for free to get updates from this writer.
SQL SUBQUERY
What is an SQL Subquery ?
It is also known as a nested query. It is a query within another SQL statement. “It allows complex filtering, aggregation, and data manipulation by using the result of one query inside another.” The inner query is executed to produce a value and the outer query uses that result to get the required data
EXAMPLE:
SELECT name, salary
FROM employees
WHERE salary >(
SELECT AVG(salary)
FROM employees);
In this example, the inner query calculates the average salary of all employees. The outer query then finds and displays only those employees whose salary is higher than the average salary
“When a condition depends on the result of another query, a subquery can be used to break the problem into smaller steps”
TYPES:
Single Row Subquery: It is a subquery that returns only one value commonly uses operators such as =,<,and >
SELECT * FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
The inner query finds the maximum salary from the employees table. The outer query then compares each employee’s salary with this value and displays the employee whose salary matches the maximum salary.
MULTI-ROW SUBQUERY: It is a subquery that returns more than one value and requires operators such as IN,ANY, and ALL
SELECT * FROM employees
WHERE manager_id IN( SELECT id FROM employees
where salary >100000);
The inner query finds the IDs of employees whose salary is greater than 100,000. The outer query then checks which employees have a manager whose ID matches any of these IDs
CORRELATED SUBQUERY: It is a subquery that depends on the outer query for its values, which can make it slower
SELECT * FROM employees e1
WHERE salary > ( SELECT salary FROM employees e2
WHERE e1.manager_id = e2.id);
The inner query finds the salary of the manager for each employee using manager_id. The outer query then compares the employee’s salary with the manager’s salary and returns employees who earn more than their managers
APPLICATIONS OF SQL SUBQUERIES:
Employee Salary Analysis:
SELECT name, salary
FROM employees
WHERE salary >(
SELECT AVG(salary)
FROM employees);
The inner query calculates the average salary. The outer query shows employees whose salary is higher than the average
CUSTOMER ORDER ANALYSIS:
SELECT customer_id, order_amount
FROM orders
WHERE order_amount >(
SELECT AVG(order_amount)
FROM orders);
The inner query calculates the average order amount. The outer query shows customers whose orders have an amount higher than the average order amount
SUBQUERIES WITH DIFFERENT SQL CLAUSES:
WHERE: SELECT name, salary
FROM employees
WHERE salary >(
SELECT AVG(salary)
FROM employees);
The subquery calculates the average salary, and the WHERE clause filters employees whose salary is higher than that average
FROM:
SELECT * FROM (SELECT name, salary
FROM employees
WHERE salary > 500000) AS high_salary_employees;
The subquery creates a temporary result set containing employees with salaries above 500,000. The outer query then retrieves the data from this result
SELECT:
SELECT name, ( SELECT MAX (salary) FROM employees) AS highest_salary FROM employees;
The subquery finds the highest salary from the employees table. The SELECT clause displays this value along with each employee’s name
IN: SELECT name, department_id
FROM employees
WHERE department_id IN (
SELECT id
FROM departments
WHERE location = ‘Delhi’);
The subquery finds the IDs of departments located in Delhi. The outer query then displays employees who belong to any of those departments
COMMON MISTAKES WHILE USING SUBQUERIES:
Returning multiple rows: when a subquery returns multiple values, it can cause an error
Ignoring Null values: null values can produce unexpected results null values may affect the result
Missing Aliases: when the same table is used in both the outer and inner queries, aliases help identify columns clearly
Overusing nested queries: too many levels of subqueries can make SQL difficult to read, understand and maintain
Using correlated subqueries unnecessarily: correlated subqueries may run repeatedly for multiple rows and can affect performance on large datasets
Not testing the inner query: First checking what the subquery returns can help identify errors before combining it with the outer query
ADVANTAGES:
Easy to understand
Useful for comparisons
Helps solve complex problems
Useful for data analysis and reporting
LIMITATIONS:
Increased resource usage
Time-consuming
Future modifications can be harder
CONCLUSION:
In this blog we explored what SQL subqueries are and how they work we briefly discussed different types of SQL subqueries with practical examples . We also covered their advantages and limitations . Understanding these concepts can help us write better SQL queries and solve complex data-related problems more effectively. We discussed three main types of SQL subqueries: single row, multi row and correlated subqueries each type depends on the number of values returned and its relationship with the outer query. They can be used in practical situations such employee salary analysis and customer orders analysis. They help us filter and compare data based on results obtained from another query. However, subqueries can sometimes make SQL queries more complex and difficult to maintain. They can also affect performance when working with large datasets
