SteamedCow SteamedCow - 1 month ago 7
MySQL Question

MySQL foreign key/natural join

Scenario:
I've been trying to solve a problem where I wanted to make a natural join between two tables Artists and Albums, where Artists cointains a column ar_id that is the primary key and Albums contains a column also named ar_id where the foreign key in the Album table is ar_id and refers to Artists ar_id. There's a 1 to many relationship between the tables (one artist can have multiple albums).

Problem:
When I want to make a NATURAL JOIN between the two tables Artists and Albums it returns 0 rows, but when I use are normal JOIN with WHERE function it returns 18 rows as it should. So I guess the problem is the foreign key setup but I can't find the problem

The select code with natural join (doesn't work):

SELECT * FROM
Artists NATURAL JOIN Albums;


The select code with normal join where (does work):

SELECT * FROM
Artists JOIN Albums
WHERE CDReg.Artists.ar_id = CDReg.Albums.ar_id;


DLL for the two tables

CREATE TABLE Artists (
ar_id int PRIMARY KEY,
ge_id int(11) DEFAULT NULL,
country_code varchar(2) DEFAULT NULL,
name varchar(45) NOT NULL,
start_year year(4) DEFAULT NULL,
end_year year(4) DEFAULT NULL,
FOREIGN KEY (ge_id) REFERENCES Genres (ge_id),
FOREIGN KEY (country_code) REFERENCES Countries (code)
);

-- --------------------------------------------------------

CREATE TABLE Albums (
al_id int PRIMARY KEY,
ar_id int,
name varchar(45) NOT NULL,
release_year year(4) DEFAULT NULL,
FOREIGN KEY (ar_id) REFERENCES Artists(ar_id)
);


Thanks for any help in advance :)
[SOLVED]:
I thought that natural join used the foreign key to join the tables but instead it uses all matching column names including the columns "
name
" (exsists in both tables), since there isn't any artists with an eponymous album title in the database the result was 0 rows. The solution was to use

SELECT * FROM
Artists JOIN albums USING(ar_id);

jpw jpw
Answer

From the documentation:

The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.

Both tables have a column name which is included in the natural join, making it fail for all combinations except for those where the artist and album names are the same (which I guess could happen).

You could use a join FROM Artists JOIN albums USING (ar_id) instead.