SQL Select statement

Purpose 

Selects particular rows and columns from one or more open tables, and treats the results as a separate, temporary table. Also provides the ability to sort and sub-total data. 

Syntax

Select  expression_list
     From  table_name [ , ... ]   [ Where  expression_group ]
  	 [ Into  results_table  [ Noselect  ]  ]   
  	 [ Group By  column_list ] 
  	 [ Order By  column_list ]
expression_list  is a comma-separated list of expressions which will comprise the columns of the Selection results
expression_group is a list of one or more expressions, separated by the keywords AND or OR
table_name  is the name of an open table
results_table  is the name of the table where query results should be stored

column_list is a list of one or more names of columns, separated by commas

Description

The Select statement provides MapBasic programmers with the capabilities of MapInfo's Query > SQL Select dialog. 
Note that the MapBasic Select statement is modeled after the Select statement in the Structured Query Language (SQL). Thus, if you have used SQL-oriented database software, you may already be familiar with the Select statement. Note, however, that MapBasic's Select statement includes geographic capabilities that you will not find in other packages. 
Column expressions (e.g. tablename.columnname ) in a Select statement may only refer to tables that are listed in the Select statement's From clause. For example, a Select statement may only incorporate the column expression STATES.OBJ if the table STATES is included in the statement's From clause. 
The Select statement serves a variety of different purposes. One select statement might apply a test to a table, making it easy to browse only the records which met the criteria (this is sometimes referred to as filtering). Alternately, Select might be used to calculate totals or subtotals for an entire table. Select can also: sort the rows of a table; derive new column values from one or more existing columns; or combine columns from two or more tables into a single results table. 

Generally speaking, a Select statement queries one or more open tables, and selects some or all of the rows from said table(s). The Select statement then treats the group of selected rows as a results table; Selection is the default name of this table (although the results table can be assigned another name through the Into clause). Following a Select statement, a MapBasic program - or, for that matter, a MapInfo user - can treat the results table as any other MapInfo table. 
After issuing a Select statement, a MapBasic program can use the SelectionInfo( ) function to examine the current selection. 
The Select statement format includes several clauses, most of which are optional. The nature and function of a Select statement depend upon which clauses are included. For example:  if you wish to use a Select statement to set up a filter, you should include a Where clause; if you wish to use a Select statement to subtotal the values in the table, you should include a Group By clause;  if you want MapBasic to sort the results of the Select statement, you should include an Order By clause. Note that these clauses are not mutually exclusive; one Select statement may include all of the optional clauses. 

Select clause

This clause dictates which columns MapBasic should include in the results table. The simplest type of expression_list is an asterisk character ("*"). The asterisk signifies that all columns should be included in the results. The statement: 

  Select  *  From world

tells MapBasic to include all of the columns from the "world" table in the results table. Alternately, the expression_list clause can consist of a list of expressions, separated by commas, each of which represents one column to include in the results table. Typically, each of these expressions involves the names of one or more columns from the table in question. Very often, MapBasic function calls and/or operators are used to derive some new value from one or more of the column names. For example, the following Select statement specifies an expression_list clause with two expressions:   

  Select  country, Round(population,1000000)  
    From world

The expression_list above consists of two expressions, the first of which is a simple column name (country), and the second of which is a function call ( Round( ) ) which operates on  another column (population). 
After MapBasic carries out the above Select statement, the first column in the results table will contain values from the world table's name column. The second column in the results table will contain values from the world table's population column, rounded off to the nearest million. 
Each expression in the expression_list clause can be explicitly named by having an alias follow the expression; this alias would appear, for example, at the top of a Browser window displaying the appropriate table. The following statement would assign the field alias "Millions" to the second column of the results table: 

  Select country,Round(population,1000000) "Millions"
    From world

Any mappable table also has a special column, called object (or obj for short). If you include the column expression  obj  in the expression_list, the resultant table will include a column which indicates what type of object  (if any) is attached to that row. 
Note that the expression_list may include either an asterisk or a list of column expressions, but not both. If an asterisk appears following the keyword Select, then that asterisk must be the only thing in the expression_list. In other words, the following statement would not be legitimate: 

  Select  *, object  From world   ' this won't work!

From clause

The From clause specifies which table(s) to select data from. If you are doing a multiple-table join, the tables you are selecting from must be base tables, rather than the results of a previous query.

