Hello friends!
We will speak about Alias and only two minute to learn some important things is really a big deal!
So think big and think that Share knowledge is the only way!
Alias
I think that everyone know what an alias is. We can have a table and call it with another name or we can have a field of a table and call it with another another name.
SELECT CODE FROM ORDTES
SELECT CODE as CODICE FROM ORDTES as ORDINI
Where 'as' is optional
Using alias if your statement represents a derived table then you can refer to it through the alias:
SELECT * FROM ORTDES
JOIN
(SELECT CODE AS CODICE FROM ORDTES) AS ORDINI ON ORDINI.CODICE = ORDTES.CODE
 |
| Tips: You can see the Alias also int the execution plan |
And then..
Suppose now to play with our usual ORDTES table.
Suppose again you need to write a statement to get the max number of document.
Using a not efficient way (i know) we could write:
SELECT id FROM ORDTES dad
WHERE NOT EXISTS (SELECT son.id FROM ORDTES son WHERE son.NUMDOC > dad.NUMDOC)
Obiouvsly we could't write this query with out the Aliases
Without alias we can't write a query where the same table name is used in the main query and inside a subquery.
Keep attention we can execute the query below and it run
SELECT id FROM ORDTES
WHERE NOT EXISTS (SELECT id FROM ORDTES WHERE NUMDOC > NUMDOC)
But the statemement inside the NOT EXISTS refer only to itself!
There will never be a row of ordtes where numdoc> numdoc and so the not exists will always return true.
A rule to remember!
Remember the rule:
for each table, SQL Server tries to find a match within the same scope and, if necessary, switches to an external scope
That's all for today! and... Think big! Share Knowledge!
Luca
