SQL coding convention/format/style
-
I have a question about SQL coding style/convention/format. I have noticed some people put the commas in the front of a line rather than the end such as...
SELECT
FirstName
, LastName
, City
FROM TableName
Why is this done? What is the advantage?
TIA.
-
Why is this done Personal preference.
What is the advantage None from my point of view.
SQL does not care
Far away is close at hand in the images of elsewhere.
Anon. -
I used to put my commas at the end of the line, but there are enough benefits for me, however small, that it was worth it for me to force myself to switch to the front.
A few benefits:
Easy "block" manipulation. For example, say you forgot to put a table alias in front of your column names in a SELECT statement. Block highlight the commas and replace with comma and the most commonly used table alias. Then manually change the rest. While there are other ways of accomplishing the same thing (some editors allow a block select with zero width, for instance), this works universally in any any editor with block mode.
If you decide to alias your columns, you don't have to go to the end of each line, then backspace, then space, then type "AS <alias" (which is what you have to do if commas are at the end). With commas in front, you have eliminated the need for the backspace. Minor improvement, but that change eliminates many typos, where you forgot to backspace, etc.
It's just plain easier to see if you're missing a comma, which as we all know is a bane of SQL developers, since the error message that results is not always what you'd expect, and sometimes you might not get an error at all (in some cases the parser would see the next column as an alias when you forget the comma).
All in all, a minor thing, but if you write a ton of code, once you switch, you're not likely to go back.
-
In addition to David's comments I like the style because it is easy to comment lines of code by adding the "--" comment at the beginning of a line. In most editors you can highlight several lines and have the editor add the comment tag, if you comment out the last field in the select list it still works if the commas are at the beginning of each subsequent line. If they are at the end of the line then the last line you didn't comment out will have an extra comma.
It's the little things in life that make it worth living
-
Good tips David...I like most started out putting the commas at the end of the column names but then switched to putting them in front for the reason stated by JLK. Later I realized that the commenting out and extra comma @ the end causing errors during development wasn't really an advantage because you get the same thing if you were to comment out the first field. So tomato tamato...
One thing I saw someone do that I have picked up is when having a huge list of fields being returned create columns in the select. This of course isn't always possible if doing any kind of massaging on the fields but is nice if you are just returning the fields themselves. Like so:
select
col1, col2, col3,
col4, col5, col6,
col7, col8, col9,
col10, col11, col12
from
...
One formatting/style point that I have debated with some colleagues many times is the formatting for join conditions. Specifically which table is referenced in the join condition first. Should it be the table being joined to or the table being joined from? Here is how I would format it...
select
*
from
table1 a
inner join table2 b on
b.akey = a.key
Cheers,
Ben
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
Ben,
On your table joining formatting, I find that I do it just the opposite. Mine usually look like
SELECT
*
FROM
TableA
INNER JOIN TableB ON TableA.Key = TableB.Key
I like doing it this way, so that if my join happens to be a left join, the table that will have all rows in the result set will actually be on the left. Just my quirky thing.
-
Yes that is the same reason you will see folks place AND and OR at the start of a line instead of the end of the prior line. So much faster to comment out items. On really large quesries I may even place the AND and OR on their own line when I have a lot of conditions so I can comment items out here and there.
-
Shawn...
I believe that is incorrect. The join type (inner, left, right) determines from which table all the records being retrieved from not which table is specified first in the condition.
Correct me if I'm wrong but:
==========
select
*
from
table1 a
left join table2 b on a.id = b.id
==========
would return the same results as
==========
select
*
from
table1 a
left join table2 b on b.id = a.id
==========
so everything from 'table1' is going to be retrieved in both cases since the left join is on 'table2'...
Cheers,Ben Sullins
bensullins.com
Beer is my primary key... -
Heh. I guess I wasn't too clear, I was just sayign that I like the format to have the "table1" from the left join on the left side of the JOIN statement, not that the order matters for which becomes the LEFT table.
-
I even go so far as to often include a 1=1 in my where clause so I can comment out any part of the where clause:
select * from blah where 1=1
and mycol=12
and othercol=344
this way I can easily comment out either part of the clause without removing one of the ands.
---------------------------------------
elsasoft.org -
The fact that you are thinking about conventions and standards is huge.
I'll sacrifice a little spead and ease of maintenance for readability. I put the comma after the column because my experience has shown me that I'll end up reading code an exponential number of times more than I'll have to update it.
-
I do the same this when programming. I will include the 1=1 in my where clause so that any other conditions can be included as ", blah=blah".
-
But you have the same problem commenting out the first line... Yes?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I agree with Ed on both points... The fact that you are even considering some standardized method of formatting is a huge step in the right direction. And, whatever format you decide upon, it should come natural for typing and be extremely readable. Above all, it must be consistent.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply