Using Logical Operators in Expressions - MapInfo_Pro - 2023

"And", "or", and "not" are logical operators. You use them to combine expressions in Select and the Where Condition clause of SQL Select. MapInfo Pro treats each such an expression as a test, which it applies to each record in the table. For each test it gets a yes/no (true/false) answer. MapInfo Pro uses the logical operators to tell it how to combine the individual yes/no answers into an overall yes/no answer: Does the current record meet the selection condition?

Suppose you want to select all properties that are worth $250,000 or more and are in Columbia county. Each record has to meet two criteria, each of which can be formulated as a simple expression:

• VALUE >= 250000
• COUNTY = "Columbia"

You could perform one selection for all properties worth $250,000 or more. Then you could perform another selection on that result, looking for all properties in Columbia county. However, it is easier to combine the two operations into one using the logical operator "and".

• COUNTY = "Columbia" and VALUE >= 250000

When MapInfo Pro examines a record to see whether or not it meets the condition set by this expression, it makes the two tests: Does COUNTY equal Columbia? Is the VALUE equal to or greater than 250000? When the answer to both of these questions is true (or yes), then the record is accepted into the current selection. When the answer to one or both of the questions is no (or false), then the record is not accepted into the current selection.

Now, what if you want all properties worth $250,000 or more and not in Columbia county? You can use "not" to negate the first clause of expression 33, yielding expression 34:

• not (COUNTY="Columbia") and VALUE>=250000

Only records where the county is not Columbia satisfy the first clause of expression 34. Now consider expression 35:

• not (COUNTY="Columbia" and VALUE>=250000)

Expression 35 is simply the negation of expression 33. Any record that would satisfy 33 does not satisfy 35. Any record that does not satisfy 33 satisfies 35.

You can use "or" when you want to specify alternative conditions, such as:

• COUNTY="Columbia" or COUNTY="Greene"

Any record evaluated against this condition is accepted if its county is any one of the two specified counties. One could, of course, use numerical tests as well. For example:

• TOTAL_AREA>40 or VALUE>250000

This tests to see whether the area is greater than 40 or the value is greater than 250000. When either one is true of a record, then that record is accepted into the selection.

In formulating expressions using logical operators you have to be careful how you use them. The following expression, while it seems OK, does not work:

• COUNTY="Columbia" or "Greene"

Judging from its English translation-COUNTY equals Columbia or Greene-this expression should operate just like expression 36 and give us any record containing Columbia County or Greene County. But the rules of computational logic and the rules of English are a bit different.

When MapInfo Pro reads expressions it reads them from left to right. One of the things it has to do is to determine how the items in the expression are grouped. Think of this operation as inserting parentheses into the expression. MapInfo Pro reads expression 35 as though it were grouped like expression 39, which is what we intend.

It reads expression 38 as though it were grouped like 40, which is not at all what we want.

• (COUNTY="Columbia") or (COUNTY="Greene")
• (COUNTY="Columbia") or ("Greene")

Both 39 and 40 have the same first clause. But their second clauses (after the "or") are quite different. The second clause of 40 is simply a literal string, "Greene." By convention, MapInfo Pro evaluates a record against a literal string as being true if that record is not blank. When MapInfo Pro evaluates records against expression 40, all non-blank records are evaluated as true and be accepted into the selection. It does not make any difference how a record evaluates on the first clause. Any non-blank record evaluates as true on the second clause, and one "true" is all it takes to evaluate the entire expression as true.

Let us consider one final example, which is the negation of expression 36:

• not (COUNTY="Greene" or COUNTY="Columbia")

Expression 39 is satisfied if the county is Greene or if it is Columbia, but not if it is Montgomery or Warren. Expression 41 is satisfied by any county other than Greene or Columbia, including Montgomery and Warren.

Numeric Clauses

English: All the household income is above $65,000 and the median age is 42.

• HH_INC>=65000 and Round(MED_AGE, 1)=42

Comment: This expression simply consists of two clauses. The clauses are connected by "and", that means that both clauses must be true of a record for it to be selected.

English: All with a price between $50,000 and $100,000.

• PRICE>=50000 and PRICE<=100000

