Aliases in SQL Server

Question

Are these two statements equivalent?

UPDATE Table1 SET Field1=( 
 SELECT Field2 FROM Table2 
 WHERE Table1.ID=Table2.ID
) 
FROM Table1 
WHERE Field1 is null



UPDATE t SET Field1=( 
 SELECT Field2 FROM Table2 
 WHERE t.ID=Table2.ID
) 
FROM Table1 t
WHERE Field1 is null

I'm trying to reduce the amount of aliasing. I feel that adding an alias to a statement only adds another table name to keep track of mentally.

My concern is that in example 1, since I'm not using the alias, it will update the entire table1 instead of filtering on the WHERE Field1 is null.

What is the rule of thumb for when aliasing is required?

Answer 1

Those two statements are equivalent, yes.

I almost always use aliasing in my queries. It keeps the length down and, personally, I think it makes things much easier to read since a short alias makes the field name stand out more prominently.

If you use the full table name, it clutters things up and makes it hard to distinguish which fields you're working with (at a glance, that is).

Answer 2

These statements are equivalent.

An alias is only required if you refer to the table twice, to avoid ambiguity.

Answer 3

Yes, they're equivalent, because aliasing won't ever change the effect of a statement, only its readability or resolving ambiguity.

But I'd do this:

UPDATE Table1 
SET Field1 = Table2.Field2 
FROM Table1 
INNER JOIN Table2 ON Table1.ID=Table2.ID
WHERE Table1.Field1 is null

or this

UPDATE t1
SET Field1 = t2.Field2 
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID=t2.ID
WHERE t1.Field1 is null

Use aliasing to help readability. Personally I like using short aliases (1-3 chars) for each of my tables, always using the same aliases for the same tables wherever possible so over time readability improves.