views:

185

answers:

14

Is there any particular reason (performance or otherwise) to use AS ahead of = when aliasing a column?

My personal preference (for readability) is to use this:

select
alias1     = somecolumn
alias2     = anothercolumn
from
tables
etc...

instead of this:

select
somecolumn as alias1
anothercolumn as alias2
from
tables
etc...

Am I missing out on any reason why I shouldn't be doing this? What are other people's preferences when it comes to formatting their columns?

+2  A: 

I prefer using AS since = is used in the where statement, and can be confusing in a long query.

pgb
Only if you are doing nested selects inside your select clause, or not formatting your query nicely, both of which are far greater concerns in my opinion
jacko
+7  A: 

I wouldn't use it simply as it looks far too much like equality operation. 'AS' is clear inasmuch that it's not ambiguous to me.

Its the same as not using upper case in sql, I find it harder to read.

Preet Sangha
+2  A: 

I prefer using neither of those. I just give the name of the column without any keyword in between

SELECT MAX(price_column) maximumprice FROM prices
Scoregraphic
I do this as well, due to painful personal history behind use of "as" within queries. I *always* make sure those alias names stand out, through use of white space and (when there's more than one) column alignment.
Philip Kelley
A: 

**even i prefer using 'as' instead of '=' . '=' makes confusion in code.

e.g :

 column as alias1
anishmarokey
...and this means a lot to mankind? ;)
Scoregraphic
+2  A: 

To put in some counterweight, I prefer using =.

If I am the consumer of the query results in some way, I find it more convenient to see what columns I as a consumer can use.

I prefer this

SELECT
      [ElementObligationID] = @MaxElementObligationID + eo.ElementObligationID
      , [ElementID] = eo.ElementID
      , [IsotopeID] = eo.IsotopeID
      , [ObligationID] = eo.ObligationID
      , [ElementWeight] = eo.ElementWeight * -1
      , [FissileWeight] = eo.FissileWeight * -1
      , [Items] = eo.Items * -1
      , [Comment] = eo.Comment
      , [AdditionalComment] = eo.AdditionalComment
      , [Aanmaak_userid] = @UserID
      , [Aanmaak_tijdstip] = GetDate()
      , [Laatste_wijziging_userid] = @UserID
      , [Laatste_wijziging_tijdstip] = GetDate()
FROM  dbo.KTM_ElementObligation eo
      INNER JOIN dbo.KTM_ElementObligationArticle eoa ON 
          eoa.ElementObligationID = eo.ElementObligationID

over this

SELECT
      @MaxElementObligationID + eo.ElementObligationID AS [ElementObligationID]
      , eo.ElementID AS [ElementID]
      , eo.IsotopeID AS [IsotopeID]
      , eo.ObligationID AS [ObligationID]
      , eo.ElementWeight * -1 AS [ElementWeight]
      , eo.FissileWeight * -1 AS [FissileWeight]
      , eo.Items * -1 AS [Items]
      , eo.Comment AS [Comment]
      , eo.AdditionalComment AS [AdditionalComment]
      , @UserID AS [Aanmaak_userid]
      , GetDate() AS [Aanmaak_tijdstip]
      , @UserID AS [Laatste_wijziging_userid]
      , GetDate() AS [Laatste_wijziging_tijdstip]
FROM  dbo.KTM_ElementObligation eo
      INNER JOIN dbo.KTM_ElementObligationArticle eoa ON 
          eoa.ElementObligationID = eo.ElementObligationID

just my 2c.

Lieven
+1: I worked on a couple of projects where the use of "alias = ..." and "... as alias" was mixed between different stored procedures -- and occasionally the same stored procedure. I find "alias = ..." much much easier to read and write. In particular, I find that right-aligning aliases using "... {tab} {tab} {tab} as alias" slightly more readable no alignment, but maintenance is a nightmare.
Juliet
+2  A: 

= can be confused with assignment and equality; actually, the form I really don't like is when it looks like a string (usually when spaces are involved):

somecolumn as 'alias 1'

or

