samurdhilbk samurdhilbk - 2 months ago 13
MySQL Question

Selecting one to one relationship

I have two tables ACTIVITIES and PERFORMERS defined in the following way.

CREATE TABLE Activities (
ActivityID int NOT NULL,
ActivityName char(30) NOT NULL default 'charity',
PRIMARY KEY (ActivityID)
);



CREATE TABLE Performers (
PerformerID int NOT NULL default '0',
PerformerName char(20) NOT NULL default '',
Street char(20) NOT NULL default '',
City char(15) NOT NULL default '',
State char(2) NOT NULL default '',
Zip int NOT NULL default '0',
ActivityID int default '0',
PRIMARY KEY (PerformerID),
FOREIGN KEY(ActivityID) REFERENCES Activities(ActivityID) ON DELETE NO ACTION ON UPDATE NO ACTION);


Each performer has a specific activity. How do I select the performers name along with the activity that they are doing?

I tried

SELECT performername,activityname FROM PERFORMERS NATURAL JOIN ACTIVITIES;


This seems to be correct. Will it always be?

Answer

Don't use NATURAL JOIN. It is a bug waiting to happen. The recommendation for an INNER JOIN is good. Here are two methods:

select p.performername, a.activityname 
from Performers p inner join
     Activities a
     on a.ActivityID = p.ActivityID; 

Or:

select p.performername, a.activityname 
from Performers p inner join
     Activities a
     using (ActivityID);

What is the problem with NATURAL JOIN? Basically, the issue is that it identifies common keys simply by their names. It does not recognize the declared foreign key relationship between the two tables. It only uses columns with the same name.

This becomes worse, because you might have additional columns that happen to have the same name. For instance, you might have called called name in both tables. This would automatically be used for the NATURAL JOIN. Because almost all tables that I create by CreatedBy and CreatedAt columns, NATURAL JOIN simply would not work on my tables.

I also think that it makes it much harder to debug code when the join keys are not explicitly specified -- as they are with either USING or ON.