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

Question

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

Was this from a code generator? That would explain useless parentheses — Hogan, Apr 27 '16 at 21:17
@Hogan not sure, the code is from a 3rd party in an interface application. I've got to modify the SQL and wanted to make sure I wasn't breaking something I didn't understand. — fossfool, Apr 27 '16 at 22:04

score 7 · Accepted Answer · edited May 23 '17 at 10:28

7

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;

edited May 23 '17 at 10:28

Community

1
1

answered Apr 27 '16 at 21:13

Martin Smith

438,706
87
741
845

or another way to put it is -- These parentheses are doing the default, if they were moved then that might cause a change. – Hogan Apr 27 '16 at 21:16
1

Thanks Martin for the clarification. Thinking back, I've seen parens used with outer Joins in the past as you mentioned. the more I think about the circumstances surrounding this mess, The more I think @Hogan might be on to something with his thought about an automated scripting tool being in the mix somewhere. – fossfool Apr 27 '16 at 22:12

score 1 · Answer 2 · answered Apr 27 '16 at 21:13

1

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

answered Apr 27 '16 at 21:13

Rich Benner

7,873
9
33
39

3

That's not a derived table. Derived table would require a full Select statement inside the parenthesis. These parenthesis do nothing. Maybe they were left over from a time when there was a full Select statement inside the parenthesis. – Timothy Kanski Apr 27 '16 at 21:14
Yep, didn't notice that. you're totally correct, thanks for fixing that one. – Rich Benner Apr 27 '16 at 21:16
1

when I first saw this, I was thinking the same thing, Rich. I wasn't until I noticed the subsequent JOINS referring back to tables inside the parens that I knew it wasn't a subquery, thanks for the input. – fossfool Apr 27 '16 at 22:16

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

2 Answers2