Build Your Own ASP.NET 2.0 Web Site Using C# & VB, Second Edition

work fine in this case, except for one thing: you don’t know the ID of the Engin-

eering department!

The solution? First, execute this query to find the ID of the Engineering depart-

ment:

SELECT DepartmentID

FROM Departments

WHERE Department = 'Engineering'

The result of this query will show that the ID of the Engineering department is

6. Using this data, you can make a new query to find the employees in that de-

partment:

SELECT Name

FROM Employees

WHERE DepartmentID = 6

This query retrieves the same list of employees we saw earlier in this chapter.

So everything’s great … except that you had to execute two queries in order to

do the job! There is a better way: SQL is very flexible and allows you to retrieve

the intended results using a single command. You could use either subqueries or

joins to do the job, so let’s take a look at them in turn.

Subqueries

A subquery is a query that’s nested inside another query, and can return data

that’s used by the main query. For example, you could retrieve all the employees

who work in the Engineering department like this:

SELECT Name

FROM Employees

WHERE DepartmentID IN

(SELECT DepartmentID

FROM Departments

WHERE Department LIKE '%Engineering')

In this case, the subquery (highlighted in bold) returns the ID of the Engineering

department, which is then used to identify the employees who work in that de-

partment. An embedded SELECT statement is used when you want to perform a

second query within the WHERE clause of a primary query.

308

Chapter 8: Speaking SQL