Limiting the Number of Results with TOP

If you execute this query, you’ll get the results we saw above, listed in reverse order. You could also order the results by multiple columns—simply add a comma after the field name and enter a second field name, as follows:

SELECT EmployeeID, Name, City

FROM Employees

ORDER BY City, Name

In this case, the results are returned in alphabetical order by city, and any tying records (i.e. with the same city) will appear sorted by name.

Limiting the Number of Results with TOP

Another using SQL keyword is TOP, which can be used together with SELECT to limit the number of returned rows. For example, if we want to retrieve the first five departments, and have the list ordered alphabetically, we’d use this command:

SELECT TOP 5 Department

FROM Departments

ORDER BY Department

Here are the results:

Department

--------------------------------------------------

Accounting Administration Business Development Customer Support Engineering

(5 row(s) affected)

Reading Data from Multiple Tables

Until now, we’ve primarily focused on extracting data from a single table. Yet in many real-world applications, you’ll need to extract data from multiple tables simultaneously. To do so, you’ll need to use subqueries or joins.

Let’s learn about joins and subqueries by looking closely at a typical example. Say you’re asked to build a report that displays all the employees in the Engineering department. To find employee data, you’d normally query the Employees table, and apply a WHERE filter on the ID of the department. That approach would

307

Chapter 8: Speaking SQL

work fine in this case, except for one thing: you don’t know the ID of the Engineering department!

The solution? First, execute this query to find the ID of the Engineering department:

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

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 department. An embedded SELECT statement is used when you want to perform a second query within the WHERE clause of a primary query.

308

Table Joins

Note that we’re using the IN operator instead of the equality operator (=). We do so because our subquery could return a list of values. For example, if we added another department with the name “Product Engineering,” or accidentally added another Engineering record to the Departments table, our subquery would return two IDs. So, whenever we’re dealing with subqueries like this, we should use the IN operator unless we’re absolutely certain that the subquery will return only one record.

Querying Multiple Tables

When using queries that involve multiple tables, it’s useful to take a look at the database diagram you created in Chapter 7 to see what columns exist in each table, and to get an idea of the relationships between the tables.

Table Joins

An inner join allows you to read and combine data from two tables between which a relationship is established. In Chapter 7, we created such a relationship between the Employees table and the Departments table using a foreign key.

Let’s make use of this relationship now, to obtain a list of all employees in the engineering department:

SELECT Employees.Name

FROM Departments

INNER JOIN Employees ON Departments.DepartmentID =

Employees.DepartmentID

WHERE Departments.Department LIKE '%Engineering'

The first thing to notice here is that we qualify our column names by preceding them with the name of the table to which they belong, and a period character (.). We use Employees.Name rather than Name, and Departments.DepartmentID instead of DepartmentID. We need to specify the name of the table whenever the column name exists in more than one table (as is the case with DepartmentID); in other cases (such as with Employees.Name), adding the name of the table is optional.

As an analogy, imagine that you have two colleagues at work named John. John Smith works in the same department as you, and his desk is just across the aisle. John Thomas, on the other hand, works in a different department on a different floor. When addressing a large group of colleagues, you would use John Smith’s full name, otherwise people could become confused. However, it would quickly become tiresome if you always used John Smith’s full name when dealing with

309