PostgreSQL

Скачать в pdf «PostgreSQL»


A similar comparison could be used to select friends living in Cedar Creek, Maryland. Other friends could live 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 live 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 4.12.


An unlimited number of and and OR clauses can be linked together to perform complex comparisons. When ANDs are linked with other ANDs, there is no possibility for confusion. The same is true of ORs. On the other hand, when ANDs and ORs are both used in the same query, the results can be confusing. Figure 4.13 shows such a case. You might suspect that it would return rows with firstname equal to Victor and state equal to PA or NJ. In fact, the query returns rows with firstname equal to Victor and state equal to PA, or state equal to NJ. In this case, the and is


test=> SELECT * FROM friend


test-> WHERE firstname = ‘Victor’ AND state


test-> ORDER BY firstname;


‘PA’ OR state = ‘NJ’


firstname


| lastname


| city


| state |


age


Dick


| Gleason


| Ocean City


| NJ |


19

Скачать в pdf «PostgreSQL»