Into Statement  «Prev  Next»
Lesson 3 Revisit the WHERE clause
ObjectiveCreate an SQL statement to return the required Information, and only the required Information


The second way you control the results set is by limiting the rows that are returned in response to your query by using the WHERE clause. Suppose, for example, that you want to return the first name of the individual who has a last name of Andrews. Here is an example SQL statement that would accomplish this:

WHERE lastname='Andrews' 

The result would be a single column, Firstname. There is one catch: If you have more than one row in the database with a last name of Andrews, you will get back more than one first name.
To get around this, you can combine elements, providing more than one test that needs to be passed to be part of the results set. Simply place the keyword AND between the tests. So, if there was more than one customer with the last name of Andrews, you could try:
WHERE lastname='Andrews' AND firstname='Julie' 

The jargon term for this is horizontal partitioning. This refers to the fact that you are making cuts on the information that pertains to rows that are returned. In this specific example, we have employed both horizontal and vertical partitioning techniques because we have indicated both a limited set of columns to return and a filter on the rows to be considered.

Theory behind the WHERE clause

You can use the WHERE clause to filter unwanted rows from the result. This filtering capability gives the SELECT statement its real power. In a WHERE clause, you specify a search condition that has one or more conditions that need to be satisfied by the rows of a table. A condition, or predicate, is a logical expression that evaluates to true, false, or unknown. Rows for which the condition is true are included in the result; rows for which the condition is false or unknown are excluded. (An unknown result, which arises from nulls, is described in the next section.) SQL provides operators that express different types of conditions. Operators are symbols or keywords that specify actions to perform on values or other elements.
Figure 6-3: A sample syntax diagram for a where clauses using the DELETE statement

Variable items in a SQL statement (such as the table name and search condition in Figure 6-3) are shown in lowercase italics. It is up to you to specify the appropriate item value(s) each time the statement is used. Optional clauses and keywords, such as the WHERE clause in Figure 5-2, are indicated by alternate paths through the syntax diagram. When a choice of optional keywords is offered, the default choice (that is, the behavior of the statement if no keyword is specified) is UNDERLINED.
Filtering Rows with WHERE

Complex Sql Statements - Exercise

Click the Exercise link below to practice making more complex statements.
Complex SQL Statements - Exercise