July 12, 2019 at 7:54 pm
Did your fine organization implement an ungodly number of triggers as a part of an application release? To the point that when you brought up a valid reason to implement a new trigger it was shot down.
July 12, 2019 at 11:35 pm
Jeff Moden wrote:david.holley wrote:Don’t make me post some examples. The issue isn’t so much the implicit join but rather understanding the criteria in the WHERE statement when it contains the join information as well as any specific criteria. Especially when the {lengthy list of redacted adjectives} developers intermixed the two. Mix in an inline select on one side of the criteria and it goes South even faster.
THAT's what I'm getting at. It's not the implicit joins that are the problem. They're merely a symptom of the rest that follows. Still and like I said previously, correlated sub-queries work using implicit joins. In fact, the same equi-join manner using a WHERE clause that is so non--ANSI join.
Are you saying that this:
SELECT
* -- I know, should be an actual column list
FROM
dbo.tableA as a, dbo.tebleB as b
WHERE
a.AJoinColumn = b.AJoinColumnis not ANSI Standard? But it is, it is ANSI-89 Standard. With that, I personally never learned to use this style having started with the ANSI-92 style joins when I first started working with SQL Server 6.5 back in 1996.
Heh... you and I are showing our age in this business, Lynn. Yes, they were ANSI compliant at one time... they don't usually refer to them as ANSI joins anymore, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 13, 2019 at 2:14 pm
Ha! Version 4.21a was my first taste. Crikey, I feel ancient. ANSI-89 style may have been a standard back in the day, but has long been supplemented. In fact it returns incomplete sets. There used to be a page in the BOL that proved it, with code samples. I have passed the code from that page to many oracle DBAs to prove to them "they are doing joins wrong" 😉
Dave J
July 13, 2019 at 2:59 pm
My other beef is that if you need to tweet the query by changing the join from an (implicit) inner join to left outer or right outer due to a prod issue, you have to covert it. My realm is prod support hence my loathing of implicit joins and (generally) inner joins. I’ve seen too many instances where a developer assumed that data would exist on both sides when in fact instances have happened where that was not the case.
July 13, 2019 at 7:25 pm
Emph mine...
ANSI-89 style may have been a standard back in the day, but has long been supplemented. In fact it returns incomplete sets. There used to be a page in the BOL that proved it, with code samples
I may be reading this wrong but an ANSI 89 style join is simply a filtered CROSS JOIN which will return a complete set. With respect to results and performance, there is absolutely no difference between a CROSS join and an INNER join. The only difference is 100% cosmetic (where you add your filter).
The queries below produce the same results and create the exact same execution plan.
-- This:
SELECT a.col1, b.col1
FROM t1 AS a, t2 AS b
WHERE a.someid = b.someid;
-- is identical to this:
SELECT a.col1, b.col1
FROM t1 AS a
CROSS JOIN t2 AS b
WHERE a.someid = b.someid;
-- which generates the same execution plan and results as:
SELECT a.col1, b.col1
FROM t1 AS a
INNER JOIN t2 AS b
ON a.someid = b.someid;
ANSI standards not withstanding- the INNER keyword is completely unnecessary in SQL Server except for Join hints (https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-join?view=sql-server-2017). E.g. INNER MERGE JOIN.
-- Itzik Ben-Gan 2001
July 13, 2019 at 7:45 pm
Emph mine...
ANSI-89 style may have been a standard back in the day, but has long been supplemented. In fact it returns incomplete sets. There used to be a page in the BOL that proved it, with code samples
I may be reading this wrong but an ANSI 89 style join is simply a filtered CROSS JOIN which will return a complete set. With respect to results and performance, there is absolutely no difference between a CROSS join and an INNER join. The only difference is 100% cosmetic (where you add your filter). The queries below produce the same results and create the exact same execution plan.
-- This:
SELECT a.col1, b.col1
FROM t1 AS a, t2 AS b
WHERE a.someid = b.someid;
-- is identical to this:
SELECT a.col1, b.col1
FROM t1 AS a
CROSS JOIN t2 AS b
WHERE a.someid = b.someid;
-- which generates the same execution plan and results as:
SELECT a.col1, b.col1
FROM t1 AS a
INNER JOIN t2 AS b
WHERE a.someid = b.someid;ANSI standards not withstanding- the INNER keyword is completely unnecessary in SQL Server except for Join hints (https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-join?view=sql-server-2017). E.g. INNER MERGE JOIN.
You forgot to change the WHERE to ON for the INNER JOIN
I'll also add CROSS APPLY to the mix:
SELECT a.col1, b.col1
FROM #t1 AS a
CROSS APPLY #t2 b
WHERE a.someid = b.someid;
SELECT a.col1, b.col1
FROM #t1 AS a
CROSS APPLY (SELECT * FROM #t2 b WHERE b.someid = a.someid) b
Does anyone know why you shouldn't use CROSS APPLY for everything? Or does it sometimes come up with bad plans?
CROSS APPLY and OUTER APPLY seem to be able to do everything JOINS do and some more.
July 14, 2019 at 1:09 am
Does anyone know why you shouldn't use CROSS APPLY for everything? Or does it sometimes come up with bad plans? CROSS APPLY and OUTER APPLY seem to be able to do everything JOINS do and some more.
I can't answer that question. I've not done a diligent comparison of Cross Apply with Inner Joins nor Outer Apply with Left Outer Joins. I do, however, frequently explain Apply as being an implicit correlated sub-query that's easier to use than many correlated equi-join sub-queries.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2019 at 1:23 am
You forgot to change the WHERE to ON for the INNER JOIN
Good catch John, thanks.
Does anyone know why you shouldn't use CROSS APPLY for everything? Or does it sometimes come up with bad plans?
CROSS APPLY was added with SQL Server 2005 for the purpose of allowing table valued functions to accept parameters.
-- this will fail:
SELECT t.*, f.*
FROM dbo.sometable AS t
CROSS JOIN dbo.table_valued_function(t.col1) AS f;
-- this is good:
SELECT t.*, f.*
FROM dbo.sometable AS t
CROSS APPLY dbo.table_valued_function(t.col1) AS f;
What makes APPLY so special is how it allows you to pass values from a table to a subquery on the Right side of the FROM clause.
-- this is okay
SELECT
sub1 = sub1.N,
sub2 = sub2.N,
[sub1+sub2] = sub1.N+sub2.N
FROM (SELECT 1) AS sub1(N)
CROSS JOIN (SELECT 2) AS sub2(N);
-- this will fail with "The multi-part identifier "sub1.N" could not be bound."
SELECT
sub1 = sub1.N,
sub2 = sub2.N
FROM (SELECT 1) AS sub1(N)
CROSS JOIN (SELECT 2+sub1.N) AS sub2(N);
-- this will succeed
SELECT
sub1 = sub1.N,
sub2 = sub2.N
FROM (SELECT 1) AS sub1(N)
CROSS APPLY (SELECT 2+sub1.N) AS sub2(N);
CROSS APPLY with a WHERE filter will usually be identical to a INNER JOIN, OUTER APPLY with a with a WHERE filter will be similar to a LEFT JOIN. Consider these two queries:
-- This returns nothing
SELECT
sub1 = sub1.N,
sub2 = sub2.N
FROM (SELECT 1) AS sub1(N)
CROSS APPLY
(
SELECT 2+sub1.N
WHERE sub1.N < 1
) AS sub2(N);
-- This returns: 1, NULL
SELECT
sub1 = sub1.N,
sub2 = sub2.N
FROM (SELECT 1) AS sub1(N)
OUTER APPLY
(
SELECT 2+sub1.N
WHERE sub1.N < 1
) AS sub2(N);
APPLY is, IMO, the most powerful SQL table operator. It essentially replaces PIVOT (see: Cross Tabs and Pivots) and UNPIVOT (see: An Alternative (Better?) Method to UNPIVOT (SQL Spackle)). You can even use it to replace NTILE (see my alternative here: Odd (to me) Behavior with NTILE() )
Many here will correctly suggest that the best articles about APPLY are by Paul White: Understanding and Using APPLY Part 1 & Part 2).
I would add that this article is equally good and important: The Cascading (CROSS) APPLY
-- Itzik Ben-Gan 2001
July 14, 2019 at 5:02 pm
I do, however, frequently explain Apply as being an implicit correlated sub-query that's easier to use than many correlated equi-join sub-queries.
I like to think of it as operator that is performed for each row of the query to the left of the APPLY with the results all returned as one rowset. Unlike an inline table expression it also has the ability to access columns that are to the left of APPLY operator.
July 14, 2019 at 11:07 pm
david.holley wrote:Young man (or woman), Implicit joins are evil. Always. Even in examples or practice exercises. Repent of your wicked ways.
BWAAA-HAAAA!!!!! Someone needs to tell MS that. Properly written correlated sub-queries require them.
Maybe someone should tell ANSI and ISO too - last time I looked, implicit joins were clearly available in all the standards.
Or perhaps it would be better if they weren't told - there's enough working software out there that relies on them that eliminating them from the standard would imply a massively expensive rewrite of lots of stuff that has worked for years (some of it for decades). A rewrite will have exactly zero impact on correctness of results (unless, as is very probable, there are mistakes in the rewrites) and may or may not entail a reduction in performance (with or without mistakes in the rewrites) depending on how the DBMS software works currently.
The nonsense about the old style joins (the ones that didn't use the "join" keyword) delivering incorrect results is, I believe, pure bullshit. I was around and active in databases in those days. There may have been bugs in someone's implementation (where both the old style and the new style had to be supported) that led to different results for the two things, but that would clearly have been a bug (or several bugs) not a correct implementation of the several join types in the cases where the results should according to the SQL semantics have been the same. And of course the whole story must be based on the idea that this notation which didn't actually deliver the result it was supposed to but something else instead, somehow managed, over a long period and in spite of these (imaginary) errors, to deliver masses of good solid working software using several RDBMS products which ensured the ongoing use of RDBMS for several decades.
Tom
July 15, 2019 at 12:02 am
Heh... you and I are showing our age in this business, Lynn. Yes, they were ANSI compliant at one time... they don't usually refer to them as ANSI joins anymore, though.
You youngsters showing your age by reference to 1996 (in Lynn's post) makes me remember when I was getting old.
In 1996 I left my much-loved job at ICL that had resulted in me collaborating with Oracle on development, discussing the same but getting nowhere with Ingres and Postgres people (they werent interested in massive parallelism) and had written and had published (with my sidekick) several research papers on the issues that arose from the idea of building an RDBMS system to run on a fully declarative operating system over a cluster of up to 256 computers (Oracle was very intersted in that) as well as obtaining (for ICL, obviously) patents for deadlock detection and handling in massively parallel database systems. And RDBMS had been a rather small part of my job, as I was the senior software architect for a very large international project involving ICL, Bull, Siemens, and several smaller companies and several Universities in France, Britain, and Germany, with responsability to report software progress to our main funder (the Commission of the EU) each year and handle the software side or our progress presentation to the evaluating panel (a dozen or so people from various Academic and Industrial organisations across the EU) each year, but I had been looking at it for about 25 years (I was only 27 years old in 1971 when I first read one of Codd's papers and got hooked on the ideas) and actually writing SQL code (mostly to try on a tool that ICL developed to allow examination of very non-relational data through SQL queries, although I played quite a lot with Oracle and Ingres too) between 1987 and 1996.
So you two should feel young - because I do, despite my extra years, and feeling young is far more fun that feeling old, even for DBAs and development managers who also are DBAs (that combination is what I was from 2000 to 2009 and what I suspect Jeff is now; before that I wasmostly a development manager, but had to know a lot about databases as well as lots of other things).
Tom
July 15, 2019 at 12:28 am
Tom,
If you played any part in creating Oracle packages, thank you.
July 15, 2019 at 1:58 am
Tom, If you played any part in creating Oracle packages, thank you.
David, I don't have any idea as to whether I had any influence on that or not. My original serious (as opposed to just checking whether ot was of any interest) connection with Oracle was through Martyn Stead after he left ICL and joined Oracle, and I have no idea whehether we then actually had any influence on Oracle production (other than their fairly largely staffed effort on making their parallel sytem fit) or whether we had any influence on adopting packages.
The final product, if I remember correctlly, reached exactly two customers, both in France, who both had it in prototype form and wanted more of it (even if still in prototype form). But that didn't matter, sales in France could be ignored, so a certain pillock who claimed to be an engineer got the project cancelled so as to screw development, which (screwing development) was a primary job for all directors since only people who were either accountants or marketeers were anything other than a waste of space. (That applied by 1996 to ICL, and if I am to believe what I have heard it also applies now to Oracle, although Martyn disputed that). It was true also for evey large scale firm I've heard of in the UK and every firm (even if very small scale) in the USA.
Tom
July 15, 2019 at 11:26 am
Did your fine organization implement an ungodly number of triggers as a part of an application release? To the point that when you brought up a valid reason to implement a new trigger it was shot down.
There weren't quite that many triggers, but some of the tables did contain multiple triggers. The messiness was that some triggers fired other triggers and we had to be careful of what order they fired. As a general rule, I don't like triggers, but I understand they're necessary sometimes. The 1300-line monster was, I think, a victim of the ever-changing spec that never quite got the attention it needed. If the ERP system would have been able to process hours accounting data properly, it could have been a whole lot simpler.
July 21, 2019 at 12:38 am
Ha! Version 4.21a was my first taste. Crikey, I feel ancient. ANSI-89 style may have been a standard back in the day, but has long been supplemented. In fact it returns incomplete sets. There used to be a page in the BOL that proved it, with code samples. I have passed the code from that page to many oracle DBAs to prove to them "they are doing joins wrong" 😉 Dave J
Please pass it on to me, then. I have a written a lot of code that used those ancient-style joins. I'd like to see the evidence that they don't work, because in my experience they always did. But if (perhaps) the latest MS SQL-Server is screwing them up it may be a problem - most likely in in the DBMS (misinterpreting those old but still valid - they were never specifically dropped - forms) not in the syntax of those forms of join.
Oddly enough I still use those forms in the latest build MSSQLSrvr 2017 and the code I write works - but the recent stuff is just code I use, not code that I've written for an employer or passed on to anyone else, what I'm worried about is stuff I wrote ages ago.
Tom
Viewing 15 posts - 481 through 495 (of 511 total)
You must be logged in to reply to this topic. Login to reply