Why is positional queries bad?

Question

I am reading CJ Date SQL and relational theory: how to write accurate SQL code , and it makes positional queries bad - for example, this is INSERT :

 INSERT INTO t VALUES (1, 2, 3) 

Instead, you should use attribute-based queries, for example:

 INSERT INTO t (one, two, three) VALUES (1, 2, 3) 

Now I understand that the first query does not match the relational model, because tuples (rows) are unordered sets of attributes (columns). I am having trouble understanding where the harm is in the first request. Can someone explain this to me?

Answer 1

The first query breaks almost during a table schema change. The second query contains any schema change that leaves its columns intact and does not add default columns.

People who execute SELECT * queries and then rely on positional notation to retrieve the values ​​they care about are version control software for the same reason.

Answer 2

While the order of the columns is defined in the schema, it is usually not considered important because it is not fundamentally important.

In addition, this means that anyone reading the first version should consult the chart to see what the meanings mean. Admittedly, this is similar to using positional arguments in most programming languages, but in this sense, SQL is slightly different from this - I would much better understand the second version (assuming the column names are reasonable).

Answer 3

I do not need theoretical concepts in this regard (since in practice the table has a certain order of columns). The main reason I would prefer the second is to add an added layer of abstraction . You can change columns in a table without loading your queries.

Answer 4

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

The first query relies on a table containing only three fields and in that order. Any change to the table will break the query.

The second query is based only on the fact that the table contains three fields, and the order of the fields does not matter. You can change the order of the fields in the table without breaking the query, and you can even add fields if they allow null values ​​or have a default value.

Despite the fact that you do not change the order of the tables too often, adding more fields to the table is quite common.

In addition, the second request is more readable. You yourself can tell from the query itself what the values ​​placed in the record mean.

Answer 5

Something that has not yet been mentioned is that you will often have a surrogate key like your PC, with auto_increment (or something similar) to assign a value. With the first you should indicate something there: - but what value can you indicate if it will not be used? NULL may be an option, but this is not very suitable when considering whether PK will be set to NOT NULL .

But besides that, the whole “tied to a specific pattern” is a much more important reason, IMO.

Answer 6

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

• Your queries are resistant to changes in the ordering of columns, so servicing requires less effort.
• The column organizes the cards better as people think, so it is more readable. It is clear that the column should be considered as the column "Name", and not the second.

Answer 7

I prefer to use syntax like UPDATE:

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

It is much easier to read and maintain than both examples.

Answer 8

In the long run, if you add another column to your table, your INSERT will not work unless you explicitly specify a list of columns. If someone reorders the columns, your INSERT may silently remove the insertion of values ​​into the wrong columns.

Answer 9

I'm going to add one more thing, the second query is less error prone initially even before the tables are modified. Why am I saying that? Beware of the seocnd form, which you can (and should, when you write the query) visually check to see if the columns in the insert table and the data in the value or select clause clause are actually in the correct order. Otherwise, you can accidentally put a social security number in the Honoraria field and tell your speakers your SSN instead of what they should do for speech (the example is not chosen randomly, except that we caught it before it actually happened visual check!).