0

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);
slavoo
  • 5,798
  • 64
  • 37
  • 39
SteamedCow
  • 197
  • 1
  • 13

2 Answers2

0

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.

jpw
  • 44,361
  • 6
  • 66
  • 86
  • Ah that makes sense. I thought it would be using the foreign key to that table only. Thanks it works fine with your suggestion. – SteamedCow Mar 07 '16 at 19:03
0

The natural join probably use namein the join which leads to 0 results unless an artist has an eponymous album!

Anyhow you should avoid natural join because, as you can see, they are less obvious. Stick to the normal join version.

Community
  • 1
  • 1
ForguesR
  • 3,558
  • 1
  • 17
  • 39