Полезная информация

next up previous contents index
Next: LIKE Comparison Up: Customizing Queries Previous: Comments

AND/OR usage

Up to this point, there have been only simple WHERE clause tests. In the next few sections, we will show how to do more complex WHERE clause testing.

Multiple WHERE clause tests are done by connecting simple tests using the words AND and OR. For illustration, I have loaded the friends table with new people in figure [*].

  
Figure: New friends
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...vert Williamsport~~~\vert PA~~~\vert~22
\par (6~rows)\end{list}\par
\end{figure}

Selecting certain rows from the table will require more complex WHERE conditions. For example, If we wanted to select Sandy Gleason, it would be difficult with only one comparison in the WHERE clause. If we tested for firstname = 'Sandy', we would select both Sandy Gleason and Sandy Weber. If we tested for lastname = 'Gleason', we would get both Sandy Gleason and her brother Dick Gleason. The proper test is to use AND to joins tests of both firstname and lastname. The proper query is shown in figure [*].
  
Figure: WHERE test for Sandy Gleason
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...\vert Ocean~City~~~~~\vert NJ~~~\vert~25
\par (1~row)\end{list}\par
\end{figure}

The AND joins the two comparisons we needed.

A similar comparison could be done to select friends living in Cedar Creek, Maryland. There could be other friends living in Cedar Creek, Ohio, so the comparison city = 'Cedar Creek' is not enough. The proper test is city = 'Cedar Creek' AND state = 'MD'.

Another complex test would be to select people who are in the state of New Jersey (NJ) or Pennsylvania (PA). Such a comparison requires the use of OR. The test state = 'NJ' OR state = 'PA' would return the desired rows, as shown in figure [*].

  
Figure: Friends in New Jersey and Pennsylvania
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...vert Williamsport~~~\vert PA~~~\vert~22
\par (3~rows)\end{list}\par
\end{figure}

An unlimited number of ANDs and ORs can be linked together to perform complex comparison tests. When ANDs are linked with other ANDs, there is no possibility for confusion. The same is true of ORs. However, when ANDs and ORs are both used in the same query, the results can be confusing. Figure [*] shows such a case.

  
Figure: Mixing ANDs and ORs
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...vert Williamsport~~~\vert PA~~~\vert~22
\par (3~rows)\end{list}\par
\end{figure}

You may suspect that it would return rows with firstname equal to Victor and state equals PA or NJ. In fact, the query returns rows with firstname equal to Victor and state equals PA, or state equals NJ. In this case, AND is evaluated first, then OR. When mixing ANDs and ORs, it is best to collect the ANDs and ORs into common groups using parentheses. Figure [*] shows the proper way to enter this query.
  
Figure: Properly mixing ANDs and ORs
\begin{figure}\begin{list}{}{
\setlength{\rightmargin}{\leftmargin}
\raggedrigh...
...ert Williamsport~~~\vert PA~~~\vert~22~
\par (1~rows)\end{list}\par
\end{figure}

Without parentheses, it is very difficult to understand a query with mixed ANDs and ORs.
 
Table: Comparisons
Comparison Operator
less than <
less than or equal <=
equal =
greater than or equal >=
greater than >
not equal <> or !=



next up previous contents index
Next: LIKE Comparison Up: Customizing Queries Previous: Comments
Bruce Momjian
1999-11-21