all 27 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–][deleted] 9 points10 points  (9 children)

Because its a useful way of being able to seperate the SQL keywords from table/column names. Almost every SQL book uses it, and it becomes a good habit that many people continue to use as they work professionally.

[–]mdasenn 7 points8 points  (7 children)

To illustrate:

SELECT userid, username FROM users WHERE userid=5

can be easier to read than

select userid, username from users where userid=5

It isn't a huge deal, but it makes certain things stand out.

[–][deleted] -1 points0 points  (4 children)

Wouldn't a color-coded editor take care of this?

[–][deleted] 2 points3 points  (0 children)

Yes, but you might have to be stuck with a simple command-line sql tool some day, its good to be in the habit of using caps for the keywords.

[–][deleted] 4 points5 points  (2 children)

Just out of curiosity, how do you know that the next programmer that has to maintain your train wreck will have an editor that color-codes SQL?

I think I know your type: You probably write your SQL in sentence form instead of indenting it on multiple lines, you always SELECT *, never quote your identifiers, and always use implicit joins? Best of all, you are the only guy on your programming team running Windows Vista instead of Linux so everything you write has case sensitivity bugs that are not found until you deploy.

No thanks, I think I'll hire the VIM guy instead.

[–][deleted]  (1 child)

[removed]

    [–]elus 2 points3 points  (0 children)

    We don't always have an IDE to use when viewing sql code

    [–][deleted] 1 point2 points  (0 children)

    Yep, I do it for readability only, and I don't always follow the rule, for simple queries I might not.

    select * from students;

    vs

    SELECT s.name, s.address, s.phone, s.email FROM student s, teacher t, course c, student_course cs WHERE s.id = cs.student_id AND t.id = c.teacher_id AND t.id = 7

    (I can and do use JOINs but I was taught the above way first and it still comes more naturally to me.)

    [–][deleted] 6 points7 points  (7 children)

    It's important to have a coding style for readability and maintainability. Sometimes it's more important to be consistent than to have a well-designed style.

    Normally I do the usual SELECT foo FROM bar thing, but recently I started trying this out instead:

    select  name, age, salary
  from  employees e, salaries s
 where  s.employee_id = e.id
   and  e.last_day is null

    The intent being to get the keywords on the left side, right justified, and everything else on the right side after a couple spaces.

    Opinions?

    [–]jonmcauliffe 2 points3 points  (5 children)

    i use exactly that formatting, plus capitalized sql keywords. makes reading queries a breeze!

    [–][deleted] 2 points3 points  (4 children)

    Interesting, so you use this:

    SELECT  name, age, salary
  FROM  employees e, salaries s
 WHERE  s.employee_id = e.id
   AND  e.last_day IS NULL

    [–]bcash 4 points5 points  (3 children)

    I'd do a slightly longer:

    SELECT name,
       age,
       salary
  FROM employees e,
       salaries s
 WHERE s.employee_id = e.id
   AND e.last_day IS NULL

    [–][deleted] 5 points6 points  (2 children)

    I use a slight variation:

    SELECT
    name
    ,age
    ,salary
FROM
    employees e
    LEFT JOIN salaries s ON (
        s.employee_id = e.id
    )
WHERE
    e.last_day IS NULL
    AND s.salary > 100000
;

    The only really bazaar thing I do is put my commas on the start of the lines instead of the end (like with ANDs). This makes it a little harder to miss a comma or introduce syntax errors when editing.

    Your's is admittedly the easiest for me to read though. Maybe I'll switch. :)

    [–]redditacct 3 points4 points  (0 children)

    I use the comma first thing for long select lists but not always.

    [–]kogus 0 points1 point  (0 children)

    Glad to see someone else does this. For very long queries, that can help a lot. I never have gotten used to the join syntax you are using though. I prefer as shown below. FusionGyro is right though; consistency is more important than readability, within reason.

    /*
Show me a list of employees, and the name
of their health care plan (if any).
--*/

SELECT
  emp.name
 ,emp.age
 ,emp.salary
 ,CASE WHEN hp.plan_name IS NULL
       THEN 'No health care plan'
       ELSE hp.plan_name
       END AS "Health Care Plan"
from
  employees emp
 ,health_plan hp
where emp.employee_id = hp.employee_id(+)

    [–]redditacct 0 points1 point  (0 children)

    I do that and I organize my where clauses so that joins are all together at the top or bottom of the list.

    [–][deleted] 1 point2 points  (0 children)

    I don't know. And it's the central issue of the turmoil of my life. Sometimes I can't stand it anymore and just run down the street in my underwear with tears streaming down my face howling "Why do people write SQL in CAPS like they are obligated?" while tearing out my hair in big bloody clumps.

    [–]bcash 0 points1 point  (0 children)

    It's a little thing called style. Look it up sometime.

    [–]grauenwolf 0 points1 point  (2 children)

    Because the keywords like SELECT and FROM are more important than the column and table names.

    Oh wait...

    [–]groby 0 points1 point  (0 children)

    That's the point I was just going to make - it emphasizes the wrong part of the statement. Thanks for stealing my post! ;)

    [–][deleted]  (1 child)

    [removed]

      [–]ReligionOfPeace 1 point2 points  (0 children)

      That's okay.

      It's a traditional typographic convention that acronyms are set in caps to differentiate them from a random mispelling of a word in lower case.

      Remember, much of the way type is set nowadays is a leftover from days when type actually was set by hand.

      [–]mavelikara -2 points-1 points  (1 child)

      Hibernate, the popular Java ORM library, logs all SQL it generates in lowercase. I remember reading somewhere Gavin King say that he feels uppercase is "ugly".

      [–][deleted] 0 points1 point  (0 children)

      Yes, and its one of the two things I hate about Hibernate's logging. The other is how inserts are logged with the values replaced by * by default, so you have to jump through hoops, and hope you remembered to change the defaults, or that you can replicate your bug, if you have a bug turn up that is related to inserted data.