Chapter 3: How to retrieve data from a single table

In this chapter, you’ll learn how to code SELECT statements that retrieve data from a single table.

How to use the DISTINCT keyword to eliminate duplicate rows

By default, all of the rows in the base table that satisfy the search condition you specify in the WHERE clause are included in the result set. In some cases, though, that means that the result set will contain duplicate rows, or rows whose column values are identical. If that's not what you want, you can include the DISTINCT keyword in the SELECT clause to eliminate the duplicate rows.

Figure 3-8 illustrates how this works. Here, both SELECT statements retrieve the VendorCity and VendorState columns from the Vendors table. The first statement, however, doesn't include the DISTINCT keyword. Because of that, the same city and state can appear in the result set multiple times. In the results shown in this figure, for example, you can see that Anaheim CA occurs twice and Boston MA occurs three times. In contrast, the second statement includes the DISTINCT keyword, so each city/state combination is included only once.

A SELECT statement that returns all rows

SELECT VendorCity, VendorState
FROM Vendors
ORDER BY VendorCity

(122 rows)

A SELECT statement that eliminates duplicate rows

SELECT DISTINCT VendorCity, VendorState
FROM Vendors

(53 rows)

Description

• The DISTINCT keyword prevents duplicate (identical) rows from being included in the result set. It also causes the result set to be sorted by its first column.
• The ALL keyword causes all rows matching the search condition to be included in the result set, regardless of whether rows are duplicated. Since this is the default, it's a common practice to omit the ALL keyword.
• To use the DISTINCT or ALL keyword, code it immediately after the SELECT keyword as shown above.

---

Figure 3-8 How to use the DISTINCT keyword to eliminate duplicate rows