Is using Natural Join or Implicit column names not a good practice when writing SQL in a programming language?

Question

When we use Natural Join, we are joining the tables when both table have the same column names. But what if we write it in PHP and then the DBA add some more fields to both tables, then the Natural Join can break?

The same goes for Insert, if we do a

insert into gifts values (NULL, "chocolate", "choco.jpg", now());

then it will break the code as well as contaminating the table when the DBA adds some fields to the table (example as column 2 or 3). So it is always best to spell out the column names when the SQL statements are written inside a programming language and stored in a file in a big project.

Answer 1

yes it is always best practice to state your fields explicitly. Otherwise when you modify your schema, your code will break.

Answer 2

Yes it is always a good idea to explicitly name the columns you are concerned with. As you point out, this prevents the query breaking if someone adds a column to the table.

The same applies for prefering to list columns in a SELECT instead of writing SELECT *. See this related question.

Answer 3

Natural joins are not a good idea wherever you write them. They hide the meaning that more explicit code would expose and may introduce subtle bugs if column names change.