Question regarding Aliasing

Question

If I have a TSQL like:

"

Select yada yada yada from

TableA Left Outer Join TableB ON [condition]

Left Outer Join TableB as TableB_2

"

So is Alias TableB_2 is just for TableB

OR

Does it represnt the entire query i,e TableB_2 in place of

"

TableA Left Outer Join TableB ON [condition]

Left Outer Join TableB as TableB_2

"

Thank you!

Answer 1

TableB_2 alias in this case refers to TableB alone.

Answer 2

Yes, TableB_2 is the alias for TableB for the whole query.

---

Many Thanks & Best Regards, Hua Min

Answer 3

TableB_2 is alias for TableB only.

Answer 4

alias stands for the table/ resultset which is present immediately before that. In this case, this is TableB, else if there would have been braces, then it can represent for whole statement.

Select yada yada yada from
(
select * from TableA Left Outer Join TableB ON [condition]
Left Outer Join TableB)
as TableB_2 // here TableB_2 stand for whole statement

http://msdn.microsoft.com/en-us/library/ms187455(v=sql.90).aspx

regards

joon

Answer 5

I think that you are confusing Derived tables with Table Alias.  You can have an alias for Table & Column.

Column Alias: Select FirstName + ' ' + LastName as FullName from table1. Here FullName is a column alias.

Table Alias: This is usually used during the join operations when you try to refer a column which are present in both the tables.

Select * from Table1 A join Table2 B on A.ID = B.ID. Here A and B are Table alias.

There is one more alias you can use for a query.

SELECT RTRIM(c.FirstName) + ' ' + LTRIM(c.LastName) AS Name,
d.City
FROM Person.Contact c
INNER JOIN HumanResources.Employee e ON c.ContactID = e.ContactID
INNER JOIN (SELECT AddressID, City FROM Person.Address) AS d
ON e.AddressID = d.AddressID
ORDER BY c.LastName, c.FirstName

Here we call 'd' as Derived table and not an alias. So when a name is given to a query we call them as derived tables.

---

Murali Krishnan

Answer 6

Thank you for detailed explaning.

Answer 7

The ANSI model is that effectively an alias creates a new table from its table expression and hides all of the tables used in that expression. This is a fiction, but a handy that defiens scoping rules for derived tables, CTEs, etc. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 8

TableB_2 will be alias to tableb and not to the join what you have made