5

What is the difference between

select  empName as EmployeeName from employees

versus

select  EmployeeName = empName from employees

from a technical point of view. Not sure if this is just SQL server specific or not.

Appreciate your answers.

ManiP
  • 713
  • 2
  • 8
  • 19

3 Answers3

8

I'd prefer the first one, since the second one is not portable - 

select  EmployeeName = empName from employees

is either a syntax error (at least in SQLite and Oracle), or it might not give you what you expect (comparing two columns EmployeeName and empName and returning the comparison result as a boolean/integer), whereas

select  empName EmployeeName from employees

is the same as

  select  empName as EmployeeName from employees

which is my preferred variant.

Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
  • Are sure that `select EmployeeName empName from employees` is valid sql? – Jon Egerton May 25 '11 at 13:59
  • @Jon if EmployeeName is the name of the column in the table, yes I'm pretty sure it is valid SQL :-) – Frank Schmitt May 25 '11 at 14:07
  • The syntax is valid, but I think Frank Schmitt got the columns mixed up from my previous query. The column in the table is empName while the output should be aliased as EmployeeName so syntax would be 'select empName EmployeeName from employees' is correct SQL. – ManiP May 25 '11 at 14:11
  • @ManiP - nice one - thought I was going mad! – Jon Egerton May 25 '11 at 14:41
3

The main advantage of the second syntax is that it allows the column aliases to be all lined up which can be of benefit for long expressions.

SELECT foo,
       bar,
       baz = ROW_NUMBER() OVER (PARTITION BY foo ORDER BY bar)
FROM T
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    Since SQL doesn't care about whitespace, can't you line them up in either case? – Alison R. May 25 '11 at 13:49
  • 1
    @Alison R. You could do, but its more work having to make the white spaces. Plus, if something changes in the data item, then the white space will have to be adjusted again – Curtis May 25 '11 at 13:50
  • @Alison - Yes. "Allow" was the wrong word but that would mean `baz` in my example wrapping onto 2 lines. One with the expression and the next with the alias. – Martin Smith May 25 '11 at 13:54
  • 1
    For the record, I've been using the 1st preference for years now, but this point has made me think twice! – Curtis May 25 '11 at 13:54
  • 1
    I'd use the second syntax if **all** the columns were aliased. If only a few are, I get confused when reading, trying to figure out if a given line starts with the data being returned, or just the alias. – Philip Kelley May 25 '11 at 13:54
  • Having the alias names on the left makes it a lot easier to read in many cases. For example; The field name should indicated what the code does, giVing insight before you read and SQL. All queries in an SP tend ot have field names in the same column on the screen, making them much easy to spot. – MatBailie May 25 '11 at 14:45
1

I don't think there's a technical difference. Its mainly preferential. I go for the second as its easier to spot columns in big queries, especially if the query is properly indented.

Jon Egerton
  • 40,401
  • 11
  • 97
  • 129