|
Arbitrarily complex conditions can be formulated. Thereby concatenations of the individual
conditions have to be extended. Complex queries are formulated by combining different attributes.
For such queries, logical operators
are used to combine the expressions (with two possible values "true" or "false").
Logical operators | Meaning | Result | Venn diagrams |
---|---|---|---|
AND | Intersection | True, if both are true. | |
OR | Union | True, if at least one is true. | |
XOR | Symmetric difference; excluding OR | True, if exactly one is true, but not both. | |
NOT | Set difference (complement) | True, if one is false. |
To make such queries understandable, Venn diagrams are used. Have a look at the previous table
and the following explanation.
The circles number 1 and 2 graphically represent two conditions: the shaded area represents the true
statement, while the part outside the circle does not correspond to a result.
To explain this situation, the above example is used.
Circle 1 : Tree species = "larch"
Circle 2 : Stock > 110 m3/ha
In the following, it is shown for each operator how SQL‐queries are formulated and how the results are presented.
INPUT
Graphic
|
Table
|
Example 1:
Operator | Query | SQL |
---|---|---|
AND | Find all parcels that are forested with larch and where the stock is greater than 110m3/ha. | select ParzelleID, Baumart, Vorrat from Parzelle where Baumart = "Lärche"and Vorrat > 110 |
Example 2:
Operator | Query | SQL |
---|---|---|
OR | Find all parcels that are forested with larch or where the stock is greater than 110m3/ha. | select ParzelleID, Baumart, Vorrat from Parzelle where Baumart = "Lärche" or Vorrat > 110 |
Example 3:
Operator | Query | SQL |
---|---|---|
XOR | Find all parcels that are forested with larch or where the stock is greater than 110m3/ha, but which do not meet both of these conditions. | select ParzelleID, Baumart, Vorrat from Parzelle where Baumart = "Lärche" xor Vorrat > 110 |
Example 4:
Operator | Query | SQL |
---|---|---|
NOT | Find all parcels which are forested with larch but where the stock is not greater than 110m3/ha. | select ParzelleID, Baumart, Vorrat from Parzelle where Baumart = "Lärche" not Vorrat > 110 |