Much like working with a group, sometimes there are multiple questions we want to ask of a group or a subset of data. To do this, we can create a query and then literally wrap another query around it to get additional details about this information. To help us understand how we might use a subquery, let’s take a look at our Data Model and then ask a few questions. For space reasons, the tables in our diagram include only the table name and the primary key.

SubquieryIf our database looked like this, what type of questions might we be able to answer?

We see that an owner can have a pet—in this case, we are only showing dogs. We can also see a pet can have a veterinarian visit. What if we wanted to see the owners who have dogs whose rabies shots are not current? Pet Owner doesn’t link directly to the VeterinarianVisitHistory table. To do this query, we would first have find the dogs whose shots were not current. I am using a bit column to do this, which we review in *bit column link*. In this example a setting of zero means no and a 1 means yes.

SELECT DogIdentifier

FROM VeterinarianVisitHistory

WHERE RabiesShotCurrent = 0;
OUTPUT

DogIdentifier
————-

551

541

551

(3 row(s) affected)

Now that we have the DogIdentifier for the owner, we can go to the Petowner table and query for the names of the pet owners.

SELECT OwnerName

FROM PetOwner

INNER JOIN Dog ON Dog.OwnerIdentifier = PetOwner.OwnerIdentifier

WHERE Dog.DogIdentifier IN (541,551) ;
OUTPUT

OwnerName
————————–
Pipper Longstocking
Mike Smith

(2 row(s) affected)

 

Using a subquery will allow us to get this information in a single statement. The query format is not completely new and it does use several of the aspects we have discussed. A little formatting will help us to understand more clearly. We must alias the join query so we can complete our join criteria.

SELECT [ColumnName] FROM [TableName] JOIN (Select [ColumnName] FROM [TableName]) [QueryAlias] ON [Join Criteria]

Our example would look like this. We use the alias of ‘shot’ to describe the query about which dogs do not have their shots current. We can use the dog identifiers to join to the dog table.

SELECT PetOwner.OwnerName
FROM   PetOwner
INNER JOIN Dog ON Dog.OwnerIdentifier = PetOwner.OwnerIdentifier
INNER JOIN ( SELECT VeterinarianVisitHistory.DogIdentifier
FROM   VeterinarianVisitHistory
WHERE RabiesShotCurrent = 0

) shot ON shot.DogIdentifier = Dog.DogIdentifier;

 

OUTPUT

OwnerName
————————–
Pipper Longstocking
Mike Smith

(2 row(s) affected)

This outline is only to help us understand the idea of a subquery. Now let’s suppose we need to get a count of the owners and the number of times they have visited the veterinarian. Our query will have two columns in the results. You will notice we do a group by in our inner query and get a count (VetVisits). We can then use that count in the outer query and provide the VetVisit number to the final select.

SELECT OwnerName,
visits.VetVisits AS 'Number of Vet Visits'
FROM PetOwner
INNER JOIN Dog ON Dog.OwnerIdentifier = PetOwner.OwnerIdentifier
INNER JOIN ( SELECT DogIdentifier ,
COUNT(*) AS VetVisits
FROM   VeterinarianVisitHistory
GROUP BY VeterinarianVisitHistory.DogIdentifier
HAVING COUNT(*) > 1
) visits ON visits.DogIdentifier = Dog.DogIdentifier;

The output looks like this .

OwnerName                 Number of Vet Visits
————————–      ——————–
Sean Conary                       2
Scott Tissue                         2

(2 row(s) affected)

 

The exciting part of subqueries comes when we start using our aggregate functions and can combine them with other data to show the users the end result.

Summary

A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. This gives us a logical format to break a query into pieces. The subquery will return a result that the remaining query can then process.