September 12, 2020 at 7:48 pm
I'm working on a tsql parser and hit following allowed syntax:
select top 30 *
FROM(
table1 s (NOLOCK)
LEFT OUTER JOIN table2 r (NOLOCK)
ONr.id = s.id
cross apply someothertable cp (NOLOCK)
)
LEFT OUTER JOIN table3 t3 (NOLOCK)
ONt3.id = r.id
It seems like it's possible to insert brackets in some cases in the from clause.
Does anyone know exactly the grammar for this construct, or some kind of information on how scoping etc works in this kind of query?
September 13, 2020 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 14, 2020 at 7:47 am
Brackets are generally used to make nested joins more recognizable even thought it is actually the order of the ONs that matter.
As this is not a nested join, the brackets can be ignored.
September 14, 2020 at 8:38 am
My Personal question is why is the query using NOLOCK against every table, and why is there an TOP clause when there's no ORDER BY? That query is really likely to give inconsistent results each time it is run.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
September 14, 2020 at 5:49 pm
Sorry for the red herrings, I just whipped up an "anonymized" version of one of our internal queries for demo purposes 🙂
The brackets are the thing of interest here, since noone of my co-workers have ever seen this kind of syntax.
NOLOCKs is another story...
September 14, 2020 at 7:09 pm
An example of a nested join is:
FROM TableA A
LEFT JOIN
(
TableB B
JOIN TableC C
ON B.Bid = C.Bid
)
ON A.Aid = B.Aid;
which could be written like:
FROM TableA A
LEFT JOIN TableB B
JOIN TableC C
ON B.Bid = C.Bid
ON A.Aid = B.Aid;
The second example is less clear so most style guides say to use the brackets.
This example could of course be written without nesting:
FROM TableB B
JOIN TableC C
ON B.Bid = C.Bid
RIGHT JOIN TableA A
ON B.Aid = A.Aid;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply