July 10, 2008 at 8:00 am
I'm trying to figure out why I get errors for this SQL statement. I've really stripped it down in my troubleshooting.
select c.date
from spdqty as c
inner join (select date, vialsOUT from spdqty as a
inner join select date, vialsIN from spdqty as b
on a.date = b.date)
on c.date = a.date
ERRORS!
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'select'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'on'.
I can't figure out what the sequence of execution is.
TFTH
July 10, 2008 at 8:28 am
I think you're looking for something like this:
select c.date
from spdqty as c
inner join (select date, vialsOUT from spdqty) as a
on c.date = a.date
inner join (select date, vialsIN from spdqty) as b
on a.date = b.date
But you know, you don't have to do those as sub-selects since you're not manipulating that data in any way. You could simply:
select c.date
from spdqty as c
inner join spdqty as a
on c.date = a.date
inner join spdqty as b
on a.date = b.date
And just get a.vialsOUT and b.vialsIN if those are the data fields you're looking for. However, based on this sample, there's nothing differentiating the data in a, b, and c.
"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
July 10, 2008 at 10:46 am
Hi,
Looks like you are very new to this joins, Instead of starting directly working the SQL ANSI joins, it would be nice to practicse on simple joins and later get familiar with SQL ANSI joins.
select c.date
from spdqty c, spdqty a, spdqty b
WHERE c.date = a.date
AND a.date = b.date
This is simplest and easiest way of writing the queries.
Thanks -- Vj
July 10, 2008 at 11:26 am
I sure don't mean to start a fight with my next statement....
Please don't do that. Learn to use the ANSI joins. There are a ton of good reasons. I'll give you three. First, it is the ANSI standard methodology that you'll see in all the books, on all the web posts, etc. It's best you understand it. Second, query processing order causes data to be limited first by join criteria and second by the WHERE clause. If you put all the criteria in the WHERE clause, you can see more data being manipulated in the query (bad execution plans) than otherwise. Third, when you get into outer joins, the old ANSI-89 syntax, *=, it's deprecated in 2005.
Learn the ANSI standard. It's not that hard to understand. If anything, it's easier since the tables being joined and the definition can be placed right next to each other.
"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
July 10, 2008 at 12:27 pm
I'm going to have to agree with Grant here. I think that the ANSI joins are not only a better place to start, but they are easier to read. When I read an ANSI join, I know right away what type of join it is and how it is being joined. The old style joins are not so. You are forced to wade through the where clause to see how if it is a INNER, LEFT OUTER, RIGHT OUTER, etc. join. Not to mention the bad execution plan possibility.
ANSI joins are not so difficult that they need to be baby stepped into. There is ample documentation in BOL on how they work.
July 10, 2008 at 12:38 pm
I too must agree with Grant... ANSI-92+ join syntax is easier to understand and much better in terms of self-documenting.
July 10, 2008 at 12:48 pm
There's an "Anti-Rbar Alliance"?
How do you join? How much are the dues? Does the initiation involve sheep or goats?
"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
July 10, 2008 at 12:53 pm
Grant, I would say that you are already a member.
Mark, that's great! How did you get Jeff's picture? Ha, he'll get a kick out of that!
July 10, 2008 at 1:12 pm
Grant Fritchey (7/10/2008)
[...]Second, query processing order causes data to be limited first by join criteria and second by the WHERE clause. If you put all the criteria in the WHERE clause, you can see more data being manipulated in the query (bad execution plans) than otherwise[...]
This is not what happens in reality. While the logical order of processing queries would have you do all the joins first and then start filtering out anything that isn't true for the WHERE clause, the actual physical processing will result in the same execution plan whether you put the filter in the ON clause or the WHERE clause. This makes sense if you imagine doing the join manually. If you were joining table A and table B on A.col1 = B.col1 where A.col2 < 10, why would you want to go to the bother of joining rows in table A where col2 >= 10, when you know you're just going to filter them out in the WHERE clause? You should save yourself (# of rows in B) * (# of rows in A where col2 >= 10) joins by filtering A before apply the join. That said, you need to be careful with outer joins, as putting the filter in the ON clause or in the WHERE clause for an outer join will not only change the execution plan, but also the output from the query. If you're doing an outer join, the ON filter will be applied before the outer rows are added back, so any rows filtered by the ON clause will still be present in the query's output, while any rows filtered by the WHERE clause will be gone for good.
Edit: Hmm, the forum software doesn't appear to filter less thans and greater thans. That's surely an incredible security problem?
July 10, 2008 at 1:23 pm
I'm going to start digging, but I've had instances where moving filtration from the WHERE clause to the ON clause does change the execution plan. I don't have the example at hand, but it does happen. I'll get back to this as soon as I can.
"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
July 10, 2008 at 1:23 pm
Grant Fritchey (7/10/2008)
There's an "Anti-Rbar Alliance"?How do you join? How much are the dues? Does the initiation involve sheep or goats?
You join by fighting it in the streets... in the developer pits... wherever you find it and squash it...
🙂 After talking with Jeff, I agreed that I didn't want to just use his logo... so we came up with a neat compromise. 😎
July 10, 2008 at 1:29 pm
Grant Fritchey (7/10/2008)
I'm going to start digging, but I've had instances where moving filtration from the WHERE clause to the ON clause does change the execution plan. I don't have the example at hand, but it does happen. I'll get back to this as soon as I can.
It shouldn't... the query optimizer should convert them between each other. Provided the =/*=/=* mish mash is written to be equivalent to INNER/LEFT/RIGHT JOIN statements.
I find the =/*=/=* garbage to be just that... garbage. LEFT/RIGHT/INNER type joins are easier to read and understand when looking at the code later. When you have to figure what's going on when 25 tables are joined together using 2-3 criteria per join, reading all the = signs just gives you a headache.
The two big reasons to not use the =/=*/*= junk are readability of code, and the fact that the pre-92 join syntax is deprecated in 2005, and hopefully will be done away with one day.
July 10, 2008 at 1:59 pm
I probably won't have the example, assuming I can find it, until tomorrow. I'm looking though.
I did find this in the BOL
This sequence can sometimes influence the results of the query if conditions are moved between the FROM and WHERE clauses.
I know I've seen it, but I'm stuck to find the example now. If I can't find it, I'll throw myself on my sword and apologize for creating confusion (not a unique event).
I've enlisted a couple of co-workers who also remember this working. One them said, and it jives with some of the other reading I've done, that changing the order of the criteria from ON to WHERE, in more complex queries, can affect how the optimizer chooses to deal with the query.
I promise, tomorrow, an example or a refutation and apology.
Here's a (feeble) example from the net
http://bytes.com/forum/thread584060.html
"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
July 10, 2008 at 2:12 pm
Grant Fritchey (7/10/2008)
I promise, tomorrow, an example or a refutation and apology.
I do remember it having a big impact on performance in SQL 7... though I never could figure out why... but the issues seemed to resolve themselves circa SQL 2000.
July 10, 2008 at 2:32 pm
From my experience, queries indeed sometimes have different execution plans when switching between ON and WHERE clause.
But I am pretty sure that there is no preference for one or the other.
The difference seems to appear when you have complex queries where the optimizer has to stop before an optimal plan was found and needs to stick with less good plan. The point where the optimizer stops is very likely different for both and therefore you will have the different execution plans.
I sometimes add additional WHERE clauses or ON conditions which should not be necessary. As an example: A.col1 = B.col1 AND A.col1 = 'valueX' AND B.col1 = 'valueX' (if part of a really complex query)
The last condition should not be necessary, but sometimes helps to use the index on hypothetical table B.
None of this is backed by evidence. It is just an assumption which is based on my experience.
Best Regards,
Chris Büttner
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply