MapInfo Pro

Over the coming weeks, I'll write a #series of articles about the #SQL improvements we have made in MapInfo Pro v2019. A big part of this will be the Select statement but we have also made a number of other improvements that I'll cover.

In this article, I'll write about the improvement around selecting all columns. In SQL this is done using the Asterix (*).

Selecting All Columns

Until MapInfo Pro v2019 was released MapInfo Pro only supported the "stand-alone" *. You were not able to combine this with a table to specify which table you wanted to select the columns from: tablename.*. If you were using the * in a join, you would get all the columns from all the tables.

We have changed that with MapInfo Pro v2019.

You can now prefix the Asterix with a table name to indicate which table you want to select all the columns from. You can do this in Select statements where you are selecting from just one table and in Select statements where you are joining tables.

The standalone Asterix and the Asterix in combination with a table name can only be used in the projection list. That's the list of columns that comes right after the Select keyword and before the From keyword.

Here are a few examples:

One Table

When you are selecting from only one table, you typically aren't using this method to specify which table to select the columns from. It's more to indicate that you want all the columns and then some additional derived columns, for example adding a column with the calculated area.

Select Parcels.*, CartesianArea(obj, "sq m") "Area_sqm"
From Parcels
Into Selection

You can also use the Table Alias combination with the *:

Select p.*, CartesianArea(obj, "sq m") "Area_sqm"
From Parcels As "p"
Into Selection

Joins

When you are joining multiple tables, the * can be used in the same way as with just one table. But with joins, you sometimes want all columns from one table and just one or a few columns from the other table. This can also be done with tablename.*.

Here's an example where you want all columns from one table and just one from the other table:

Select p.*, a.Address
From Parcels As "p", Addresses As "a"
Where p.obj Contains a.obj
Into Selection

You can also use it to add additional derived columns to the projection list.

Select a.*, CartesianArea(p.obj, "sq m") "Area_sqm"
From Addresses As "a", Parcels As "p"
Where p.obj Contains a.obj
Into Selection

I hope this gives you some ideas on how you can benefit from combing a table name or table alias with the Asterix in your queries.

------------------------------
Peter Horsbøll Møller
Distinguished Engineer
Pitney Bowes Software & Data
------------------------------