difference between these sql joins (same results, same exec plans)

Question

Two tables. First table holds every person, another table is used to specify which person is a kid as part of a family:

create table persons(
    personID bigint primary key auto_increment,
    firstName varchar(30) not null,
    lastName varchar(30) not null
);

create table kids(
    personID bigint not null,
    familyID bigint not null,
    grade bigint,

    constraint kidReference foreign key (personID) references persons (personID),
    constraint familyReference foreign key (familyID) references families (familyID)
);

I want to select all details for each person that is a kid. Using the following queries, i get the same result, the same identical execution plan:

(1) comma separated join:

select persons.personID, persons.firstName, persons.lastName, kids.familyID, kids.grade from persons, kids where persons.personID = kids.personID;

(2) natural join:

select * from persons natural join kids;

(3) named join:

select * from persons join kids using (personID);

(4) condition join:

select * from persons inner join kids on persons.personID = kids.personID;

Is it a matter of mere readability? And when writing queries should i aim for just for -what-it-works-best-?

Answer 1

In your case, each version of the query will result in the same basic query.

The comma syntax will try to find a join based on the WHERE clause. where persons.personID = kids.personID becomes inner join kids on persons.personID = kids.personID. Note that = causes inner join and *= causes left join.

The natural join looks to join on columns which have the same name, i.e. PersonID. You don't need to specify a specific name as natural join will find common names for you. The default is an inner join.

The named join using (PersonID) is shorthand for joining both tables on column name PersonID which they both share. JOIN on its own is interpreted as an inner join.

And the final version is spelling out exactly what you want, with no need for interpretation.

My preference is for the final version because it has no surprises.

Having said that, JOIN using (personID) is also unambiguous - it's just shorthand for the final version.