Selecting Specific Rows with WHERE

Let's take the next step in controlling the output of SELECT. In the previous section, we showed how to select only certain columns from the table. Now, we will show how to select only certain rows. This operation requires a WHERE clause. Without a WHERE clause, every row is returned.

The WHERE clause goes immediately after the FROM clause. In the WHERE clause, you specify the rows you want returned, as shown in Figure .  

        test=> SELECT * FROM friend WHERE age = 23;
            firstname    |       lastname       |      city       | state | age 
        -----------------+----------------------+-----------------+-------+-----
         Cindy           | Anderson             | Denver          | CO    |  23
        (1 row)  

The query returns the rows that have an age column equal to 23. Figure   

        test=> SELECT * FROM friend WHERE age <= 22;
            firstname    |       lastname       |      city       | state | age 
        -----------------+----------------------+-----------------+-------+-----
         Mike            | Nichols              | Tampa           | FL    |  19
         Sam             | Jackson              | Allentown       | PA    |  22
        (2 rows)  

shows a more complex example that returns two rows.

You can combine the column and row restrictions in a single query, allowing you to select any single cell, or a block of cells. See Figures   

        test=> SELECT lastname FROM friend WHERE age = 22;
               lastname       
        ----------------------
         Jackson             
        (1 row)  

and .  

        test=> SELECT city, state FROM friend WHERE age >= 21;
              city       | state 
        -----------------+-------
         Denver          | CO
         Allentown       | PA
        (2 rows)  

Up to this point, we have made comparisons only on the age column.  The age column is an INTEGER. The tricky part about the other columns is that they are  CHAR() columns, so you must put the comparison value in single quotes.  You also have to match the capitalization exactly. See Figure . If you had compared the firstname column to `SAM' or `sam', it would have returned no rows. Try a few more comparisons until you are comfortable with this operation.   

        test=> SELECT * FROM friend WHERE firstname = 'Sam';
            firstname    |       lastname       |      city       | state | age 
        -----------------+----------------------+-----------------+-------+-----
         Sam             | Jackson              | Allentown       | PA    |  22
        (1 row)