MapInfo Pro

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.

Table 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.plannavn
From Addresses, Planning_Zones
Where Addresses.Obj Within Planning_Zones.Obj
Into 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
Into Selection

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.

If you want to use a different table in your statement, you only have to change the table name in the From part of the statement. This of course also requires that any columns used also need to be in both tables.

Also notice that the Join and Column dropdown, and the IntelliSense will use the Table Alias too.

Column Alias

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.*
From Addresses As "a", Planning_Zones As "p"
Where a.Obj Within p.Obj
Into Selection

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.*
From Addresses As "a", Planning_Zones As "p"
Where a.Obj Within p.Obj
Into Selection

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.

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

Hi Peter,

thanks a lot for pushing MI SQL behaviour forward. The new improvements have been missed for a long time, especially after dealing with other (spatial) databases.
A short comment to your approach of table and column aliases: you mention the general way defining column aliases with the "AS" keyword, but in fact most SQL interpreters can easily do without it, since the SQL statement logic doesn't need it. After listing the original column name or a functional/operational transformation, the statement could be continued with either a comma or one or more blanks, followed by another keyword ("FROM" or "AS"). If you miss out the keyword, the only possible meaning could be a column alias. Thus even the quotation marks could be missed out:
SELECT a.Address As "FullAddress" FROM Addresses As "a"​
vs
SELECT a.Address FullAddress FROM Addresses a
Maybe you should consider these ideas when optimizing the MI SQL Syntax, since it is even closer to SQL standards.

Gisbert

------------------------------
Gisbert Schnell
Entwickler
AGIS GmbH
Frankfurt Am Main
------------------------------

Original Message:
Sent: 02-19-2020 21:03
From: Peter Horsbøll Møller
Subject: Table and Column Alias in Select statements

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.

Table 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.plannavn
From Addresses, Planning_Zones
Where Addresses.Obj Within Planning_Zones.Obj
Into 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
Into Selection

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.

If you want to use a different table in your statement, you only have to change the table name in the From part of the statement. This of course also requires that any columns used also need to be in both tables.

Also notice that the Join and Column dropdown, and the IntelliSense will use the Table Alias too.

Column Alias

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.*
From Addresses As "a", Planning_Zones As "p"
Where a.Obj Within p.Obj
Into Selection

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.*
From Addresses As "a", Planning_Zones As "p"
Where a.Obj Within p.Obj
Into Selection

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.

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

Thanks, Gisbert,

That's certainly worth taking into account as it would make it easier to add an alias to your table or column name.

I wonder if it could open the statement up to other issues if people forget to add a comma or include the Where keyword?

Anyway, I'll pass this onto our engineering team so that we get the idea captured.

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

Original Message:
Sent: 03-23-2020 06:33
From: Gisbert Schnell
Subject: Table and Column Alias in Select statements

Hi Peter,

thanks a lot for pushing MI SQL behaviour forward. The new improvements have been missed for a long time, especially after dealing with other (spatial) databases.
A short comment to your approach of table and column aliases: you mention the general way defining column aliases with the "AS" keyword, but in fact most SQL interpreters can easily do without it, since the SQL statement logic doesn't need it. After listing the original column name or a functional/operational transformation, the statement could be continued with either a comma or one or more blanks, followed by another keyword ("FROM" or "AS"). If you miss out the keyword, the only possible meaning could be a column alias. Thus even the quotation marks could be missed out:
SELECT a.Address As "FullAddress" FROM Addresses As "a"​
vs
SELECT a.Address FullAddress FROM Addresses a
Maybe you should consider these ideas when optimizing the MI SQL Syntax, since it is even closer to SQL standards.

Gisbert

------------------------------
Gisbert Schnell
Entwickler
AGIS GmbH
Frankfurt Am Main

Original Message:
Sent: 02-19-2020 21:03
From: Peter Horsbøll Møller
Subject: Table and Column Alias in Select statements

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.

Table 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.plannavn
From Addresses, Planning_Zones
Where Addresses.Obj Within Planning_Zones.Obj
Into 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
Into Selection

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.

If you want to use a different table in your statement, you only have to change the table name in the From part of the statement. This of course also requires that any columns used also need to be in both tables.

Also notice that the Join and Column dropdown, and the IntelliSense will use the Table Alias too.

Column Alias

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.*
From Addresses As "a", Planning_Zones As "p"
Where a.Obj Within p.Obj
Into Selection

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.*
From Addresses As "a", Planning_Zones As "p"
Where a.Obj Within p.Obj
Into Selection

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.

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