Consider this grape-growing region table containing the details of nine regions:
SELECT * from region; +-----------+---------------------+-------------+------+ | region_id | region_name | description | map | +-----------+---------------------+-------------+------+ | 1 | Goulburn Valley | NULL | NULL | | 2 | Rutherglen | NULL | NULL | | 3 | Coonawarra | NULL | NULL | | 4 | Upper Hunter Valley | NULL | NULL | | 5 | Lower Hunter Valley | NULL | NULL | | 6 | Barossa Valley | NULL | NULL | | 7 | Riverland | NULL | NULL | | 8 | Margaret River | NULL | NULL | | 9 | Swan Valley | NULL | NULL | +-----------+---------------------+-------------+------+ 9 rows in set (0.00 sec)
It is possible to select only a few rows with a SELECT statement by adding a WHERE clause. For example, to show only the first three regions, you can issue the following statement:
SELECT * FROM region WHERE region_id<=3;
This outputs all attributes for only the first three region rows:
+-----------+------------------+-------------+------+ | region_id | region_name | description | map | +-----------+------------------+-------------+------+ | 1 | Goulburn Valley | NULL | NULL | | 2 | Rutherglen | NULL | NULL | | 3 | Coonawarra | NULL | NULL | +-----------+------------------+-------------+------+ 3 rows in set (0.00 sec)
You can combine the attribute and row restrictions and select only the region_name and region_id attributes for the first three regions:
SELECT region_id, region_name FROM region WHERE region_id <= 3; +-----------+------------------+ | region_id | region_name | +-----------+------------------+ | 1 | Goulburn Valley | | 2 | Rutherglen | | 3 | Coonawarra | +-----------+------------------+ 3 rows in set (0.00 sec)
More complex WHERE clauses use the Boolean operators AND and OR, as well as the functions described later in Section 3.9. The Boolean operators AND and OR have the same function as the PHP && and || operators introduced in Chapter 2.
Consider an example query that uses the Boolean operators:
SELECT * FROM customer
WHERE surname='Marzalla' AND
firstname='Dimitria';
This retrieves rows that match both criteria, that is, those customers with a surname Marzalla and a firstname Dimitria.
Consider a more complex example:
SELECT cust_id FROM customer
WHERE (surname='Marzalla' AND firstname LIKE 'M%')
OR email='john@lucaston.com';
This finds rows with either the surname Marzalla and a firstname beginning with M, or customers with the email address john@lucaston.com. The OR operator isn't exclusive, so an answer can have an email of john@lucaston.com, a surname of Marzalla, and a firstname beginning with M. This query, when run on the winestore database, returns:
+---------+ | cust_id | +---------+ | 440 | | 493 | +---------+ 2 rows in set (0.01 sec)
SELECT queries are often sophisticated and a long WHERE clause may include many AND and OR operators. More complex examples of queries are shown in the later section Section 3.7.
The WHERE clause is also a common component of UPDATE and DELETE statements, and we have shown simple examples of using WHERE with these earlier in this chapter. Consider another example of an UPDATE with a WHERE clause:
UPDATE wine SET winery_id = 298 WHERE winery_id = 299;
In this case, for wines that are made by the winery with winery_id=299, the winery_id is changed to winery_id=298.
The WHERE clause can be used similarly in a DELETE. Consider an example:
DELETE FROM wine WHERE winery_id = 299;
This removes only selected rows based on a condition; here the wines made by the winery with winery_id=299 are deleted.