MySQL Bugs: #61069: Key Join

Bug #61069	Key Join
Submitted:	5 May 2011 11:01	Modified:	10 May 2011 9:19
Reporter:	Marcus Hambraeus	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	key join

Description:
It would be great to have the ability to join on foreign-key-relations between tables. Since foreign keys represents a relation between tables, it seems logical to be able to join them based on that specified relation.

How to repeat:
Consider the following setup:

+--------+
| people |
+--------+
| id     |
| name   |
+--------+

+-----------+
| employees |
+-----------+
| id        |
| person_id | FOREIGN KEY people (id)
+-----------+

As it stands now, I'd have to do:

SELECT people.name FROM employees INNER JOIN people ON employees.people_id = people.id;

With the proposed Key Join, it could be done much easier:

SELECT people.name FROM employees INNER KEY JOIN people;

Suggested fix:
Implement Key Join in InnoDB and other engines supporting Foreign Keys.

Small fix to How to repeat: The INNER-statement in INNER KEY JOIN could be omitted, since the FK-relation would be enough to figure out the relation type.

Why not to give the same names to related columns and use (SQL standard) NATURAL JOIN instead? See http://dev.mysql.com/doc/refman/5.5/en/join.html.

Lots of databases that I've seen (including my own, obviously :P ) use the field name "id" for their auto-incremented primary key-field, and then [table name]_id when referencing it from another field. Natural joins wouldn't work in those scenarios, since it requires the fields to be named the same. Sure, it could be solved if people used [table name]_id instead of id in the main table too, but a key join would greatly simplify database query writing, without forcing people to work in a specific way. Also, natural joins can behave in "strange" ways since it tries to invent a relation based on field names, while a key join would use an already specified relation. Also, since we've gone though the trouble of specifying relations between tables, why not use that to its fullest?