Query Ninja: An alternative to "OR"

User-added image

Query Ninja: An alternative to "OR"!
User-added imageThe Query Ninja is back with a slick way to select records that meet multiple criteria. The idea here is to provide an alternative to using the "OR" operator. I'll describe this with an example but you should try and 'translate' this into a relevant use with your own data.

The following query examples will use cities and countries as seen in the screen shots below.

User-added image
User-added image

The map and data shown here is the WorldInfo mapping product from Pitney Bowes Software. More info on our data products can be found at www.pitneybowes.com/us/data.html

Selecting all of the cities from a particular country might look like this: 

User-added image 

Using the Or() operator 

Let's say you want to select all of the cities from both Guatemala and Honduras. You may be aware of the Boolean operators, AND, OR and NOT, that are available in MapInfo Pro. Below is how you can select all of the cities from both Guatemala and Honduras using the OR operator. 

User-added image 

If you then need to add a third or more countries the where condition can get very long!

CountryName = "Guatemala" or CountryName = "Honduras" or CountryName = "El Salvador" or "CountryName = "Nicaragua"

There is an easier way! Introducing the In() operator

The In() operator allows you to more easily specify a set of items. Here is the same expression using the In() operator. One thing to note is that you will have to type this into the SQL dialog box. The In() operator does not appear in the Operators list.

User-added image

This expression will achieve the same results but is a lot easier to set up.

Is there a limit to how many items I can list inside the In() operator?

There is a limit but it is not a hard coded number, rather the limit is related to how long an SQL statement can be. The In() operator has another advantage over trying to use a string of OR conditions in that it is much more efficient with space.

Is there more? 

Sure! MapInfo Pro has a number of other keyword clauses you might find useful. In addition to In() there is Any(), All() and Between(). We'll cover these in future articles but if you are curious you can learn more in the MapInfo Pro Help. Look in the section on "Using Logical Operators in Expressions". A search on "Logical Operators" will turn this up.  

Article by Tom Probert, Editor of "The MapInfo Pro" journal 

When not writing articles for "The MapInfo Pro", Tom enjoys talking to MapInfo Pro users at conferences and events. When not working he likes to see movies with car chases, explosions and kung-fu fighting.

UPDATED:  June 28, 2017