How to Practice SQL Subqueries

Have you ever wondered what separates beginners from advanced SQL users? It includes things like, for example, subqueries. In this article, I explain their importance and why you need to practice SQL subqueries to become an expert.

What Is an SQL Subquery?

SQL is an easy-to-learn language. However, there are certain complex features and techniques of the language that require practice. One of these features is the subquery, which adds great expressive power to the language and your projects.

In this article, we review examples for you to take your first step toward practicing subqueries. We also mention articles and courses where you find more material like lectures and exercises to continue improving in the use of subqueries.

If you do not know what I'm talking about, start with the SQL Subqueries course. It is a SQL practice course designed specifically to practice SQL subqueries. If you already know a little about the topic but do not know how to practice subqueries, read on.

In simple terms, a subquery is a query inside a query. SQL allows us to put subqueries in different parts of an SQL query, like the WHERE and FROM clauses, among others. Creating a subquery is very easy because its structure is the same as a regular query. We only need to know some rules about how to connect the query and the subquery.

Before starting with examples, let’s clarify some vocabulary: in the rest of this article, we call the external query in which a subquery resides an “outer query.”

Let’s introduce a simple database with two tables: employee and department.

Table employee

Table department

Suppose we want to obtain the name of the department with the largest budget. We may write the following query, which includes a subquery (in blue):

SELECT dept_name, budget
FROM department
WHERE budget = ( SELECT MAX(budget) FROM department )

We have a subquery (in blue) in the WHERE clause that returns the largest budget of the department table. Then, we use this value in the WHERE clause of the outer query to compare with the budget column. All records in the department table having a budget equal to the maximum value of the budget are returned.

Note that the subquery is enclosed in parentheses. In SQL, subqueries always need to be enclosed in parentheses.

With this example, we have seen how easy and simple it is to create a subquery. However, there are some rules about how to connect the subquery with the outer query. We also need to know where a subquery should be within the SELECT statement. There are different clauses (like WHERE, FROM, and HAVING) in the outer query where we may put a subquery. These are some of the reasons why practice with subqueries is so important.

Finally, for those looking for resources to practice their SQL skills, I suggest the course SQL Practice Set. You find more than 80 interactive exercises, ranging from simple tasks with SELECT FROM statements to more advanced problems involving multiple subqueries. Another interesting set of SQL exercises is in the Monthly SQL Practice Sets, where you see your SQL code in action and receive immediate feedback.

Another SQL Subquery Example

We have seen a subquery that returns a result set with a single record and a single column. This kind of subquery is quite common, and it is easy to integrate or connect the subquery with the outer query by using the equal (=) operator.

Now, let’s see a subquery that returns a single column but multiple records to contrast with a subquery that produces a single record.

Suppose we need to obtain the employee IDs and the names of the employees who work in the CentralPark building. We may write an SQL query like the following. Again, the subquery is in blue.

SELECT employee_id, last_name, first_name
FROM employee
WHERE department_id IN ( SELECT department_id
                         FROM   department
                         WHERE  building = ‘CentralPark’
                       )

The first thing to note is the number of records returned by the subquery. It returns two records because there are two departments in the CentralPark building: Sales and Accounting. So, the subquery generates a result set with two department IDs, 100 and 101.

The second thing to note is the IN operator used in the WHERE clause of the outer query. When a subquery returns multiple rows, we cannot use the equal operator (=) as we have in the first example. Instead, we use the IN operator, which works like the “belongs to” operator. In other words, the condition:

department_id IN ( SELECT department_id
                         FROM   department
                         WHERE  building = ‘CentralPark’
                       )

returns TRUE when the value of department_id exists in the subquery result set and FALSE when the value of department_id is not included in the subquery result set.

Always try to identify in advance how many records your subquery returns. Is it a multi-record subquery or a single-record subquery? Based on that, figure out how to connect it with the outer query. To learn more details about multi-record and single-record subqueries, I strongly suggest the articles “Beginner’s Guide to the SQL Subquery” and “What Are the Different Types of SQL Subqueries?”

For those who want to go deeper into subqueries, another interesting article is “Subquery vs. JOIN.” You learn different ways to create equivalent SQL queries using joins and subqueries.

Using SQL Subqueries in Other Parts of the Query

We have shown subquery examples in the WHERE clause of the SELECT statement. In this section, we show subqueries in other clauses.

The FROM clause is where we usually specify the table to read in the query. However, we may put a subquery in the FROM clause to create a sort of intermediate result set. After that, this result set may be used like a virtual table by the outer query.

Let’s see an example. Suppose we have departments where the total salary of all employees exceeds the department budget. To detect these cases, we want to obtain the budget and the total amount of salaries paid by each department. We also add to the report a math expression to obtain the unused portion of the budget. Let’s see the query:

SELECT 
  d1.dept_name,
  d1.budget,
  total.total_salaries,
  d1.budget - total.total_salaries AS budget_after_salaries
  FROM ( SELECT department_id, SUM(salary) AS total_salaries
         FROM employee
	 GROUP BY department_id
	) AS total
  INNER JOIN department d1 
  ON d1.department_id = total.department_id

The subquery in blue in the FROM clause creates an intermediate table named total with columns department_id and total_salaries. After that, we do a JOIN between the intermediate table total and the regular table department. In the list of columns in SELECT, we calculate the difference between the department budget and the total salary amount. We name this value budget_after_salaries. Let’s see the result of the query:

We may also put subqueries in other clauses of the SELECT statement, like the HAVING clause used to filter groups of records. Suppose we want to show the departments whose total salary amount is greater than the average of the department totals. We obtain the average of the department totals with a subquery. Let's see the query:

SELECT 	
  d1.dept_name,
  SUM(salary)
FROM employee e
INNER JOIN department d1 
ON d1.department_id = e.department_id
GROUP BY d1.dept_name
HAVING SUM(salary) > (
  SELECT SUM(salary)/COUNT(distinct department_id)
  FROM employee)

We obtain the total salary amount in each department with the expression SUM(salary). Then, in the HAVING clause, we compare this amount with the average department total obtained in the subquery. The departments whose SUM(salary) is greater than the result of the subquery are shown in the result of the query, as we see below:

For those who want to learn where the subquery is supported in SQL, the next two articles explain other places subqueries may be placed. The article “Subquery vs. CTE: A SQL Primer” explains CTEs, and in the article “How to Use Subqueries in INSERT, UPDATE, and DELETE Statements,” as the title says, you learn SQL subqueries in other statements like INSERT, UPDATE, and DELETE.

Practice SQL Subqueries to Improve Your Skills

We have seen several SQL subqueries in different clauses. SQL subqueries are usually used in a SELECT statement; however, it is possible to use them in other statements. Also, there are other kinds of SQL subquery like correlated subqueries and CTEs that add even more expressivity to the SQL language.

It is easy and intuitive to learn SQL, especially in the first few steps. But like any language, you need to practice so that you reach the level where you can manage and create all kinds of queries and reports. The more time you spend solving problems in the SQL language, the greater the SQL skills you acquire.

I would like to close with a great resource for learning SQL subqueries: our SQL Subqueries course. It contains over 80 hands-on exercises designed specifically to practice SQL subqueries.

Enhance your skills and invest in yourself!