- Retrieving Columns with SELECT and FROM
- Creating Column Aliases with AS
- Eliminating Duplicate Rows with DISTINCT
- Sorting Rows with ORDER BY
- Filtering Rows with WHERE
- Combining and Negating Conditions with AND, OR, and NOT
- Matching Patterns with LIKE
- Range Filtering with BETWEEN
- List Filtering with IN
- Testing for Nulls with IS NULL
Eliminating Duplicate Rows with DISTINCT
Columns often contain duplicate values, and it’s common to want a result that lists each duplicate only once. If I type Listing 4.6 to list the states where the authors live, the result, Figure 4.6, contains unneeded duplicates. The DISTINCT keyword eliminates duplicate rows from a result. Note that the columns of a DISTINCT result form a candidate key (unless they contain nulls).
Listing 4.6. List the states in which the authors live. See Figure 4.6 for the result.
SELECT state FROM authors;
Figure 4.6 Result of Listing 4.6. This result contains unneeded duplicates of CA and NY.
state ----- NY CO CA CA NY CA FL
To eliminate duplicate rows:
SELECT DISTINCT columns FROM table;
columns is one or more comma-separated column names, and table is the name of the table that contains columns (Listing 4.7 and Figure 4.7).
Listing 4.7. List the distinct states in which the authors live. The keyword DISTINCT eliminates duplicate rows in the result. See Figure 4.7 for the result.
SELECT DISTINCT state FROM authors;
Figure 4.7 Result of Listing 4.7. This result has no CA or NY duplicates.
state ----- NY CO CA FL