There's more to experience when you log in!
Over the coming weeks, I'll write a #series of articles about the #SQL improvements we have made in MapInfo Pro v2019. This will, of course, look at the Select statement, we will get there eventually, but I'll also dive into the other improvements.
In this article, we'll dive into another of the improvements we made to the SQL Select statement: Table Alias and Column Alias.
A very useful improvement to the Select statement is the support for Table Alias. Table Alias is an easy way to make it easier to write your Select statement and it also comes with the side effect that you afterward easily can change the table used in your query.
Let's create a simple example of a join between two tables:
Select Addresses.Address, Addresses.AddressPostalCode, Addresses.BuildingName, Planning_Zones.plantype, Planning_Zones.plannr, Planning_Zones.plannavnFrom Addresses, Planning_ZonesWhere Addresses.Obj Within Planning_Zones.ObjInto SelectionInto Selection
Typically that would look like the statement above. Notice how you are using the combination of table and column names in multiple places throughout the statement. Now that we support Table Alias, you can make this statement in this way instead:
Select a.Address, a.AddressPostalCode, a.BuildingName, p.plantype, p.plannr, p.plannavn
From Addresses As "a", Planning_Zones As "p"
Where a.Obj Within p.Obj
Notice how the statement looks easier to read now that we have gotten rid of the long table names. I have in bold highlighted the table and the Table Alias: Addresses As "a". The string in quotes is the Table Alias that you can use instead of the table name in the statement. A Table Alias can be one or more characters identifying the table. I'd typically use one or two characters taken from the table name, often just the first character.
Addresses As "a"
We will not claim that this is a new feature as we have supported Column Alias for a very long time. We have however adopted the general way Column Alias are defined to match how we define a Table Alias, and how other SQL Select dialects define these.
Earlier, you would define a Column Alias simply by adding a text in quotes after the column expression, like this:
Select a.Address "FullAddress", p.*
We still support the method above but now you can also use this method which is very similar to the way you define Table Alias:
Select a.Address As "FullAddress", p.*
The only difference is the As keyword adding in front of the Column Alias.
I hope that you have found this small tip useful and that it will help you write your SQL Select statement.
SELECT a.Address As "FullAddress" FROM Addresses As "a"
SELECT a.Address FullAddress FROM Addresses a