Subqueries, sometimes referred to as nested queries or inner queries, are a useful feature in SQL that allows you to deal with more complex data retrieval and manipulation tasks. They are a useful tool for any SQL developer or database administrator, enabling you to write more sophisticated queries. This article will provide an in-depth understanding of subqueries in SQL, covering their theoretical aspects and practical usage. We’ll explore subqueries in the SELECT statement, the FROM clause, and the WHERE clause, providing examples for each to illustrate their application.
Keywords:
sql query subquery, sql query with subquery, subquery in subquery sql, subqueries, subquery in sql select, sql server subquery
SQL Subqueries table of contents
Learn SQL online
Subquery syntax
A SQL query subquery is an SQL query inside another SQL query. It can be used to retrieve data from more than one table and to enable more complex results to be returned. It can however also add inefficiencies to your SQL query and make it harder for the SQL engine to optimise the SQL statements execution so should be treated with caution on larger datasets. Before diving into practical examples, it’s essential to understand the three common ways SQL subqueries are used:
Subquery in the SELECT Statement
A subquery in the SELECT statement is used to retrieve a single value that will be displayed for each row returned by the outer query. This is particularly useful when you want to include aggregated or derived values in the result set without having to group the main data set. Here’s an example:
SELECT employee_name, (SELECT MAX(salary) FROM employee) AS max_salary
FROM employee;
In this query, a subquery within the SELECT clause retrieves the maximum salary from the employee table for each employee in the result set. The subquery is contained within an open and closed bracket, is an SQL SELECT query in its own write and must contain a single value. In this example we have given the result of the subquery an aliased name max_salary. The same result is returned for each row. You could further use the returned result in the subquery to for example work out how each employees salary compares to the maximum salary.
Subquery in the FROM Clause
A subquery in the FROM clause is used to create a derived table that can be joined with other tables or further manipulated. This can be helpful when you need to combine data from multiple sources or filter data before working with it. There are better ways using table joins to join two tables together that will execute faster but this is useful if you need to perform complex calculations or aggregations before you join two sets of data together. An example:
SELECT emp_name, dep_name
FROM (SELECT employee_name AS emp_name, department_name AS dep_name
FROM employee
WHERE department_name = 'IT') AS it_employees;
In this query, a subquery in the FROM clause creates a derived table containing employees from the ‘IT’ department. The subquery is contained within the open and closed brackets and acts as a table from which the outer query can select data. The subquery has aliased the columns as emp_name and dep_name. These fields are then accessible from the main query. Although this is a simple example and not necessarily needed it is a useful approach for more complex needs. It should be noted that using a subquery in this way limits the of the SQL engine to optimise the query.
Subquery in the WHERE Clause
A subquery in the WHERE clause is used to filter the rows returned by the outer query based on the results of the subquery. This is commonly employed for scenarios where you want to filter data based on specific conditions that need to be pre calculated. Here’s an example:
SELECT product_name, unit_price
FROM products
WHERE unit_price > (SELECT AVG(unit_price) FROM products);
In this query, the subquery in the WHERE clause filters products with a unit price higher than the average unit price of all products.
Subquery example
Let’s put this theory into practice with a real-world example. Consider a simplified database with two tables: employees and departments. We want to find employees who earn more than the average salary within their respective departments. We can achieve this using subqueries in the WHERE clause:
SELECT employee_name, salary, department_name
FROM employees e
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id);
In this query the main query retrieves employee names, salaries, and department names from the employees table.
The subquery calculates the average salary for each department by filtering the employees table using the department_id. The main query has an aliased table with the name e. this is then referenced from the inner que
The main query’s WHERE clause uses the subquery’s result to filter and select employees whose salaries are greater than the average salary within their respective departments.
Conclusion
Subqueries in SQL are a versatile and powerful feature that enable you to perform complex data retrieval and manipulation tasks. Understanding the three common usages of subqueries in the SELECT statement, the FROM clause, and the WHERE clause is essential for mastering SQL. By integrating subqueries into your queries, you can retrieve and manipulate data in ways that might be challenging or inefficient using standard SQL statements. With practice and creativity, subqueries can become a valuable tool in your SQL toolkit, helping you achieve more advanced data analysis and reporting tasks.
Please see the other SQL learning articles below as part of this learning SQL online course.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
