May 14, 2015 at 3:32 am
Guys,
I just came across this in one of our views
CREATE VIEW [dbo].[Vsinv_accp]
AS
SELECT
dbo.sinv_accp.invoice,
dbo.sinv.price_curr AS currency,
dbo.sinv_accp.level_1,
dbo.cust_qlfl.ql_ledger_id AS ledger_id,
dbo.cust_qlfl.area_id,
dbo.sinv_accp.ledger_status,
dbo.sinv_accp.date_accepted AS date_entered,
dbo.sinv.customer
FROM
dbo.paym WITH (nolock)
RIGHT OUTER JOIN
dbo.cust_defl WITH (nolock)
INNER JOIN
dbo.sinv_accp WITH (nolock)
INNER JOIN
dbo.sinv WITH (nolock) ON dbo.sinv_accp.invoice = dbo.sinv.invoice
INNER JOIN
dbo.cust_qlfl WITH (nolock) ON dbo.sinv.customer = dbo.cust_qlfl.customer
ON dbo.cust_defl.customer = dbo.sinv.customer
ON dbo.paym.code = dbo.cust_defl.pay_method
WHERE
(dbo.paym.pay_type <> 10)
OR
(dbo.paym.pay_type IS NULL)
In 10 years of coding SQL I have never seen multiple ON clauses in a join - The syntax checker doesn't seem to worry about it - I would normally do with with AND rather than ON. Is this valid code, is it ANSI compliant in not is it deprecated in future versions?
Syntactically will it produce the same results as using AND for the multiple conditions and will it modify the query plan
Don't get me started on the nolocks - not my code!
May 14, 2015 at 3:46 am
There aren't multiple ON clauses in a join there, it's not allowed. What's there is a rather badly formatted mess. It is NOT equivalent to a single join with ANDs, no more than you can replace all the ON clauses with a single ON clause in a query like this:
FROM x Inner Join y on x.a=y.a inner join z on y.b=z.b
Each of the ON clauses belongs to one JOIN, they're just not adjacent to each other in the code, you'll see some joins don't have the ON right after them but rather have it at the end. Personally I find this hell confusing to read, it's better when brackets are added, but the brackets aren't required and so you see this kind of mess.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 14, 2015 at 6:41 pm
I gotta wonder what the ANSI standards say about this one!
Never seen anything like it before, but I will take note when writing my "Obfuscasting SQL" book! Looks better than encryption!
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
May 14, 2015 at 8:49 pm
If I had to guess - the person who wrote this started out writing queries in Access. It prefers to nest joins inside other joins, e.g.
select column
from A
join B
Join C
Join D
on c.id=d.ID
on b.id=c.id
on a.id=b.id
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 14, 2015 at 11:48 pm
dwain.c (5/14/2015)
I gotta wonder what the ANSI standards say about this one!
Perfectly legal afaik.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 15, 2015 at 6:19 am
GilaMonster (5/14/2015)
dwain.c (5/14/2015)
I gotta wonder what the ANSI standards say about this one!Perfectly legal afaik.
It's legal. I've seen this before. List all the tables and then all the ON clauses separately. You can do it. I'd say though, that doesn't mean you should. It looks really messy and it's got to be a lot harder to read. It's going to cause confusion, as we're getting here. While the syntax is supported, I would caution against it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 15, 2015 at 6:57 pm
Grant Fritchey (5/15/2015)
GilaMonster (5/14/2015)
dwain.c (5/14/2015)
I gotta wonder what the ANSI standards say about this one!Perfectly legal afaik.
It's legal. I've seen this before. List all the tables and then all the ON clauses separately. You can do it. I'd say though, that doesn't mean you should. It looks really messy and it's got to be a lot harder to read. It's going to cause confusion, as we're getting here. While the syntax is supported, I would caution against it.
Actually with decent layout it's perfectly clear and understandable and doesn't need brackets, while the other way of doing it will require brackets to be used anywhere the join order isn't a total order, and that is pretty hard to read unless used in conjunction with decent layout. I tend to use whichever seems more appropriate to the particular nesting of the joins in the query. Of course if all the joins are inner the order doesn't affect the semantics, but I can remember a time when optimisers weren't very bright and the order could affect performance so sometimes a partial order would deliver better performance than a total order even on quite small numbers of rows (and I'm not even sure that modern optimisers handle the awkward cases well). Of course it would have been real fun to use a genuine reverse (or forward) polish syntax for joins to avoid separating the join keyword(s) from the ON clause, but as no-one but mathematicians generally uses notation like that it would probably have been too confusing.
Tom
May 15, 2015 at 7:56 pm
TomThomson (5/15/2015)
Grant Fritchey (5/15/2015)
GilaMonster (5/14/2015)
dwain.c (5/14/2015)
I gotta wonder what the ANSI standards say about this one!Perfectly legal afaik.
It's legal. I've seen this before. List all the tables and then all the ON clauses separately. You can do it. I'd say though, that doesn't mean you should. It looks really messy and it's got to be a lot harder to read. It's going to cause confusion, as we're getting here. While the syntax is supported, I would caution against it.
Actually with decent layout it's perfectly clear and understandable and doesn't need brackets, while the other way of doing it will require brackets to be used anywhere the join order isn't a total order, and that is pretty hard to read unless used in conjunction with decent layout. I tend to use whichever seems more appropriate to the particular nesting of the joins in the query. Of course if all the joins are inner the order doesn't affect the semantics, but I can remember a time when optimisers weren't very bright and the order could affect performance so sometimes a partial order would deliver better performance than a total order even on quite small numbers of rows (and I'm not even sure that modern optimisers handle the awkward cases well). Of course it would have been real fun to use a genuine reverse (or forward) polish syntax for joins to avoid separating the join keyword(s) from the ON clause, but as no-one but mathematicians generally uses notation like that it would probably have been too confusing.
+1. Same thoughts here. Although a lot of folks hated it, I used to (and still) love having the joins in the WHERE clause for this very reason. Of course, it causes most ANSI zealots' hair to spontaneous burst into flames but, sometimes, that's half the fun. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
May 15, 2015 at 7:59 pm
Oddly enough, I've seen the "query builder" generate such code a whole lot in my early days and it worked just fine. There are places where it doesn't work. And, no... I've never kept examples of any of that.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2015 at 4:45 am
Jeff Moden (5/15/2015)
Oddly enough, I've seen the "query builder" generate such code a whole lot in my early days and it worked just fine. There are places where it doesn't work. And, no... I've never kept examples of any of that.
Quite few code generating tools and GUIs do this too. I do recall few exceptions but most of the time it works, like Jeff, I have not kept any samples there on.
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply