• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 425
  • Last Modified:

sql server 2012 - Using Derived Tables

hi experts
i do not understand thedifference between two querys


-- Select this query and execute it to show internal column aliases
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders) AS derived_year
GROUP BY orderyear;

-- Select this query and execute it to show external column aliases
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (SELECT YEAR(orderdate), custid
FROM Sales.Orders) AS derived_year(orderyear, custid)
GROUP BY orderyear;
query.png
0
enrique_aeo
Asked:
enrique_aeo
2 Solutions
 
Jared_SCommented:
The second query adds an alias to one of the columns in the derived table - which has absolutely no effect on the results.

The parentheses following the derived table alias in the second query is a mystery to me.

AS derived_year(orderyear, custid)...

It doesn't seem to have an affect, but I don't know what it's goal is.
0
 
sameer_goyalCommented:
If you notice, in the second query, the sub query

(SELECT YEAR(orderdate), custid
FROM Sales.Orders) AS derived_year(orderyear, custid)

does not provide any alias to YEAR(orderdate)

but the same sub query in the first query

(SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders) AS derived_year

does it.

The utility of As derived_year(orderyear,custid) is that it allows you to NOT provide alias attached with the column in the sub query but defer to assign it afterwards.

End result for both the queries, hence, is same.

Let me know if there are queries?
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now