SQL Select:  The "Super-Select"
Presented at the Puget Sound MapInfo User Group July 9, 2002
John Schlosser, Schlosser Geographic Systems, Inc. (SGSI).  Edited July 2004 and again January 2008.

 

Overview

  • MapInfo Pro has two "Select" menu commands:
    Query > Select  and
    Query > SQL Select.
  • "SQL Select" does everything done by the simple "Select"—only much more.
  • Purpose of this session is to learn to: 
    -- Choose the appropriate command:  "Select" vs. "SQL Select";
    -- Sample the power of the "SQL Select" syntax.

What is "SQL"?

  • SQL = Structured Query Language.
  • SQL is the industry-standard syntax for asking questions of a database.  It originated with IBM.  Now used widely by most vendors, albeit with slight variations in syntax.
  • A simple SQL query:
    SELECT name, address, zip FROM AddressList WHERE zip = "98101" ORDER BY name INTO qNameAddrList
MapInfo's SQL

Two ways to use SQL:

1.  To address MapInfo Tables directly.  

In this case dialogs  mask the complexity of SQL, but MapInfo is using SQL "behind the scenes" nonetheless.   MapInfo's internal  implementation of SQL is not as complete as Oracle's or IBM's implementation.  There is still more SQL power in MapInfo Pro than 99% of people will use. MapInfo's SQL support is still more complete than any other GIS.

Tip:  Keep your MapBasic window open at all times, and see how SQL commands are built by MapInfo.

2.  To compose & send SQL commands to remote databases such as DB2, Oracle and SQL Server. 

In this case, MapInfo can send the most complex queries supported by the remote DBMS.  The syntax is interpreted by the remote DBMS, not by MapInfo.

MapInfo receives the results of the SQL query and treats it as a MapInfo table.

Summary:  There is still more SQL power in MapInfo Pro than 99% of people will use.

MapInfo's SQL Spatial syntax:
With standard (non-GIS) SQL you "join" tables together by matching columns and values:  e.g.,  "...where Table1.zip = Table2.zip".

MapInfo offers spatial extensions to SQL syntax:   e.g., "...where Table1Pnt.obj WITHIN Table2Region.obj"
SQL Select vs. simple Select:  Examples

When to use SQL Select instead of the simple Select?

To view selected, not all columns:

SELECT col1, col5, col3 FROM MyTable

To sort your results by more than one column:

SELECT col1, col5, col3 FROM MyTable ORDER BY col5, col1

To aggregate or summarize table values:

SELECT CountyFIPS, Sum(Pop_Tract) FROM PopByTract GROUP BY CountyFIPS

To temporarily join and display values from two tables:

SELECT Table1.CountyFIPS, Table1.CountyRevenue, Table2.CntyFIPS, Table2.Population FROM Table1, Table2 WHERE Table1.CountyFIPS = Table2.CntyFIPS INTO qComboTbl1_Tbl2

To temporarily rename columns:

SELECT Pop1990_2002 "Pop Change" FROM MyPopTable

Tip:  Since SQL Select does everything, why not ignore the simple Select menu entirely?
SQL Examples

Finding Points Outside a Region

The resulting table will be a list of points that fall outside a region object.

1. Open point and boundary file.

2. Select objects in the boundary file.

3. Perform the following SQL:

sql2.jpg (29108 bytes)

Calculating the number of Points that Fall within a Boundary

The resulting table will list the boundary name and the number of points that are contained inside the region.

1. Open Boundary and Points tables.

2. Perform the following SQL Select:

sql1.jpg (32829 bytes)

 

Using "ANY", "ALL" & "IN" syntax:

= ANY compares separately against each value in a list of items. E.g.,

SELECT * FROM tablename WHERE STATE_ABBR = ANY ("NY", "CA")
This example selects records if column STATE_ABBR matches ANY one of the values in the list.

SELECT * FROM tablename WHERE STATE_ABBR <> ANY ("NY", "CA")
This example selects no STATES.TAB records because each record fails to match at least one of the values in the list.

IN functions like = ANY . It lets you select any item from a set.

SELECT * FROM tablename WHERE STATE_ABBR IN ("NY, CA")
This example selects all records with values of STATE_ABBR equal to "NY" or to "CA"

ALL compares against the list as a whole. E.g.,

SELECT * FROM tablename WHERE STATE_ABBR <> ALL ("NY", "CA")
This example will select all the states except NY and CA

NOT IN is equivalent to the <> ALL term. [Notice the quotes.]

SELECT * FROM tablename WHERE STATE_ABBR NOT IN ("NY", "CA")
This example will select all records except those with values of "NY" and "CA" in the STATE_ABBR column.
Tricks & Traps

I used a "Group By" clause to summarize my table, now the table does not show on the map at all.  Why?

Description:
The "Group By" option summarizes the text or other values in the table, but cannot be used to aggregate the map regions, points, or other geometry.  The results of a "group by" query do not include any map objects.  Often, you can make your "group by" results mappable by saving it, then geocoding it.

I tried to specify a long list of column names and other long entries in the SQL Select dialog box.   But only part of my entries were processed.  Why?

Description:
The SQL Select dialog accepts a maximum number of (we think) 254 characters.  If your list of column names in the "Columns" areas or your list of conditions and/or functions in the "Where" area exceed the limit, the rest just doesn't get processed. 
Workaround:  Keep your MapBasic window open.  Issue and/or edit the SQL Select command in this MapBasic window.  It can accept and will process longer command strings.

 
SGSI Product support:
> http://www.sgsisupport.com/
NW MapInfo User Group
> http://www.miuser.com/

Terms of use | Trademarks & copyrights | SGSI Home | Top of page
Email | Map to Seattle office (pdf) http://www.sgsi.com/
Schlosser Geographic Systems, Inc. (SGSI)
 

Seattle, WA: 206-224-0800
Honolulu, HI: 808-585-1100
Portland, OR: 503-224-0400