JIT Hotel Reservation for the MySQL UC

I finally made hotel reservations for my stay in the U.S. for the MySQL User Conference.

I'm flying to San Fransisco tomorrow and I will arrive there in the afternoon. Then, I'm straight off to Santa Clara to check into my hotel.

I'd like to go for some sightseeing on maybe Saturday but definitely Sunday, and I'm thinking of San Fransisco itself.

I'm supposed to have WIFI access at the hotel, so if anyone would like to join me or arrange a meeting, I should be able to read my mail there too.

Have a nice trip everyone, and see you at the UC!

Be careful with \ in your my.ini

It's probably a nobrainer, but as this is the first time I'm running into this problem, I decided I'd write it down.

I nearly ran out of diskspace on the C: disk of my notebook (running windows there). You now how it goes: I knew I did not have too much space left, but that little...It just couldn't be true. So, I ran a little Windows Wizard to clean up temporary files and such, and the next thing that happened:

ERROR 126 (HY000): Incorrect key file for table 'C:\WINDOWS\TEMP\#sql_474_0.MYI'; try to repair it

Whoamm! The mysql client utility quit running my query. Seems like a temporary table got cleaned up too...
(I'm running an insane query for experimentation purposes).

The good news was that my disk space problems were indeed instantly solved.

Of course, I'm a little bit surprised that it would be possible for windows to throw away the files associated with the temporary table. I would expect them to be locked by the MySQL server so that it would be impossible for windows to delete it.

Anyway, I decided that maybe I should not be so lazy and put some effort in not running into this kind of trouble so easily. I've got lots (...well, it's never enough, right?) of space left on my D: disk, so I looked in the manual to look for some hint on how to specify the location for the MySQL temporary files.

Well, that was easy enough: http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html tells you that MySQL will use the value of the TMPDIR environment variable as the path, or else a system default. You can also set the directory using the --tmpdir option of the mysqld server executable.

