What is your preferred syntax for SQL aliases?

Question

I have seen many different syntax styles for aliases in SQL. Are there any advantages/disadvantages to any of these syntax styles? What is your preference?

Alias only:

SELECT patient.nm_first   patient_firstname, 
       patient.nm_last    patient_lastname,
       physician.nm_first physician_firstname,
       physician.nm_last  physician_lastname

Use AS:

SELECT patient.nm_first   AS patient_firstname, 
       patient.nm_last    AS patient_lastname,
       physician.nm_first AS physician_firstname,
       physician.nm_last  AS physician_lastname

Using equal:

SELECT patient_firstname   = patient.nm_first,   
       patient_lastname    = patient.nm_last, 
       physician_firstname = physician.nm_first,
       physician_lastname  = physician.nm_last

Answer 1

Mine is to use the

column AS alias

convention. It's less ambiguous than the other methods. Using the "=" can easily cause confusion with other expressions when scanning the code. With no keyword at all, it's unclear if you're looking at multiple columns or a column and an alias.

Of the three, using "AS" is the most clear when reading the code.

Answer 2

I just alias with the space. The less keystrokes I have to make during my day, the better.

On occasion (working in Oracle usually), I will use the AS statement just because some lengthy procedures lend themselves to readability that way.

Answer 3

I like using "AS", simply for better SQL readability.

Answer 4

The most important thing is to be consistent. Talk it over with your co-workers and see if your company uses one more frequently than the others. I can't think of any strong arguments for one over any other.

Answer 5

I believe that AS is the clearest to read. There is no implication of computed columns with AS, whereas '=' does imply a computed column and simple whitespace can be confusing (it appears there are more columns if you don't notice the missing ',').

Other than that, it is preference and convention.

Answer 6

I like the "AS", it separates and distinguishes the new column name best to my eye.

At first glance, the "=" method looks like an assignment or condition, and the space style looks like a syntax error to me.

Answer 7

AS keyword is a SQL-92 standard while = is not.

Use AS if you need to be standards compliant.

My preference is to use =, personally, for quick selects. For work, I am for AS

Answer 8

I use just space as it requires less typing. As is also good because it is clear. = is not standard and might be confusing so I would not prefer it.

And please do not use capitalized keywords in sql, nowadays editors can color keywords.

Answer 9

Seems I'm the odd man out here, but I strongly prefer alias = expression.

Aliases tend to be shorter than expressions, and of more consistent length, so in any complex SELECT clause it keeps things more readable. eg.

select
  groupby1
, groupby2
, ratio1   = convert(float,sum(case <big long thing here> end))
           / nullif(sum(case <big long thing here> end),0)
, total2   = <some other long expression>
from table1 etc....

In this format, you can visually parse the SELECT list in one pass. If you need more detail you can quickly find and focus on a particular expression, as it maps to a column in the result set.