As others have pointed out, it is always good to be explicit when writing SQL statements. This means writing

INSERT into the_table(foo, bar, baz) values(...)
[download]

INSERT into the_table values(...)
[download]

SELECT foo, bar, baz FROM the_table WHERE ...
[download]

SELECT * FROM the_table WHERE ...
[download]

Michael

Thanks for the explanation mpeppler, that was helpful, and points well taken. And hardburn was right, no code samples really necessary, but your examples didn't hurt .

—Brad
"A little yeast leavens the whole dough."

Don't see what code samples could be provided besides what is already above. It's just as mrpeppler said: INSERT INTO foo SET . . . works on MySQL, but may not work anywhere else (it doesn't on PostgreSQL, for instance). Which is unfortunate, IMHO, because the SET syntax makes it easier to figure out which parameters are being set to what in a large statement.

While it may be nice syntactic sugar, it isn't portable SQL. And, I don't see how the following is difficult to work with ...

INSERT INTO foo_table
       ( foo, bar, baz, foo1, bar1, baz1 )
VALUES ( ?,   ?,   ?,   ?,    ?,    ?    )
[download]

It's all about how you format your SQL. Personally, I format my SELECT statements as such:

SELECT xx.foo
      ,xx.bar
      ,yy.baz
      ,COUNT(*) AS count
  FROM foo_table xx
      ,bar_table yy
 WHERE xx.abcd      = yy.abcd
   AND xx.asdfghjkl = yy.asdfghjkl
 GROUP BY xx.foo
         ,yy.bar
         ,yy.baz
[download]

Notice where the commas and where the equals signs are. Also, note how I line up the whitespace after the upper-cased keyword. The more you can say in your formatting, the better.

------
We are the carpenters and bricklayers of the Information Age.

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

Personally, I format my SELECT statements as such:

I use the same format - and when you have a large select with lots of ugly stuff in the WHERE clause (or in the SELECT clause for that matter) it really helps to make it readable.

Michael

I was mostly talking about a SET being used in an interactive session. In a static SQL statement set into a larger program, it's generally not very useful to know that a given column lines up with some question mark. As long as there is one question mark per column (minus any statically-defined columns), it doesn't matter. In any case, my programs usually generate placeholders on the fly (using code similar to what I wrote in another node in this thread), so there is little point in formatting the SQL at all.

In an interactive session, I'm usually doing a lot of quick-and-dirty work with statements that will be thrown away once the session is over, so I don't take the time to indent the code. SET would be much nicer here.

Update: One other thing--formatting the (cols . . ) VALUES (? . . . ) statement like that will break down for very long lines.

----
I wanted to explore how Perl's closures can be manipulated, and ended up creating an object system by accident.
-- Schemer

: () { :|:& };:

Note: All code is untested, unless otherwise stated