Why are positional queries bad?

Answer 1

The first query breaks pretty much any time the table schema changes. The second query accomodates any schema change that leaves its columns intact.

People who do SELECT * queries and then rely on positional notation for extracting the values they're concerned about are software maintenance supervillains for the same reason.

Answer 2

I don't really care about theoretical concepts in this regard (as in practice, a table does have a defined column order). The primary reason I would prefer the second one to the first is an added layer of abstraction. You can modify columns in a table without screwing up your queries.

Answer 3

While the order of columns is defined in the schema, it should generally not be regarded as important because it's not conceptually important.

Also, it means that anyone reading the first version has to consult the schema to find out what the values are meant to mean. Admittedly this is just like using positional arguments in most programming languages, but somehow SQL feels slightly different in this respect - I'd certainly understand the second version much more easily (assuming the column names are sensible).

Answer 4

You should try to make your SQL queries depend on the exact layout of the table as little as possible.

The first query relies on the table only having three fields, and in that exact order. Any change at all to the table will break the query.

The second query only relies on there being those three felds in the table, and the order of the fields is irrelevant. You can change the order of fields in the table without breaking the query, and you can even add fields as long as they allow null values or has a default value.

Although you don't rearrange the table layout very often, adding more fields to a table is quite common.

Also, the second query is more readable. You can tell from the query itself what the values put in the record means.

Answer 5

SQL gives you syntax for specifying the name of the column for both INSERT and SELECT statements. You should use this because:

• Your queries are stable to changes in the column ordering, so that maintenance takes less work.
• The column ordering maps better to how people think, so it's more readable. It's more clear to think of a column as the "Name" column rather than the 2nd column.

Answer 6

I prefer to use the UPDATE-like syntax:

INSERT t SET one = 1 , two = 2 , three = 3

Which is far easier to read and maintain than both the examples.

Answer 7

Long term, if you add one more column to your table, your INSERT will not work unless you explicitly specify list of columns. If someone changes the order of columns, your INSERT may silently succeed inserting values into wrong columns.