'alias 1' = somecolumn

I far prefer the alternative notation:

somecolumn as [alias 1]
Marc Gravell
There is a potential for confusion but only if you are doing nested selects inside your select clause, or not formatting your query nicely, both of which are far greater concerns in my opinion
jacko
+4  A: 

"=" is just plain ambiguous.

If you indent to break out each select clause...

select
    alias1     = somecolumn,
    alias2     = anothercolumn,
    result     = column1 * column2
from
    table
....


select
    somecolumn as          alias1,
    anothercolumn as       alias2,
    column1 * column2 as   result
from
    tables
     ...
gbn
While I disagree that = is ambiguous, this is a nice alternative...
jacko
Good indentation really helps readability. But I always align the "AS" on the right just before the alias. It's more readable.
Rob Garrison
+2  A: 

Column aliases declared by "=" syntax are deprecated in SQL Server 2008 and not supported in the next version. See MSDN article.

devio
That only mentions the deprecation of *'string_alias' = expression*, **not** *column_alias = expression* wich is still available
Lieven
+9  A: 

‘=’ isn't valid ANSI SQL, so you'll have difficulty should you wish to run your application on a different DBMS.

(It's when ANSI form is used but the optional ‘AS’ is omitted I find the results difficult to read, personally.)

bobince
A valid point, but I would argue the improvement in readability in 95% of cases outweighs the 5% of cases where you would need to execute a query against a different DBMS
jacko
The readability judgement is rather subjective! :-) How often you need a different DBMS is dependent on the type of project; if it's an in-house tool for an MS-oriented company relying on T-SQL extensions might be appropriate; for, say, an open-source project it wouldn't really fit.
bobince
+1 This would be the only reason why I would consider changing my SQL writing style but I will postpone until the event occurs (wich may well be never).
Lieven
I suppose ANSI compliance is a good enough reason to use AS over "=", and with some of the formatting suggestions below, it can still be very readable. Considering I work on SqlServer 99% of the time however, its not enough for me to change my style :)
jacko
+1  A: 

The postfix alias form (with or without the "AS") is consistent between column and table aliases. Personally, I'd like an option to enforce the use of "AS", and then you wouldn't have the situation:

select
    columnA,
    columnB
    columnC
from
    table

producing a result set with two columns instead of the expected 3.

I'd also say that with the prefix "=" form, it can make it more difficult to read if you're mixing obtaining a result set and variable assignment:

select
    cA = columnA,
    @cB = columnB,
    cC = columnC
from
    table
Damien_The_Unbeliever
+1  A: 

While I have a preference for using AS, the really key thing here is to have a corporate standard and to follow it. If more of your people use AS than = then everyone should use it. Coding standards are what makes it easier to maintain code not the particular standard you pick. If everyone uses the same thing, then your eye gets used to picking it out.

HLGEM
A: 

The three ways I know of to alias:

  1. TableColumn AS MyAlias
  2. TableColumn MyAlias
  3. MyAlias = TableColumn

Re: 1), I prefer this as it is the most self-documenting code (IMO), and it lets me search for AS if I need to find aliases..

Re: 2), This is my second choice, but without the AS, I am never sure whether this is a cut-and-paste error or not, especially in long, badly-formatted queries.

Re: 3), I don't like this because a) it looks like an assignment, and b) it blends in too much with ON clauses and CASE statements

So, my vote is to use the AS keyword for your aliases.

RedFilter
Is the `=` commutative in SQL with respect to aliases? I've never seen it used as you're describing instead of `MyAlias = TableColumn`.
Austin Salonen
You're right, I transposed it, fixed now.
RedFilter
+1  A: 

You don't have to use either

Drop the AS and use

SELECT originalname alias
FROM
   tablename
John Nolan
+1  A: 

I like the

SELECT
 column1 = table.column1
 ,column2 = table.colum2
FROM table

I find AS not as easily noticable compared to a = sign (I can spot = quicker than AS)

Also when one just does SELECT column alias, sometimes it's confusing to know which one is which :)

jerryhung