Comment: Now we are setting two conditions that a record must satisfy in order to be selected. By using the operators "greater than or equal to" (>=) and "less than or equal to" (<=) we ensure that the records with the exact prices of $50,000 and $100,000 are selected. When we had simply used "greater than" (>) and "less than" (<), the expression would select $50,001 and $99,999 but not $50,000 or $100,000.

English: All with a price between $50,000 and $100,000 or between $150,000 and $200,000.

• (PRICE>=50000 and PRICE<=100000) or (PRICE>=150000 and PRICE<=200000)

Comment: This expression has the overall form: (expression1) or (expression2). Each of these expressions has the same form as expression 36. When a row meets the condition specified by either expression1 or expression2, MapInfo Pro puts the row into the selection.

String Clauses

English: All customers from N to Q.

• LAST_NAME >= "N" and LAST_NAME <"R"

Comment: The first part of the expression checks for names that are either alphabetically equal to "n" or that are alphabetically greater than (after) "n". The second part of the expression checks for names that are alphabetically less than (before) "r". Any name starting with letters "n" through "q" satisfies this condition.

English: All customers from N to Z.

• LAST_NAME >= "N"

Comment: Since "z" is the last letter in the alphabet, there is no need to test for it. There cannot be any customers with a last name starting with some letter beyond "z".

English: All customers whose last name begins with C.

• LAST_NAME>="C" and LAST_NAME<"D"

Comment: The logic of this expression is the same as the logic for expression 48.

English: All customers whose last name is between "Ce . . ." and "Cn . . ."

• LAST_NAME=>"CE" and LAST_NAME<"CO"

Comment: The logic of this expression is the same as the logic for expression 46.

English: All not from Texas.

• not STATE="TX"

Comment: The expression causes MapInfo Pro to check the contents of the STATE column. When there is an entry other than "TX" the row is selected.

Date Clauses

English: Records for all received in August 1990

• Month(RECEIVED)=8 and Year(RECEIVED)=1990

Comment: In this expression we specify the year explicitly, using the "year(<somecolumn>)" function to extract it from the date.

• RECEIVED>="8-1-90" and RECEIVED<="8-31-90"

Comment: In this expression we do not explicitly ask for month and year. Instead, we rethink a bit and use an expression that might more accurately be rendered in English as: Records for all received on or after August 1, 1990 and on or before August 31, 1990. The two expressions give equivalent results, but they are formulated differently.

English: Records for all received in July or September

• Month(RECEIVED)=7 or Month(RECEIVED)=9

Comment: As in a previous example, no year for the dates. Depending on the data in your table and what you are trying to achieve, that may or may not be a problem.

When you want to specify the year:

English: Records for all received in July or September of 1989

• month(RECEIVED)=any(7, 9) and year(RECEIVED)=89

Comment: The first clause of this expression uses the keyword "any". The first clause is satisfied if any of the items in parenthesis match the month received - in this case, the months of July and September.

Keyword Clauses

MapInfo Pro supports the use of keywords "any", "all", "in" and "between". These keywords must be typed into expressions.

Use "any" to select any item in a set of items.

• ABBR = any("AL", "MN", "TX")

Expression 54 is true of any record where the state is Alabama, Minnesota, or Texas.

To understand the use of "all" consider expression 55:

• ABBR <> all("AL", "MN", "TX")

This statement says: Give me all orders where the state does not equal Alabama, Minnesota, or Texas. It selects all orders except those from Alabama, Minnesota or Texas. Consider what would happen if we issued 56:

• ABBR <> any("AL", "MN", "TX")

Orders from Alabama would be selected because they are not from either Minnesota or Texas; and orders from Minnesota and Texas would be selected for a similar reason.

This example, shows the use of "in":

• ABBR in("AL", "MN", "TX")

This produces the same result as 54.

Note that "in" is equivalent to "=any" and "not in" is equivalent to "<>all".

Finally, consider 58 and 59, which illustrate "between":

• PRICE between 50000 and 100000
• (PRICE between 50000 and 100000) or (PRICE between 150000 and 200000)

These are equivalent to expressions 43 and 44 respectively. You can also use between with character strings (such as are in examples 48, 50, and 51).