So far, we have looked at selecting all data from a table and selecting particular columns . Next, we will consider how to select particular rows. This is useful because we frequently want to select records from a table or tables that match particular search criteria. This becomes more important when we need to retrieve a few useful rows from a much larger table. We can accomplish this using the WHERE clause of the SELECT statement. A simple example follows : select employeeID, name from employee where job='Programmer'; (Remember, by the way, that we can set queries out on multiple lines. Each query is terminated by a semicolon. We lay out the SELECT statement like this to make it easier to read.) The results of running this query on the employee database are as shown here: +-------------+--------------+ employeeID name +-------------+--------------+ 6651 Ajay Patel 7513 Nora Edwards +-------------+--------------+ 2 rows in set (0.42 sec) We used a condition in the WHERE clause to match only the rows in the table that met the specified criteria ”in this case, they had to be employed as programmers. Notice that we have combined this with a specific list of desired columns ( employeeID and name ) to pull out only the information we are interested in. In this case, we have used a test of equality in the WHERE clause. Note that SQL uses = for testing equality. This is different from various other languages that use == or eq . A huge variety of functions are available for use in the WHERE clause, and we will address these in detail in Chapter 8. For the time being, we will mention only the most commonly used operators:
In addition to the operators, we will use one function in some examples. The count() function allows us to count the number of rows returned by a query. For example: select count(*) from employee; This query will tell us how many rows there are in the employee table. Finally, we can control precedence by grouping expressions with parentheses. An example of a slightly more complex query using WHERE is as follows: select * from assignment where employeeID=6651 and hours > 8; This query selects all the work assignments performed by employeeID 6651 (Ajay Patel) in which he performed more than 8 hours of work. One important point to note is that we are not allowed to use any column aliases in the WHERE clause. We must use the original column name. This is an ANSI SQL limitation. The reason for it is that the value of the aliased column may be unknown at the time the WHERE condition is examined. |