CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
fossfool
fossfool

Reputation: 150

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

Upvotes: 2

Views: 1015

Answers (2)

Martin Smith
Martin Smith

Reputation: 453327

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;

Upvotes: 7

Rich Benner
Rich Benner

Reputation: 8113

It is a sub query, its treating everything within the parenthesis as the base table. It doesn't look necessary in this instance though as it's just then joining to the final table anyway

Upvotes: 1

Related Questions