Subqueries

Sophia

Sophia

PDF Version

WHAT'S COVERED

This tutorial explores subqueries and using them in SELECT statements in three parts:

Subquery Basics

Considering Subquery Results

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\mathrm{.}71.$

SELECT invoice$\_$id$,$ invoice$\_$date,customer$\_$id$,$ total

FROM invoice

WHERE total $>5\mathrm{.}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\mathrm{.}71$;

We would remove the $5\mathrm{.}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

Play Video

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 wan