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)