james james - 7 months ago 22
SQL Question

What are the parentheses doing in this SQL Statement in the FROM CLAUSE (TSQL)

What are the parentheses on lines 5 and 11 for?

1 SELECT
2 s.name, s.ShirtDescription, c.Color,
3 z.Size, i.AvailableQTY, i.UnitPrice
4 FROM
5 (
6 test.ShirtInventory i
7 join test.Colors c ON
8 i.ColorID = c.id
9 join test.Shirts s ON
10 i.ShirtID = s.ID
11 )
12 JOIN test.Sizes z ON
13 i.SizeID = z.ID
14 WHERE .....


I've never seen parentheses used this way in the FROM Clause. This isn't a sub query, and it's not scoping the the table and the joins. You can see where I reference i.SizeID outside the parentheses. When I first saw it, I thought it might be a way to "hint" to SQL Server how you wanted the data to be fetched, but nothing changes in the execution plan when you remove the parens.

Look forward to your replies.
edit: got the lines wrong

Answer

Nothing in this case as inner joins are associative and commutative.

In general you can change the virtual tables that participate in joins by moving the position of the on clause and this can have optional parentheses applied also to hopefully make things clearer.

So for example you could have

SELECT P.PersonName,
       Pt.PetName,
       Pa.AccessoryName
FROM   Persons P
       LEFT JOIN Pets Pt
                  INNER JOIN PetAccessories Pa
                    ON Pt.PetName = Pa.PetName
         ON P.PersonName = Pt.PersonName;

Which optionally might also be written

SELECT P.PersonName,
       Pt.PetName,
       Pa.AccessoryName
FROM   Persons P
       LEFT JOIN (Pets Pt
                  INNER JOIN PetAccessories Pa
                    ON Pt.PetName = Pa.PetName)
         ON P.PersonName = Pt.PersonName;