ClassicASP SQL WHERE, AND, & OR
페이지 정보
본문
Often times a single search condition with a singe WHERE clause is not enough and we need to search on multiple conditions.
This is where additional logical conditions such as AND & OR come into play. Taking our previous example just a bit further we can see how this all works.
Plus, at the end of this post there is a warning on combining the AND & OR condition together.
Here is the data for our example again.
Table 1.
DOG_ORIGIN table
This first example shows the first logical condition (AND) that most practitioners understand and typically use.
A logical condition simply combines the results of two component conditions to produce a single result.
The logical AND condition facilitates this purpose and is considered.
Just as you would suspect, when using the logical AND condition requires both conditions between the AND to be true for a result set to be returned.
If one condition is FALSE then the total condition is FALSE.
So if we wanted to look for medium sized dogs in the USA we would construct a query like the following:
Another logical conditional operator is the OR condition.
The logical OR condition specifies that one or the other conditions need only be true for a result set to be returned.
If both conditions are False then the statement evaluates to False.
This is just as you would suspect and if we wanted to display the dogs from countries that were big or small we could construct the following:
Problems arise when constructing aSQL
statement that contains both logical operators AND and OR.
Great care must be given in regard to the order they are evaluated.
Just remember that the logical AND condition has a higher priority than the OR operator and thus gets evaluated first.
This is best explained by looking at an example.
Let's say we wanted to look for dogs in the USA that were either Big or Small. We might come up with the following SQL at first but recognize that the result set
is totally WRONG. Somehow we returned "Small" dogs from Australia.
We might re-arrange the SQL a little such as this next example but we quickly realize that it too has returned rows where the dogs are from different countries than that of the USA.
So what can we do? The idea here is to realize that the logical conditions all have a presedence in the order they are evaluated. Just so happens that the AND
condition evaluates before the OR condition. So it is impossible to get the results we want without doing some better grouping of logical conditions. Here are two
different methods that get the proper result sets.
This is where additional logical conditions such as AND & OR come into play. Taking our previous example just a bit further we can see how this all works.
Plus, at the end of this post there is a warning on combining the AND & OR condition together.
Here is the data for our example again.
Table 1.
DOG_ORIGIN table
Country Breed Size
------- --------------------- -----
Germany German Shepherd Dog Big
Germany Dobermann Big
Germany Rottweiler Big
USA Siberian Husky Medium
USA Alaskan Malamute Medium
USA American Bulldog Big
Switzerland Bernese Mountain Dog Big
Switzerland Saint Bernard Dog Big
Switzerland Entlebuch Cattle Dog Medium
Australia Australian Cattle Dog Medium
Australia Jack Russell Terrier Small
This first example shows the first logical condition (AND) that most practitioners understand and typically use.
A logical condition simply combines the results of two component conditions to produce a single result.
The logical AND condition facilitates this purpose and is considered.
Just as you would suspect, when using the logical AND condition requires both conditions between the AND to be true for a result set to be returned.
If one condition is FALSE then the total condition is FALSE.
So if we wanted to look for medium sized dogs in the USA we would construct a query like the following:
SQL > SELECT country, breed, breed_size
FROM dog_origin
WHERE country = 'USA'
AND breed_size = 'Medium';
COUNTRY BREED BREED_SIZE
------------ -------------------- ----------
USA Siberian Husky Medium
USA Alaskan Malamute Medium
Another logical conditional operator is the OR condition.
The logical OR condition specifies that one or the other conditions need only be true for a result set to be returned.
If both conditions are False then the statement evaluates to False.
This is just as you would suspect and if we wanted to display the dogs from countries that were big or small we could construct the following:
SQL > SELECT country, breed, breed_size
FROM dog_origin
WHERE breed_size = 'Big'
OR breed_size = 'Small';
COUNTRY BREED BREED_SIZE
------------ ------------------- ----------
Germany German Shepherd Dog Big
Germany Dobermann Big
Germany Rottweiler Big
USA American Bulldog Big
Switzerland Bernese Mountain Dog Big
Switzerland Saint Bernard Dog Big
Australia Jack Russell Terrier Small
Problems arise when constructing a
Great care must be given in regard to the order they are evaluated.
Just remember that the logical AND condition has a higher priority than the OR operator and thus gets evaluated first.
This is best explained by looking at an example.
Let's say we wanted to look for dogs in the USA that were either Big or Small. We might come up with the following SQL at first but recognize that the result set
is totally WRONG. Somehow we returned "Small" dogs from Australia.
SQL > SELECT country, breed, breed_size
FROM dog_origin
WHERE country = 'USA' AND breed_size = 'Big' OR breed_size = 'Small';
COUNTRY BREED BREED_SIZE
------------------------------ ------------------------------ ----------
USA American Bulldog Big
Australia Jack Russell Terrier Small
2 rows selected.
We might re-arrange the SQL a little such as this next example but we quickly realize that it too has returned rows where the dogs are from different countries than that of the USA.
SQL > SELECT country, breed, breed_size
FROM dog_origin
WHERE breed_size = 'Big' OR breed_size = 'Small' AND country = 'USA';
COUNTRY BREED BREED_SIZE
------------------------------ ------------------------------ ----------
Germany German Shepherd Dog Big
Germany Dobermann Big
Germany Rottweiler Big
USA American Bulldog Big
Switzerland Bernese Mountain Dog Big
Switzerland Saint Bernard Dog Big
6 rows selected.
So what can we do? The idea here is to realize that the logical conditions all have a presedence in the order they are evaluated. Just so happens that the AND
condition evaluates before the OR condition. So it is impossible to get the results we want without doing some better grouping of logical conditions. Here are two
different methods that get the proper result sets.
SQL > SELECT country, breed, breed_size FROM dog_origin
WHERE country = 'USA' AND breed_size = 'Big'
OR country = 'USA' AND breed_size = 'Small';
COUNTRY BREED BREED_SIZE
------------------------------ ------------------------------ ----------
USA American Bulldog Big
SQL > SELECT country, breed, breed_size FROM dog_origin
WHERE country = 'USA'
AND ( breed_size = 'Big' OR breed_size = 'Small' );
COUNTRY BREED BREED_SIZE
------------------------------ ------------------------------ ----------
USA American Bulldog Big
자료출처 : http://blogs.ittoolbox.com/database/solutions/archives/finding-data-sql-where-and-or-11191
댓글목록
등록된 댓글이 없습니다.