I guess that in my case, MySQL was using the system default, because I do not have a TMPDIR environment variable (I've got a TEMP and a TMP though, neither of which was used). Apparently, C:\WINDOWS\TEMP is the system default on windows.

I decided that it would be best to set the directory using the --tmpdir option, because I can add it as an entry in the my.ini file, which I can reuse whenever I upgrade the server software. So, I went right ahead, and added this entry to my.ini:

tmpdir=D:\MySQL\temp

I restarted the server, and re-ran the query. Almost instantly, it went:

ERROR 1 (HY000): Can't create/write to file 'D:\MySQL   emp\#sql_648_0.MYI' (Errcode: 22)

LOL! It seems that the \t sequence in the path is interpreted as the horizontal tab character (ascii 0x09). I looked it up in the manual in http://dev.mysql.com/doc/refman/5.1/en/mysql-config-wizard-editing.html, but I could not find a note documenting this behaviour. Well, it's always worth trying to use forward slashes:

tmpdir=D:/MySQL/temp

Yes! Now I can run the query. You can probably also escape the \ using \\ but I'm not going to try it right now as I like the forward slashes better.

Server SQL Modes Quickref chart available at the MySQLForge Wiki

Wow!

I just created my first Wiki Page, ever.

I created a new Quickref category in the wiki of the MySQLForge site. There, I created a new article where I put the Server SQL Mode chart I blogged about yesterday in a HTML format.

I like the MySQL reference manual a lot, but especially when I'm looking for some specifics about a topic I already read before, I find that it takes too much time to quickly locate what I need. Knowing what simple Server SQL modes are member of what composite mode is such an example. Knowing the order of arguments for a function is another such problem. I hope the quickref section in the wiki will remedy this, and I hope other people will find it useful too.

Overview of Server SQL Modes

One of the features that distinguishes MySQL 5 from it's predecessors is the increased ability to control how the server interprets SQL and how strict or relaxed it should behave towards errors. This is done by setting the server SQL mode.

Choosing an appropriate Server SQL Mode can be an important tool to ease the task of porting from database products like Oracle or Microsoft Sequal Server. Despite the terrific documentation on the MySQL Reference manual, I really felt the need to have some kind of quick reference around to make effective use of the server sql modes. So, I made up one myself:

Enjoy!

Some MySQL XML utilities

Hi everyone,

I know it's next to nothing, but in case you happen to need it, I've got some XML-related MySQL utilities.

• A documented XML Schema of the mysql command line utility xml format


• A little windows batch file that exports schema-related metadata in the ordinary mysql command line utility xml format

It's all as-is of course but this time, I explicitly included a couple of lines saying that the usage is all under GPL license.

Regarding the information_schema dump, there are some more tools in the works, among which is a xslt stylesheet that generates human readable documentation for a database schema in html format. As soon as I feel these tools are usuable to others, I will make them available too.

Nested repeating groups in MySQL query output

There are a lot of articles around that explain that relations (tables) should be normalized (an article by Mike Hillyer will get you started) before you can store them in a relational database. The complete normalization process consists of two steps:

1. elimination of multi-valued attributes ('at most one value per column')


2. elimination of redundancy ('no duplication of data')

Although these are two distinct activities that solve different problems, the methods used to perform them have a great deal in common. In both cases, some columns from the original table are moved to a new table, and a foreign key relationship is constructed to be able to relate the two tables.

The goal of the entire normalization process is to make the data as maintainable as possible to ensure integrity and validity of the data. Essentially, that goal is achieved by putting each separate 'thing' in it's own table reducing the amount of effort that is needed to add, remove or modify it.

This ususally means that retrieving and presenting data requires more effort than it would've been if the data would've been stored in an unnormalized structure. Because data is spread out across multiple tables, answering even simple questions usually involves accessing multiple tables.

More often than not, data presentation involves recreating (but not storing) a redundant structure with repeating groups. This is especially true for human-readable presentations of data (reports etc), but it may also occur when generating flat files from the database for the purpose of data-exchange (for example, XML-documents).

Although it may require considerable effort to query and retrieve a normalized structure to present data in a redundant format with repeating groups, it is doable. It's certainly a lot easier than storing the data in a redundant format and maintaining integrity and validity in case data is added, modified or removed.

GROUP_CONCAT(): repeating groups through aggregation

Consider the following structure from the Sakila sample database:

(Some of you might recall I used this structure in my previous blog entry to support my plea for a new JOIN USING FOREIGN KEY syntax.)
This is a normalized structure. There are no multi-valued attributes (no column can have more than one value for a particular row). There is no redundancy either: each column is dependant only upon the primary key of it's table.

Suppose you'd want an overview of all the countries and their cities. As described in my previous blog entry, this is easily achieved by JOIN-ing the city table to the country table:

SELECT      country.country
,           city.city
FROM        country
JOIN        city
ON          country.country_id = city.country_id
ORDER BY    country.country
,           city.city
LIMIT       5
;

(The ORDER BY and LIMIT clauses are there only to allow me to present a small, managable list of data - it has no other bearing).
This query yields the following result:

+----------------+--------+
| country        | city   |
+----------------+--------+
| Afghanistan    | Kabul  |
| Algeria        | Batna  |
| Algeria        | Bchar  |
| Algeria        | Skikda |
| American Samoa | Tafuna |
+----------------+--------+
5 rows in set (0.03 sec)

Now, let's rephrase the requirements just a little bit. What if we want an overview of all countries and a list of all it's cities. It resembles the previous overview in that each country is displayed with all of it's cities; it's just the presentation that is different. Here, the output is required to use a repeating group: the result itself will be a list of countries, and each country in the list will contain a list of cities itself. That's pretty much the definition of a repeating group:

+----------------+----------------------+
| country        | cities               |
+----------------+----------------------+
| Afghanistan    | Kabul                |
| Algeria        | Batna, Bchar, Skikda |
| American Samoa | Tafuna               |
+----------------+----------------------+
3 rows in set (0.03 sec)

As it turns out, it is impossible to solve this with a single SELECT expression in Oracle or MS SQL. How convenient that this is not a problem at all in MySQL:

SELECT      country.country
,           GROUP_CONCAT(
                city.city
                ORDER BY city
                SEPARATOR ', '
            ) cities
FROM        country country
JOIN        city    city
ON          country.country_id = city.country_id
GROUP BY    country.country ASC
LIMIT 3

This query has two extra elements compared to the previous one: the GROUP BY clause and the GROUP_CONCAT() function.

GROUP BY accepts a comma-separated list of expressions. GROUP BY modifies the returned resultset: all rows from the original resultset that have a distinct combination of values in the list of GROUP BY expressions, are bundled (grouped) and returned as one single row in the returned resultset.

Columns from the original resultset that do not appear in the GROUP BY list ('detail'-columns, as opposed to 'grouping'- or 'summary'-columns) should not be used directly in the SELECT list. That's because for one group in the final resultset, mutliple distinct values may occur in those columns. Because the expressions in the GROUP BY list are returned a single row, the resultset simply doesn't have room to return all of them.

(Most rdbms-es consider it an error to have a column that does not appear in the GROUP BY list in the SELECT list. By default MySQL will accept such a statement. MySQL even returns the correct value too if the column has a unique value within the group. Otherwise, the returned value is undefined. This particular behaviour can be overidden using the ONLY_FULL_GROUP_BY server SQL mode. In that particular mode, MySQL will treat it as an error to have such columns in the SELECT-list too. See the manual for more info on this subject.)

Although the detail values for each group cannot be accessed in the SELECT list directly, they can be processed by an aggregate function, like MIN, MAX, COUNT, SUM. All these functions operate on a group of objects only to return a single scalar value.

GROUP_CONCAT() is such an aggregate function. The function processes the expression passed to it by concatenating all occurrences within the group together. Apart from the expression value, an ORDER BY clause may be specified to control the order in which the detail expression values are concatenated to each other. Also, a SEPARATOR clause may be passed to specify the string that should be used to separate the multiple entries in the return value.

Adding a level

Ok, we've seen that in MySQL, generating the repeating group in the query result is really very easy.
Now, suppose the requirements for the query change again. This time, we would also like to see the list of customers living in each city. First, let's take a look at the relevant structures in the database schema:

If we would not require the repeating groups in the output, the solution is a quite straightforward extension of the first country/city query:

SELECT      country.country
,           city.city
,           customer.last_name
,           customer.first_name
FROM        country
JOIN        city
ON          country.country_id = city.country_id
JOIN        address
ON          city.city_id = address.city_id
JOIN        customer
ON          address.address_id = customer.address_id
WHERE       country.country = 'United Kingdom'
ORDER BY    country.country
,           city.city
,           customer.last_name
,           customer.first_name
;

(This time, I used a WHERE condition to limit the number of rows. It has no bearing otherwise.)
This is the result:

+----------------+-----------------+-----------+------------+
| country        | city            | last_name | first_name |
+----------------+-----------------+-----------+------------+
| United Kingdom | Bradford        | POWELL    | ANNE       |
| United Kingdom | Dundee          | BURNS     | APRIL      |
| United Kingdom | London          | HOFFMAN   | MATTIE     |
| United Kingdom | London          | VINES     | CECIL      |
| United Kingdom | Southampton     | THORN     | MARSHALL   |
| United Kingdom | Southend-on-Sea | MARTIN    | SANDRA     |
| United Kingdom | Southport       | GRUBER    | ARMANDO    |
| United Kingdom | Stockport       | PAINE     | DAN        |
| United Kingdom | York            | SLEDGE    | GILBERT    |
+----------------+-----------------+-----------+------------+
9 rows in set (0.00 sec)

However, we run into problems when we try to think of an appropriate GROUP BY clause and GROUP_CONCAT() expression to generate the desired repeating groups.

If we GROUP BY the country.country column like we did before, we would get one summary row for the country, exaclty as required. However, what remains is a set of city/customer combinations. Although we could process those with GROUP_CONCAT, that will never give us the desired result. The repeating group generated by GROUP_CONCAT will have an entry for each city/customer combination corresponding to the United Kingdom, and that is not what we want: what we want is a repeating group of cities per country and per city, a repeating group of customers.

So, we need two different repeating groups occurring for two different groupings or levels. This means that somehow, our query needs to have two corresponding GROUP_CONCAT()'s as well as two GROUP BY's. To achieve that, we need to have a '..query inside a query..' that we can equip with it's own GROUP BY clause so it can generate it's own repeating group.

A 'query inside a query' is called a subquery. Inside a SELECT expression, a subquery can appear in three different places: in the WHERE-clause, in the FROM-clause, or in SELECT-list. Let's stick as close as we can to our previous attempt, JOIN-ing all relevant tables, and write a subquery in the FROM clause.

Well, it's not really hard to see what parts we must place in the subquery. We had the repeating group for the city table in place, all we did since then is add the address and customer tables. These two tables really are all that is required to generate the repeating group of customers per city:

SELECT      a.city_id
,           GROUP_CONCAT(
                CONCAT(
                    c.first_name
                ,   ' '
                ,   c.last_name
                )
                ORDER BY c.last_name
                SEPARATOR ', '
            )   customers_per_city
FROM        customer c
JOIN        address a
ON          c.address_id = a.address_id
WHERE       a.city_id = 312
GROUP BY    a.city_id

(312 is the city_id of London in the United Kingdom)
You can see that this query indeed generates the repeating group of customers for a particular value of city_id:

+---------+-----------------------------+
| city_id | customers_per_city          |
+---------+-----------------------------+
|     312 | MATTIE HOFFMAN, CECIL VINES |
+---------+-----------------------------+

In fact, the repeating group corresponds to the 3rd and 4th entry in the result of the previous query: the customers living in London.

Now, we combine this with the previous query, substituting the directly joined address and customer tables and their JOIN condition with this query:

SELECT      concat(
                country.country
            ,   ': '
            ,   GROUP_CONCAT(
                    concat(
                        '\n\t', city.city, ':'
                    ,   '\n\t\t', customer_per_city.customer_per_city
                    )
                    separator ''
                )
            ) customers_per_city
FROM        country
JOIN        city
ON          country.country_id = city.country_id 
JOIN       (
            SELECT      a.city_id
            ,           GROUP_CONCAT(
                            CONCAT(
                                c.first_name
                            ,   ' ' 
                            ,   c.last_name
                            )
                            ORDER BY c.last_name
                            SEPARATOR ', '
                        )   customer_per_city
            FROM        customer c
            JOIN        address a
            ON          c.address_id = a.address_id
            GROUP BY    a.city_id
            ) customer_per_city
ON          city.city_id = customer_per_city.city_id
WHERE       country.country = 'United Kingdom'
GROUP BY    country.country ASC

(In this query, some adjustments are made to the separators. Specificly, some newlines and indenting were added to make the result more readable. It's still a repeating group but this time, a new-line is output between cities instead of the semi colon used in previous examples.)
This is the result:

United Kingdom:
      Bradford:
              ANNE POWELL
      Dundee:
              APRIL BURNS
      London:
              MATTIE HOFFMAN, CECIL VINES
      Southampton:
              MARSHALL THORN
      Southend-on-Sea:
              SANDRA MARTIN
      Southport:
              ARMANDO GRUBER
      Stockport:
              DAN PAINE
      York:
              GILBERT SLEDGE 

Summary

The MySQL aggregate function GROUP_CONCAT() generates a repeating group for the string expression passed as argument.

The items that appear in a repeating group can themselves be repeating groups. Nesting of repeated groups is achieved by writing distinct queries for each distinct repeating group. These distinct queries are then joined (as subqueries) to relate the repeating groups to each other.

Intelligent SQL JOIN syntax?

The SQL standard specifies two different intelligent variants of the JOIN syntax: the natural join and the named columns join:

<natural join> ::=
    <table reference> NATURAL [ <join type> ] JOIN <table factor>

<named columns join> ::= 
    USING <left paren> <join column list> <right paren>

(from ISO/IEC 9075-2:2003; 7.7 <joined table>)

I call these two forms of the join syntax intelligent because they imply a non-trivial join condition. This means that such a join operation will relate the records from the two tables expressions based on some criterion without requiring that the criterion is specified in each and every detail. This is unlike the other variants of the JOIN syntax: to be meaningful, these require that the join condition in the form of a boolean expression is associated with the JOIN operation using a ON clause.

Relationships, Foreign Keys and Joins

Consider this structure taken from the MySQL sakila sample database:



Here, we have table country that has a 1 to many relationship with city. The relationship conveys the fact that a city is situated inside a particular country (I won't be discussing the validity of that here). The concept of a relationship in itself has nothing todo with databases or computers or whatever. It merely has to do with they way we, as humans, perceive the world around us. 'Relationship' is a conceptual term.

In a relational database, relationships are implemented using foreign keys. Here in the example, the city table (the 'many' end of the relationship) has a country_id column. For a particular record from the city table this column stores one of the values found in the country_id column of the country table (the 'one' end of the relationship).

The country_id column in the country table stores only unique values: this is enforced using a primary key constraint. A foreign key constraint on the city table ensures that the country_id column in the city table may only contain values that are in fact present in the country_id column of the country table.

An arbitrary record from the city table refers to exactly one particular record in the country table by storing a value from the country table's key, coutry_id. That's why it is called a foreign key: the city stores values that act as key values (identifying values) in another table, in this case the country table.

So, a foreign key is an implementation of a relationship in a relational database: foreign key is an implementation term. (In fact, it is they most common way to implement a relationship.) Usually, foreign keys are actively enforced by declaring a foreign key constraint. A foreign key constraint declares the exact column mapping involved in the referenced, and it makes the database management system prevent any data from being entered, removed or modified that might violate any of the references.

The SQL code to create this structure is shown here:

CREATE TABLE country (
    country_id 
        SMALLINT UNSIGNED 
        NOT NULL 
        AUTO_INCREMENT
,   country 
        VARCHAR(50) 
        NOT NULL
,   last_update 
        TIMESTAMP 
        NOT NULL 
        DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP
,   PRIMARY KEY  (country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE city (
    city_id  
        SMALLINT UNSIGNED 
        NOT NULL 
        AUTO_INCREMENT
,   city 
        VARCHAR(50) 
        NOT NULL
,   country_id 
        SMALLINT UNSIGNED 
        NOT NULL
,   last_update 
        TIMESTAMP   
        NOT NULL 
        DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP
,   PRIMARY KEY  (city_id)
,   KEY idx_fk_country_id (country_id)
,   CONSTRAINT `city_resides_in_country`
        FOREIGN KEY (country_id)
        REFERENCES country (country_id) 
        ON DELETE RESTRICT 
        ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

Joins

Now, suppose we want to have a list of all the city names together with the name of the country in which it is situated. Such a list can be conveniently produced using a JOIN operation. In a Join operation, records from different tables are combined to create new (virtual) records. So, joining is a (runtime) processing term. Usuallly, the join operation combines records that have some sort of correspondence; that is, records that maintain some kind of relationship between one another.

To understand the result of the JOIN operation we can imagine that the database server performs the following actions (Caution: the following 'algorithm' does not illustrate what a database server actually does to obtain the result of the JOIN operation - it only illustrates what the result will look like):

1. For each record from the city table, all records from the country table are retrieved. A new record is constructed consisting of all the fields from the 'city' record and all the fields from the 'country' record, thus yielding all combinations of city and country records: the cartesian product. These newly constructed records are temporarily stored in a virtual table.


2. A filter is applied to the virtual table, only retaining those records that have equal values for both country_id fields


3. For each record in the filtered table, create a new record consisting only of the city and country fields (projection), and return those records as the final resultset

It is in fact possible to write a SELECT expression that corresponds quite literally to this conceptual algorithm:

--
-- step 3: column projection
--
SELECT      city.city        
,           country.country  
--
-- step 1: construct a cartesian product
--
FROM        city
CROSS JOIN  country
--
-- step 2: retain only the corresponding records
--
WHERE       city.country_id = country.country_id

Instead of the CROSS JOIN operator, one can also write a comma (,). However, because the comma denotes a separator when used in the SELECT list, it's better to disambiguate using the keywords.

Now, the WHERE clause contains the condition that forms a criterion for filtering the records. Our condition is made up of only one element: the column comparison that specifies that only corresponding records should be retained. However, the WHERE is a general container for such conditions: we may write any kind of condition there. And, we should if we need to apply extra criteria. Suppose we want to have the list only for those countries of which the name starts with a capital A. Then, our WHERE clause could be extended to look like this:

WHERE       city.country_id = country.country_id -- join condition
AND         country.country like 'A%'            -- just an extra criterion

So, both the join condition - the condition that is supposed to retain the related records - and our extra criterion appear mixed all in one big WHERE condition. This style of joining is called theta join.

Another way of putting it is to say that this is an unqualified join: the join condition does not seem to be associated with the join operation itself: it just happens to be a criterion that requires columns from both the tables in join operation to share equal values.

Qualified Joins

It's generally considered to be better to avoid theta join style in favour of ansi join style. The ansi join style requires the join condition to be directly associated with the join operation: we say that the join is qualified with the condition.

The join condition is separated from the join operation by a single ON keyword only:

--
-- step 3: column projection
--
SELECT      city.city        
,           country.country  
--
-- step 1: combine only corresponding records
--
FROM        city
JOIN        country
ON          city.country_id = country.country_id
--
-- step 2: apply any criteria
--
WHERE       country.country like 'A%'

We can modify the 'conceptual algorithm' presented earlier to match this more closely. (Please bear in mind that this 'algorithm' still has nothing to do with how a server might actually implement it. Also, note that this has no bearing at all on our understanding of the final resultset: the final resultset here is indistinguishable from the one we had earlier):

1. For each record from the city table, loop through all records from the country table. A new record is constructed consisting of all the fields from the 'city' record and all the fields from the 'country' record, but oly if the values of the country_id column of both records are exactly equal. These newly constructed records are temporarily stored in a virtual table.


2. A filter is applied to the virtual table, only retaining those records that match the WHERE clause


3. For each record in the filtered table, create a new record consisting only of the city and country fields (projection), and return those records as the final resultset

From the developer's point of view, the advantage of the ansi join syntax is that all the elements that have to do with relating the two tables can be written on consecutive lines. This makes it much easier to maintain the code if the database structure changes, or to remove the join.

Intelligent Join constructs

There are two particular forms of qualified join syntax that do not require the precise join condition to be specified. Rather, this syntax relies on some kind of rule that implies the precise join condition. The actual join condition itself is derived from the rule, and never entered literally.

Natural Join

The natural join implies a join condition that requires that equally named pairs of columns belonging to the table expressions appearing on the left and right side of the JOIN operator have equal values. In our example, two such pairs are present: both tables have a column named country_id and a column named last_update. Therefore, a natural join between the country and the city table implies a join condition that requires that the columns from the country_id and the last_update pairs have equal values. So, this:

SELECT          city.city        
,               country.country  
FROM            city
NATURAL JOIN    country

is equivalent to

SELECT  city.city        
,       country.country  
FROM    city
JOIN    country
ON      city.country_id  = country.country_id
AND     city.last_update = country.last_update

This example immediately illustrates an important limitation of the NATURAL JOIN construct. We now that the city table referes to the country table by means of the country_id foreign key. So, the implied join condition is 'half-right' in requiring equality for the values in the country_id columns. However, because both tables have a last_update column, equality is required for these columns too. This is of course total non-sense: the last_update column is a TIMESTAMP column, and it will contain the current date and time of the moment the record was last updated (or inserted). Any equality found for these columns will be a matter of coincidence, so altough the columns can be related to each other, this does not constitute a relationship.

One could of course argue that this natural join does not check out because the table definition is flawed. If the last_update columns would've been given a name that is more specific to the table to which they belong, the problem would not've occurred, and we could've used the natural join. However, I feel that is not a very good argument

If we were to go along with this, we will be forced to rename all columns systematically in order to avoid accidentally occurring identical column names. Although we could limit ourselves to rename only the columns that are uninentionally included in the join conditions, this would introduce an amount of inconsistency in the identifier set, which is undesirable because it makes it harder to code against the schema. Also, the columns that we don't rename now could become a problem later on when we add new tables and columns to the schema, hampering further development. So we really need some sort of systematic approach towards picking identifiers. For example, all columns that 'truly' belong to a table could be given a prefix that is derived from the table name. Although this seems doable, it is rather impractical. It would yield long column names - for some rdbms-es, a column name can easily become too long (Oracle has a maximum of 30 characters, MaxDB has a maximum of 32).

Named columns join

The other 'intelligent' join construct is the named columns join. Like the natural join, the named columns join implies a join condition based on requiring equal values for pairs of identically named columns. The difference with the natural join is that the column pairs that should be implied in the join condition must be specified explicitly with a USING clause.

Because the column pairs must be explicitly specified, we can avoid unrelated column pairs that happen to have identical names to be implied in the join condition. This solves the problem with the last_update column we just discussed for the natural join between the city and the country table:

SELECT  city.city        
,       country.country  
FROM    city
JOIN    country 
USING(country_id)

This is equivalent to:

SELECT  city.city        
,       country.country  
FROM    city
JOIN    country
ON      city.country_id  = country.country_id

Athough the named columns join is thus slightly more useful than the natural join, it still poses a problem. No matter how we name our columns, we can never find a satisfactory solution for those cases where a particular table has more than one foreign key referring to one key in another table. In that case, each foreign key will need it's own set of columns, and only one of these can choose column names identical to the names of the referenced columns. The natural join as well as the named columns join can be used for joining along one relationship only. Joining along the other relationships always involves writing an explicit join condition.

All in all, if think that bioth the natural join and the named columns join constructs are not very useful. I never use them, because they would introduce inconsistency in the code. I feel that similar tasks should be solved in similar ways to avoid confusion, and because you will potentially need to write explicit join conditions anyway, I prefer to do so right away, even if a particular case would allow for a natural or named columns join.

What about a foreign key join?

Nevertheless, I think that the concept of an implied join condition is really elegant and charming if it could be used generically. Both the natural join and the named columns join cannot be used generically because they infer the join condition based on equality of the column names. Clearly, equality of column names just isn't the right sort of thing to use to infer join conditions.

In a lot of cases, we want to join along the columns of a foreign key, regardless of the actual column names. If we enforce the foreign key with a constraint, the rdbms could use that information to infer a join condition. So I think it's really amazing to discover that neither the SQL Standard nor any of the more renowned rdbms-es support a syntax that actually takes advantage of this.

I do know a development environment, USoft, that actually supports such a construct. I don't know if they still support it, but when I used it, you could write the example join something like this:

SELECT  city.city        
,       country.country  
FROM    city RELATE country WITH city_resides_in_country

So, the foreign key name city_resides_in_country is used to specify the join condition. A variant that resembles the usual named columns join syntax could look like this:

SELECT  city.city        
,       country.country  
FROM    city JOIN country USING city_resides_in_country

The nice thing about this syntax is that it solves all the problems described with the natural join and the ordinary named columns join. As a bonus, our code has become resilient to structural changes of the keys: If we were to add a column to the foreign key in the city table and corresponding primary key in the country table, our joins would not need to be modified because they never refer to a column name.

Anyone to point me in the direction of a rdbms that supports this kind of syntax?