Use Sophia to knock out your gen-ed requirements quickly and affordably. Learn more
×

Subqueries

Author: Sophia

what's covered
This tutorial explores subqueries and using them in SELECT statements in three parts:
  1. Subquery Basics
  2. Considering Subquery Results
  3. Adding Multiple Columns

1. Subquery Basics

There are many instances when we may want to use subqueries to create more complex types of queries. Very frequently, we will see this with aggregate calculations. For example, we may want to find all of the invoices that have the total amount larger than the average total amount.

We could do this in two separate steps, based on what we’ve learned so far. We could first calculate the average total:


SELECT ROUND(AVG(total),2)
FROM invoice;

query results

Next, we can take that value and query the invoice table to find those that are larger than 5.71.


SELECT invoice_id, invoice_date,customer_id, total 
FROM invoice
WHERE total > 5.71;

table

Although this approach works, we can also do it in a way that can pass the result from the first query (that calculated the average) into the second query by using a subquery. The subquery can be nested in a SELECT, INSERT, DELETE, or UPDATE statement, but we mostly see it in a SELECT statement.

To construct the subquery, we would enclose the second query in round brackets and place it in the WHERE clause as an expression. So, in our original second statement:


SELECT invoice_id, invoice_date,customer_id, total 
FROM invoice
WHERE total > 5.71;

We would remove the 5.71 and replace it with round brackets enclosing the first statement, like this:


SELECT invoice_id, invoice_date,customer_id, total 
FROM invoice
WHERE total > (SELECT ROUND(AVG(total),2) FROM invoice);

The query inside the round brackets is called the subquery, or inner query. The query that contains the subquery is called the outer query. Notice that we don’t include the semi-colon at the end of the inner statement in the subquery.

The database would execute the subquery first, then take the result from that subquery and pass it to the outer query. Then, the database would execute the outer query.


2. Considering Subquery Results

A subquery could potentially return 0 or more results, so it is important to think about the operator that is used to compare with the results. For example, if we use a = operator, we would expect that the subquery would return 0 or 1 row as a result.

Let's look at an obvious subquery that is querying on the customer_id to return the customer_id:


SELECT invoice_id, invoice_date,customer_id, total 
FROM invoice
WHERE  customer_id = 
    (SELECT customer_id 
     FROM customer
     WHERE customer_id = 1);

Since the primary key of the table is the customer_id, querying on the customer_id would only return one value.

table

If we tried to select a value that doesn’t exist:


SELECT invoice_id, invoice_date,customer_id, total 
FROM invoice
WHERE  customer_id = 
    (SELECT customer_id 
     FROM customer
     WHERE customer_id = 0);

The results would still run, but show that 0 rows were displayed:

query results

However, in the case that we have multiple rows being returned:


SELECT invoice_id, invoice_date,customer_id, total 
FROM invoice
WHERE  customer_id = 
    (SELECT customer_id 
     FROM customer
     WHERE country = 'USA');

Since there are 13 customers that live in the country USA, we will end up getting this error:

query results

In order to avoid this error, we have to use the IN operator instead of the equal sign. This will allow 0, 1, or many results to be returned:


SELECT invoice_id, invoice_date, customer_id, total 
FROM invoice
WHERE customer_id IN 
     (SELECT customer_id 
      FROM customer 
      WHERE country = 'USA');

table


3. Adding Multiple Columns

We can also add in multiple columns as criteria to compare with the subquery. In order to do so, we must use the round brackets around the columns within the WHERE clause and have them match up with the columns that we want to compare within the subquery. For example, if we wanted to compare the customer_id and the billing_country in the invoice table with the customer_id and country in the customer table, we could do the following:


SELECT invoice_id, invoice_date,customer_id, total 
FROM invoice
WHERE  (customer_id,billing_country) IN 
    (SELECT customer_id, country 
     FROM customer
     WHERE country = 'USA');

table

If we did not include the round brackets around the customers to be compared to, we would get an error:


SELECT invoice_id, invoice_date,customer_id, total 
FROM invoice
WHERE  customer_id, billing_country IN 
    (SELECT customer_id, country 
     FROM customer
     WHERE country = 'USA');

query results


Video Transcript

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
Subqueries can be used to create more complex queries that are combined together.

Source: Authored by Vincent Tran