Where clause

One function of the Where clause is to specify which rows to select. Any expression can be used (see Expressions section below). Note, however, that groups of two or more expressions must be connected by the keywords And or Or, rather than being comma-separated. For example, a two-expression Where clause might read like this:  

    Where  Income > 15000  And  Income < 25000

Note that the And operator makes the clause more restrictive (both conditions must evaluate as TRUE for MapBasic to select a record), whereas the Or operator makes the clause less restrictive (MapBasic will select a record if either of the expressions evaluates to TRUE). 
By referring to the special column name object, a Where clause can test geographic aspects of each row in a mappable table. Conversely, the expression  "Not object"  can be used to single out records which do not have graphical objects attached. For example, the following Where clause would tell MapBasic to select only those records which are currently un-geocoded: 

    Where  Not Object 

If a Select statement is to use two or more tables, the statement must include a Where clause, and the Where clause must include an expression which tells MapBasic how to join the two tables. Such a join-related expression typically takes the form  Where tablename1.
field = tablename2.field, where the two fields have corresponding values. 
The following example shows how you might join the tables "States" and "City_1k." The column City_1k.state contains two-letter state abbreviations which match the abbreviations in the column  States.state. 

    Where  States.state = City_1k.state

Alternately, you can specify a geographic operator to tell MapInfo how to join the two tables. 

    Where  states.obj  Contains  City_1k.obj 

A Where clause can incorporate a subset of specific values by including the Any or All keyword. The Any keyword defines a subset, for the sake of allowing the Where clause to test if a given expression is TRUE for any of the values in the subset. Conversely, the All keyword defines a subset, for the sake of allowing the Where clause to test if a given condition is true for all of the values in the subset. 
The following query selects any customer record whose state column contains "NY,"  "MA," or "PA."  The Any( ) function functions the same way as SQL's "IN" operator.

  Select * From customers
    Where state = Any ("NY", "MA", "PA")

A Where clause can also include its own Select statement, to produce what is known as a subquery. In the next example, we use two tables:  "products" is a table of the various products which our company sells, and "orders" is a table of the orders we have for our products. At any given time, some of the products may be sold out. The task here is to figure out which orders we can fill, based on which products are currently in stock. This query uses the logic, "select all orders which are not among the list of items that are currently sold out."

 Select * From orders 
   Where partnum <> 
   All(Select partnum from products 
          where not instock)

On the second line of the query, the keyword Select appears a second time; this produces our sub-select. The sub-select builds a list of the parts that are currently not in stock. The Where clause of the main query then uses All() function to access the list of unavailable parts. 
In the example above, the sub-select produces a set of values, and the main select statement's Where clause tests for inclusion in that set of values. Alternately, a sub-select might use an aggregate operator to produce a single result. The example below uses the Avg( ) aggregate operator to calculate the average value of the pop field within the table states. Accordingly, the net result of the following Select statement is that all records having higher-than-average population are selected. 

Select * From states
  Where population > 
    (Select Avg(population) From states)

MapInfo also supports the SQL keyword In. A Select statement can use the keyword In in place of the operator sequence = Any. In other words, the following Where clause, which uses the Any keyword: 

  Where state = Any ("NY", "MA", "PA")

 is equivalent to the following Where clause, which uses the In keyword: 

  Where state  In ("NY", "MA", "PA")

In a similar fashion, the keywords Not In may be used in place of the operator sequence: 
<> All. 
Note:  A  single Select statement may not include multiple, non-nested subqueries. Additionally,  MapBasic's Select statement does not support "correlated subqueries." A correlated subquery involves the inner query referencing a variable from the outer query. Thus, the inner query is reprocessed for each row in the outer table. Thus, the queries are correlated. An example:

' Note: the following statement, which illustrates
' correlated subqueries, will NOT work in MapBasic

Select * from leads
Where lead.name = 
   (Select var.name From vars 
      Where lead.name = customer.name)

This limitation is primarily of interest to users who are already proficient in SQL queries, through the use of other SQL-compatible database packages.

Into  clause

