Online College Courses for Credit

+
Subqueries

Subqueries

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose a query that utilizes a subquery to find all values greater than the average in a numeric column.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using subqueries and being able to use 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 where 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 done so far. We would first calculate the average total:

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

File:11431-2300-1.png

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;

File:11432-2300-2.png

Although this approach works, it is not a very intuitive approach. We want to use a way that can pass the result from the first query that calculated the average and pass it into the second query. This type of solution can be handled using a subquery. The subquery can be nested in a SELECT, INSERT, DELETE, or an UPDATE statement but we will mostly see it in a SELECT statement.

To construct the subquery, we would place the second query in round brackets and place it in the WHERE clause as an expression. So, in taking 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 and the first statement to have:

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

Notice that we don’t have a semi-colon at the end of the inner statement as it is set up as a subquery. The query that we have inside of the round brackets is called a subquery or an inner query. The query that has the subquery is also called an outer query.

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


2. Considering Subquery Results

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

Let us look at an obvious result from the subquery where it’s 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.

File:11433-2300-3.png

If we tried to select using 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:

File:11434-2300-4.png

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:

File:11435-2300-5.png

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 correctly:

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

File:11436-2300-6.png


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');

File:11437-2300-7.png

If we did not include the round brackets around the customers to be compared to, we will 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');

File:11438-2300-8.png


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.