May 12, 2006 at 11:57 am
I was going through a list of 'best practices' that I would use to gently suggest to my developers how they should be writing SQL correctly. 😉
I found this on Joins - Below this section there is a question from the developer for clarificaiton
Is this just to be ANSI-Standard or is there also a performance reason?
FROM titles t, authors a, titleauthor ta
WHERE
a.au_id = ta.au_id AND
ta.title_id = t.title_id AND
t.title LIKE '%Computer%'
SELECT a.au_id, t.title
FROM authors a
INNER JOIN
titleauthor ta
ON
a.au_id = ta.au_id
INNER JOIN
titles t
ON
ta.title_id = t.title_id
WHERE t.title LIKE '%Computer%'
I’m not sure that I described the other question clearly:
(Scenario 1)
JOIN ON (a.FieldID = b.FieldID AND b.Sold=1)
VS
(Scenario 2)
JOIN ON a.FieldID = b.FieldID
…
WHERE b.Sold=1
So, you are saying that the join conditions (b.Sold=1) should be in the WHERE clause.
But doesn’t it seem logical that the scenario 1 join would return a smaller result set for the WHERE clause to filter and scale better? Especially if you have multiple table joins?
May 12, 2006 at 12:46 pm
That is the fun part.
With this
(Scenario 1)
JOIN ON (a.FieldID = b.FieldID AND b.Sold=1)
VS
(Scenario 2)
JOIN ON a.FieldID = b.FieldID
…
WHERE b.Sold=1
If it is an Inner Join it makes no difference if in the ON or the WHERE,
If were a LEFT JOIN of A LEFT JOIN B then it should be in the ON or your net effect is an INNER JOIN and not a LEFT JOIN.
If it were a RIGHT JOIN of A RIGHT JOIN B then you have to decide.
So it all depends on what JOIN you are using.
May 12, 2006 at 1:24 pm
From a purely best practices point of view, pick one and ask the the team to stick with it. That way everybody gets used to reading and coding the same style. Team code reviews are much easier. My experience is that using this one,
(Scenario 2)
JOIN ON a.FieldID = b.FieldID
WHERE b.Sold=1
is easier to read. If you have a large join with lots of tables then all of your filters are together after the WHERE. It's easier to trouble shoot rather than reading through each ON portion and finding the filters. Just my opinion.
Mark
May 12, 2006 at 2:28 pm
OUTER JOIN default mathematically to a NULL so there is a fixed table position and you need the ISNULL function to get expected result. The WHERE clause is only a filter since 2000 a JOIN uses the FROM and ON clause and when both are used the query processor ignores the WHERE clause. The AND operator is also used for additional search condition. Check SQL performance book for more detail. Hope this helps.
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
July 28, 2006 at 12:33 pm
The only time I use the JOIN syntax of the ANSI-92 standard is in OUTER JOINs. Personally, I don't think that LEFT and RIGHT join syntax is very clear. I've been coding in SQL since SQL Server v4.2, 10+years ago, and I'm very comfortable with the old syntax and it's more readable to me. There is no LEFT or RIGHT table in my point of view.
Example:
Select
tbl1.col1,
tbl2.col2 ......
From
tbl1,
tbl2,
tbl3,
tbl4
Where
........
Which table is on the Right and which is on the Left ? I really see Top and Bottom or 1st and 2nd etc. Unless they force the formatting, there is no real Left and Right.
Also if I switch the physical positioning of tbl1 and tbl2, the LEFT and RIGHT syntax becomes invalid. Using a where clause join it should always be valid.
Maybe it's just an old dog learning new tricks, but they should have created the standard in a better way.
July 31, 2006 at 8:32 am
I agree with what Antares and Mark posted; just adding a few words to it. Core of the developer's question is :
But doesn’t it seem logical that the scenario 1 join would return a smaller result set for the WHERE clause to filter and scale better?
As far as I know, no. Optimizer takes all the conditions from JOINs and WHERE and produces a plan how to do it best. For performance, it does not matter where you put which condition. It does not work the way you write it, you can not specify what will be done first and what later. Of course, ordering and grouping is separate from the filtering etc., but that's not the question.
Just take these 2 queries (scenario 1 and 2) and let QA produce the estimated execution plan. It should be the same for both scenarios.
July 31, 2006 at 9:19 am
Microsoft specifically states that left and right outer Joins are not supported and in some cases “results in an ambiguous query that can be interpreted in more than one way.” (Microsoft SQL Server Books On Line : Transact_SQL Joins).
If you are in the process of setting standards, I would recommend using explicit joins rather than where clauses. That way you do not have any issues with left and right joins down the line and everyone is coding the same...
I wasn't born stupid - I had to study.
August 1, 2006 at 1:22 am
Just to avoid confusion - what is not supported are only old syntax left and right joins:
"In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way." (BOL)
Standard LEFT and RIGHT joins with conditions specified in the join are supported.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply