Unfamiliar character in SQL statement

Question

This is sort of SQL newbie question, I think, but here goes.

I have a SQL Query (SQL Server 2005) that I've put together based on an example user-defined function:

SELECT 
    CASEID,
    GetNoteText(CASEID)
FROM 
( 
    SELECT 
        CASEID 
    FROM 
        ATTACHMENTS 
    GROUP BY 
        CASEID 
) i
GO

the UDF works great (it concatenates data from multiple rows in a related table, if that matters at all) but I'm confused about the "i" after the FROM clause. The query works fine with the i but fails without it. What is the significance of the "i"?

EDIT: As Joel noted below, it's not a keyword

Answer 1

When you use a subquery in the FROM clause, you need to give the query a name. Since the name doesn't really matter to you, something simple like 'i' or 'a' is often chosen. But you could put any name there you wanted- there's no significance to 'i' all by itself, and it's certainly not a keyword.

If you have a really complex query, you may need to join your sub query with other queries or tables. In that case the name becomes more important and you should choose something more meaningful.

Answer 2

The "i" is giving your select statement an effective table name. It could also be written (I think - I'm not an MSSQLServer guy) as "AS i".

Answer 3

As others stated, it's a table name alias for the subquery.

Outside the subquery, you could use i.CASEID to reference into the subquery results.

It's not too useful in this example, but when you have multiple subqueries, it is a very important disambiguation tool.

Although, I'd choose a better variable name. Even "temp" is better.

Answer 4

The i names the (subquery), which is required, and also needed for further joins.

You will have to prefix columns in the outer query with the subquery name when there are conflicting column names between joined tables, like:

SELECT 
    c.CASEID, c.CASE_NAME,
    a.COUNT AS ATTACHMENTSCOUNT, o.COUNT as OTHERCOUNT,
    dbo.GetNoteText(c.CASEID)
FROM CASES c
LEFT OUTER JOIN
( 
    SELECT 
        CASEID, COUNT(*) AS COUNT
    FROM 
        ATTACHMENTS 
    GROUP BY 
        CASEID 
) a
ON a.CASEID = c.CASEID
LEFT OUTER JOIN
(
    SELECT 
        CASEID, COUNT(*) AS COUNT
    FROM 
        OTHER
    GROUP BY 
        CASEID 
) o
ON o.CASEID = c.CASEID

Answer 5

The i names your subquery so that if you have a complex query with numerous subqueries and you need to access the fields you can do so in an unambiguous way.

It is good practice to give your subqueries more descriptive names to prevent your own confusion when you start getting into writing longer queries, there is nothing worse then having to scroll back up through a long sql statement because you have forgotten which i.id is the right one or which table/query c.name is being retrieved from.

Answer 6

"Derived table" is a technical term for using a subquery in the FROM clause.

The SQL Server Books Online syntax shows that table_alias is not optional in this case; "table_alias" is not enclosed in brackets and according to the Transact-SQL Syntax Conventions, things in brackets are optional. The keyword "AS" is optional though since it is enclosed in brackets...

   derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]

FROM (Transact-SQL):
http://msdn.microsoft.com/en-us/library/ms177634(SQL.90).aspx

Transact-SQL Syntax Conventions:
http://msdn.microsoft.com/en-us/library/ms177563(SQL.90).aspx

Answer 7

The lesson to learned is to think of the person who will inherit your code. As others have said, if the code had been written like this:

SELECT DT1.CASEID, GetNoteText(DT1.CASEID) 
FROM (
   SELECT CASEID 
   FROM ATTACHMENTS
   GROUP BY CASEID
) AS DT1 (CASEID);

then there's an increased chance the reader would have figured it out and may even pick up on 'DT1' alluding to a 'derived table'.