[dmfay]

You may be interested in the USING clause SQL-92 added for foreign keys where the referencing columns have the same name as the referenced columns: JOIN b USING (a_id). The only major RDBMS that doesn't support it is SQL Server afaik.

[JoelJacobson]

Yes, I know about USING, and you probably know about the one big issue it has; if you add a column name with the same name to some table included, then you get an error due to the ambiguity.

Example:

  SELECT
    language.name,
    COUNT(*)
  FROM rental
  JOIN customer USING (customer_id)
  JOIN address USING (address_id)
  JOIN city USING (city_id)
  JOIN country USING (country_id)
  JOIN inventory USING (inventory_id)
  JOIN film USING (film_id)
  JOIN language USING (language_id)
  WHERE country.country = 'Sweden'
  GROUP BY language.name

Works fine, but if we do

  ALTER TABLE rental ADD address_id integer;

Then we get

  ERROR:  common column name "address_id" appears more than once in left table

The foreign key based join approach doesn't suffer from this problem, since there which columns to join on is explicit and stable.