Insert Set or Insert Values

Question

INSERT INTO `table` VALUES ('val1','val2','val3')

INSERT INTO `table` SET a='val1',b='val2',c='val3'

Both are used for the same purpose.but which i should use? in which case? and why?

Answer 1

They are identical, but the first one is standard SQL. As the SQL parser treats them as synonyms there is no difference in performance, usage or anything. As always, prefer using the standardized form, the first syntax in this case, for portability and compatibility.

Refer to the SQL-92 standard syntax.

Answer 2

As far as I can tell, both syntaxes are equivalent.

The first is SQL standard, the second is MySQL's extension.

So they should be exactly equivalent performance wise.

http://dev.mysql.com/doc/refman/5.6/en/insert.html says:

INSERT inserts new rows into an existing table. 
The INSERT ... VALUES and INSERT ... SET forms of the statement 
insert rows based on explicitly specified values. 
The INSERT ... SELECT form inserts rows selected from another 
table or tables.

Answer 3

Fields are not always mandatory in a table. Sometime you just want to set some fields not all of them. Then you will use the second form.

The first form is usually auto generated with a script like:

$query = "INSERT INTO `table` VALUES ("
for(@values) {
   $query .= "'$_'";
}
$query .= ")";

mysql_query($query);

But the correct way would be to use a more usual form which is:

@fields = (qw/name firstname address/);
@values = (qw/Wall Larry Paris/);

$query = "INSERT INTO `table` (";
$query .= join(',', @fields);    
$query .= ") VALUES (";
$query .= join(',', @values);
$query .= ")";



mysql_query($query);