Home > Articles

  • Print
  • + Share This
This chapter is from the book

Placing Conditions on Queries

If you ever want to find a particular item or group of items in your database, you need one or more conditions. Conditions are contained in the WHERE clause. In the preceding example, the condition is

NAME = 'BROWN'

To find everyone in your organization who worked more than 100 hours last month, your condition would be

NUMBEROFHOURS > 100

Conditions enable you to make selective queries. In their most common form, conditions comprise a variable, a constant, and a comparison operator. In the first example, the variable is NAME, the constant is 'BROWN', and the comparison operator is =. In the second example, the variable is NUMBEROFHOURS, the constant is 100, and the comparison operator is >. You need to know about two more elements before you can write conditional queries: the WHERE clause and operators.

Using the WHERE Clause

The syntax of the WHERE clause is

WHERE <SEARCH CONDITION>

SELECT, FROM, and WHERE are the three most frequently used clauses in SQL. WHERE simply causes your queries to be more selective. Without the WHERE clause, the most useful thing you could do with a query is display all records in the selected table(s)—for example,

SQL> SELECT * FROM BIKES;

or

mysql> select * from bikes;

lists all rows of data in the table BIKES.

NAME
FRAMESIZE
COMPOSITION
MILESRIDDEN
TYPE
-----------
---------
------------
-----------
--------
TREK 2300
22.5
CARBON FIBER
3500
RACING
BURLEY
22
STEEL
2000
TANDEM
GIANT
19
STEEL
1500
COMMUTER
FUJI
20
STEEL
500
TOURING
SPECIALIZED
16
STEEL
100
MOUNTAIN
CANNONDALE
22.5
ALUMINUM
3000
RACING
6 rows selected.

If you wanted a particular bike, you could type

SQL> SELECT *
   FROM BIKES
   WHERE NAME = 'BURLEY';
mysql> select *
  -> from bikes
  -> where name = 'BURLEY';

which would yield only one record:

NAME      FRAMESIZE COMPOSITION  MILESRIDDEN TYPE
-------------  --------- -----------  ----------- ------
BURLEY         22 STEEL         2000 TANDEM
+--------+-----------+-------------+-------------+--------+
| name  | framesize | composition | milesridden | type  |
+--------+-----------+-------------+-------------+--------+
| BURLEY |   22.00 | STEEL    |    2000 | TANDEM |
+--------+-----------+-------------+-------------+--------+
1 row in set (0.27 sec)

These simple examples show how you can place a condition on the data that you want to retrieve.

  • + Share This
  • 🔖 Save To Your Account