This optional clause lets you name the results table. If no Into clause is specified, the resulting table is named Selection. Note that when a subsequent operation references the Selection table, MapInfo will take a "snapshot" of the Selection table, and call the snapshot QUERYn  (e.g. QUERY1). 
If you include the Noselect keyword, the statement performs a query without changing the pre-existing Selection table. Use the NoSelect keyword if you need to perform a query, but you do not want to de-select whatever rows are already selected. Note: If you include the Noselect keyword, the query does not trigger the SelChangedHandler procedure. 

Group By clause

This optional clause specifies how to group the rows when performing aggregate functions (sub-totalling). In a Group By clause, you typically specify a column name (or a list of column names); MapBasic then builds a results table containing subtotals. For example, if you want to subtotal your table on a state-by-state basis, your Group By clause should specify the name of a column which contains state names. The Group By clause may not reference a function with a variable return type, such as the ObjectInfo( ) function. 
The aggregate functions Sum( ), Min( ), Max( ), Count(*), Avg( ) and WtAvg( ) allow you to calculate aggregated results. Note: These aggregate functions do not appear in the Group By
 clause. Typically, the Select expression_list clause includes one or more of the aggregate functions listed above, while the Group By clause indicates which column(s) to use in grouping the rows. 

Suppose the Q4Sales table describes sales information for the fourth fiscal quarter. Each record in this table contains information about the dollar amount of a particular sale. Each record's Territory column indicates the name of the territory where the sale occurred. The following query counts how many sales occurred within each territory, and calculates the sum total of all of the sales within each territory. 

  Select territory, Count(*), Sum(amount)
    From q4sales
    Group By territory

The Group By clause tells MapBasic to group the table results according to the contents of the Territory column, and then create a subtotal for each unique territory name. The expression list following the keyword Select specifies that the results table should have three columns: the first column will state the name of a territory; the second column will state the number of records in the q4sales table "belonging to" that territory; and the third column of the results table will contain the sum of the Amount columns of all records belonging to that territory. 
Note that the Sum( ) function requires a parameter, to tell it which column to summarize. The Count( ) function, however, simply takes an asterisk as its parameter; this tells MapBasic to simply count the number of records within that sub-totalled group. The Count( ) function is the only aggregate function that does not require a column identifier as its parameter. 

The following table describes MapInfo's aggregate functions. 

Function name	Description

Avg( column  ) 	Returns the average value of the specified column. 

Count( * ) 	Returns the number of rows in the group. Specify * (asterisk) instead of column name.
Max( column ) 	Returns the largest value of the specified column for all rows in the group. 
Min( column ) 	Returns the smallest value of the specified column for all rows in the group. 
Sum( column ) 	Returns the sum of the column values for all rows in the group. 
WtAvg( column , weight_column )	Returns the average of the column values, weighted. See below. 

Calculating Weighted Averages

Use the Wtavg( ) aggregate function to calculate weighted averages. For example, the following statement uses the Wtavg( ) function to calculate a weighted average of the literacy rate in each continent: 

 Select continent, Sum(pop_1994), WtAvg(literacy, Pop_1994)
   From World 
   Group By continent 
   Into  Lit_query 

Because of the Group By clause, MapInfo groups rows of the table together, according to the values in the Continent column. All rows having "North America" in the Continent column will be treated as one group; all rows having "Asia" in the Continent column will be treated as another group; etc. For each group of rows - in other words, for each continent - MapInfo calculates a weighted average of the literacy rates. 
A simple average (using the Avg( ) function) calculates the sum divided by the count. A weighted average (using the WtAvg( ) function) is more complicated, in that some rows affect the average more than other rows. In this example, the average calculation is weighted by the Pop_1994 (population) column; in other words, countries that have a large population will have more of an impact on the result than countries that have a small population. 

Column Expressions in the Group By clause

In the preceding example, the  Group By territory  clause identifies the Territory column by name. Alternately, a Group By clause can identify a column by a number, using an expression of the form  col#. In this type of expression, the # sign represents an integer number, having a value of one or more, which identifies one of the columns in the Select clause. Thus, the above Select statement could have read  Group By  col1, or even  Group By  1, rather than Group By territory. 
It is sometimes necessary to use one of these alternate syntaxes. If you wish to Group By a derived expression, which does not have a column name, then the Group By clause must use the  col#  syntax  or the #  syntax to refer to the proper column expression. In the following example, we Group By a column value derived through the Month( ) function. Since this column expression does not have a conventional column name, our Group By clause refers to it using the col# format: 

  Select Month(sick_date), Count(*)
    From sickdays
    Group By 1 

This example assumes that each row in the sickdays table represents a sick day claim. The results from this query would include twelve rows (one row for each month); the second column would indicate how many sick days were claimed for that month. 

Grouping By Multiple Columns 

Depending on your application, you may need to specify more than one column in the Group By clause; this happens when the contents of a column are not sufficiently unique. For example, you may have a table describing counties across the United States. County names are not unique; for example, many different states have a Franklin county. Therefore, if your Group By clause specifies a single county-name column, MapBasic will create one sub-total row in the results table for the county "Franklin". That row would summarize all counties having the name "Franklin", regardless of whether the records were in different states. 
When this type of problem occurs, your Group By clause must specify two or more columns, separated by commas. For example, a group by clause might read: 

  Group By  county, state

With this arrangement, MapBasic would construct a separate group of rows (and, thus, a separate sub-total) for each unique expression of the form  countyname , statename. The results table would have separate rows for Franklin County, MA versus Franklin County, FL.

Order By clause

This optional clause specifies which column or set of columns to order the results by. As with the Group By clause, the column is specified by name in the field list, or by a number representing the position in the field list. Multiple columns are separated by commas. 
By default, results sorted by an Order By clause are in ascending order. An ascending character sort places "A" values before "Z" values;  an ascending numeric sort places small numbers before large ones. If you want one of the columns to be sorted in descending order, you should follow that column name with the keyword DESC. 

  Select * From cities  
    Order By state, population Desc

This query performs a two-level sort on the table Cities. First, MapBasic sorts the table, in ascending order, according to the contents of the state column. Then MapBasic sorts each state's group of records, using a descending order sort of the values in the population column. Note that there is a space, not a comma, between the column name and the keyword DESC.
The Order By clause may not reference a function with a variable return type, such as the ObjectInfo( ) function. 

Geographic Operators

MapBasic supports several geographic operators: Contains, Contains Part, Contains Entire, Within, Partly Within, Entirely Within, and Intersects. These operators can be used in any expression, and are very useful within the Select statement's Where clause. All geographic operators are infix operators (operate on two objects and return a boolean). The operators are listed in the table below. 

Usage 	Evaluates TRUE if: 

objectA  Contains  objectB	first object contains the centroid of second object

objectA  Contains Part  objectB	first object contains part of second object
objectA  Contains Entire objectB	first object contains all of second object 
objectA  Within objectB	first object's centroid is within the second object
objectA  Partly Within objectB	part of the first object is within the second object
objectA  Entirely Within  objectB	the first object is entirely inside the second object 
objectA  Intersects  objectB	the two objects intersect at some point 

Selection Performance 

Some Select statements are considerably faster than others, depending in part on the contents of the Where clause. 
If the Where clause contains one expression of the form:
	columnname = constant_expression 
or if the Where clause contains two or more expressions of that form, joined by the And operator, then the Select statement will be able to take maximum advantage of indexing, allowing the operation to proceed quickly. However, if multiple Where clause expressions are joined by the Or operator instead of by the And operator, the statement will take more time, because MapInfo will not be able to take maximum advantage of indexing. 
Similarly, MapInfo provides optimized performance for Where clause expressions of the form: 

[ tablename . ] obj     geographic_operator      object_expression 
and for Where clause expressions of the form: 
	RowID  =  constant_expression 
RowID is a special column name. Each row's RowID value represents the corresponding row number within the appropriate table; in other words, the first row in a table has a RowID value of one. 

Examples  

This example selects all customers that are in New York, Connecticut, or Massachusetts. Each customer record does not need to include a state name; rather, the query relies on the geographic position of each customer object to determine whether that customer is "in" a given state. 

Select * From customers
  Where obj Within Any(Select obj From states 
     Where state = "NY" or state = "CT" or state = "MA")

The next example demonstrates a sub-select. Here, we want to select all sales territories which contain customers that have been designated as "Federal."  The subselect selects all customer records flagged as Federal, and then the main select works from the list of Federal customers to select certain territories. 

Select * From territories
  Where obj Contains Any (Select obj From customers 
     Where customers.source = "Federal")

The following query selects all parcels that touch parcel 120059.

Select * From parcels
  Where obj Intersects (Select obj From parcels
     Where parcel_id = 120059)