31 Oct Sql server tutorial 12 Using Subqueries to Access and Modify Data
Key Skills & Concepts
- Create Subqueries that Return Multiple Rows
- Create Subqueries that Return One Value
- Work with Correlated Subqueries
- Use Nested Subqueries
Use Subqueries to Modify Data
ubqueries, like joins, provide a way to access data in multiple tables with a single
query. A subquery can be added to a SELECT, INSERT, UPDATE, or DELETE
statement in order to allow that statement to use the query results returned by the
subquery. The subquery is essentially an embedded SELECT statement that acts as a
gateway to data in a second table. The data returned by the subquery is used by the
primary statement to meet whatever conditions have been defined in that statement. In this
chapter, I discuss how subqueries are used in various statements, particularly SELECT
statements, and provide examples that demonstrate how to create subqueries and what
type of query results to expect.
Create Subqueries that Return Multiple Rows
In Chapter 9, I include several examples of subqueries that are used to demonstrate certain
types of predicates, such as IN and EXISTS. This chapter, in many ways, is an extension
of that discussion because of the way in which subqueries are most commonly
implemented—in the WHERE clause of a SELECT statement. An understanding of these
types of subqueries goes hand in hand with an understanding of how certain predicates are
formulated to create specific search conditions, search conditions that rely on those
subqueries to return data from a referenced table.
You can divide subqueries in a WHERE clause into two general categories: those that
can return multiple rows and those that can return only one value. In this section, I discuss
the first of these categories. In the next section, “Create Subqueries that Return One
Value,” I discuss the second category. As I expand on each subject, you’ll no doubt
recognize the statement formats from my discussion of predicates. Although this
information might seem a bit repetitive (which is why I keep it brief), it is presented here
not only to provide a cohesive overview of subqueries, but also to provide a different
perspective. In other words, rather than looking at subqueries through the perspective of
the predicate, we’ll look directly at the subquery itself.
Despite the fact that my discussion focuses on subqueries that are implemented through
the WHERE clause, the use of subqueries is not limited to that clause. Indeed, you can
include subqueries in a SELECT clause or HAVING clause. However, using subqueries in
a SELECT clause is not very common. In addition, you would use subqueries in a
HAVING clause only when defining search conditions on grouped data. Even so, the
principles for using subqueries in a HAVING clause are similar to using them in a
WHERE clause. For these reasons, my discussion here focuses on using subqueries in the
WHERE clause. As you become a more advanced SQL programmer, you will likely want
to try using subqueries in other places within a SELECT statement.
Using the IN Predicate
The first type of subquery that we’ll look at is the type used within the IN predicate. As
you might recall from Chapter 9, the IN predicate compares values from a column in the
primary table to values returned by the subquery. If the column value is in the subquery
results, that row (from the primary table) is returned in the query results of the SELECT
Using the EXISTS Predicate
In some circumstances, you might want your subquery to return only a value of true or
false. The content of the data itself is unimportant in terms of meeting a predicate
condition. In this case, you can use an EXISTS predicate to define your subquery. The
EXISTS predicate evaluates to true if one or more rows are returned by the subquery;
otherwise, it evaluates to false.
For an EXISTS predicate to be useful, the associated subquery should include a search
condition that matches values in the two tables that are being linked through the subquery.
(I explain this type of subquery in more detail in the “Work with Correlated Subqueries”
section later in this chapter.) This search condition is similar to the equi-join condition
used in certain join operations. (See Chapter 11 for information about joins and equi-join
conditions.) For example, returning to the CD_STOCK table and the CD_ARTISTS tables
(shown in Figure 12-1), we can create a SELECT statement that uses an EXISTS predicate
to query the CD_ARTISTS table:
Using Quantified Comparison Predicates
The IN and EXISTS predicates are not the only predicates that rely on the type of
subqueries that can return one or more rows for the search condition to evaluate to true.
Quantified comparison predicates—SOME, ANY, and ALL—also use subqueries that can
return multiple rows. These predicates are used in conjunction with comparison operators
to determine whether any or all returned values (from the subquery) meet the search
condition set by the predicate. The SOME and ANY predicates, which perform the same
function, check to see whether any returned values meet the search requirement. The ALL
predicate checks to see whether all returned values meet the search requirement.
When a quantified comparison predicate is used, the values in a column from the
primary table are compared to the values returned by the subquery. Let’s take a look at an
example to clarify how this works.