May 14, 2008 at 10:22 am
Steve Jones - Editor (5/14/2008)
I've seen queries similar, and people would write them. The fact that it compiles is a bad thing in my mind.Someone submitted this and I think it's a good tickler to let people know that there might be developers that would write this. Perhaps not intentionally, but once it worked, they might keep writing.
The fact you wouldn't allow it doesn't mean that you shouldn't know this behavior, while buggy and perhaps not desired, is available.
I agree that it's important to be aware that this type of query does compile and return values. The discussion that this question generated and the knowledge discovered and shared are what makes this so worthwhile. Unfortunately, not everyone who looks at the Question of the Day or Articles realizes the value of reading the comments.
May 14, 2008 at 12:00 pm
My gut feeling is that it's not valid syntax (so you won't find it in BOL), but somehow SQL Server managed to parse it into an executable query.
I'd love to know if anyone finds any documentation on it -- I would guess under selects in where clauses.
May 14, 2008 at 12:10 pm
Well, I checked BOL for both WHERE clause syntax and SELECT subquery, and neither shows this "feature".
Under the WHERE clause syntax, it simply shows the option to use a (subquery) , a couple variations, like EXISTS (subquery), which to me, implies a syntactically-correct subquery.
May 14, 2008 at 12:12 pm
ezlikesundaymorning (5/14/2008)
OK, just plain and simple, I am going to give my 2 cents worth here. This question is pure BS. I mean has anyone here ever seen a query written like this? In my house(place of business) the person who tried to get this through me, would be ridiculed beyond belief. I mean really, did I get the question wrong, yep. Did I also think that the question was misleading, yep. Do I know a developer that would try to get this past me, nope. Lets get some real questions in here. First off I do not even let anyone write in ANSI 89 SQL. Plain and simple it is my way or the highway.
I really appreciate this comment.
I understand, you had not come across with such question ever before. But best part would have been if you have thought some thing different than usual stuff.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
May 14, 2008 at 12:23 pm
Carla, you are right. there is no reference in BOL.
Its just a way of writing query that does not return error but does return data.
[font="Verdana"]--www.sqlvillage.com[/size][/font]
May 14, 2008 at 12:50 pm
Hmmm... I dug a little deeper in BOL and found something interesting.
On the main "clauses" page under "SELECT", it shows that the FROM clause is a required clause with almost everything else optional.
But at the bottom of that page is a link to a more complete syntax of the SELECT statement.
(apologies if these links don't work -- I've never tried this before)
This syntax includes the WITH "CTE" syntax, and on this page, the SELECT statement shows the FROM clause as optional! Perhaps when CTEs were introduced in SQL 2005, this changed the "rules".
May 15, 2008 at 1:55 am
Jason Miller (5/14/2008)
Syntactically correct, but it doesn't produce meaningful results. :hehe:I agree with the previous posts, it compiles and executes, but what is it trying to provide?
Listen to me, I try to learn something new everyday and that is the reason why I answer these questions but sorry, today I learned nothing. Who writes queries like this?!:hehe::hehe::hehe::hehe::hehe:
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
May 15, 2008 at 4:12 am
Carla Wilson (5/14/2008)
This syntax includes the WITH "CTE" syntax, and on this page, the SELECT statement shows the FROM clause as optional! Perhaps when CTEs were introduced in SQL 2005, this changed the "rules".
The FROM clause is definitely optional since otherwiseSELECT getdate()or (slightly more usefully);with number as (
select 1 as n union all
select 3)
select n from numberwould fail to parse. What isn't clear is how the parser processes field references in a subquery without a FROM clause. As the execution plan shows, it's clearly not obvious and my reaction would be always to include the FROM clause both to make the query clear and so that the behaviour was predictable (and explicable :)).
Searching further, I found the explanation of the FROM clause, SS2K5 or SS2K8, both state
Specifies the tables, views, derived tables, and joined tables used in DELETE, SELECT, and UPDATE statements. In the SELECT statement, the FROM clause is required except when the select list contains only constants, variables, and arithmetic expressions (no column names).
So the query given, while it parses and runs, is supposed to be invalid syntax, but somehow slips through.
The topic "Subquery Fundamentals" for SS2K, SS2K5 and SS2K8 all state
A subquery nested in the outer SELECT statement has the following components:
A regular SELECT query including the regular select list components.
A regular FROM clause including one or more table or view names.
An optional WHERE clause.
An optional GROUP BY clause.
An optional HAVING clause.
So the requirement to have a FROM clause hasn't changed recently!
Maybe it ought to be logged as a bug with the workaround "don't do this!" 🙂
Derek
May 15, 2008 at 8:13 am
I'm the DBA for a large medical system in the midwest and we purchase many of our solutions versus doing in-house builds. While I'd flogg my developers for coding in this manner I'll tell you right now that I've seen this query structure in play quite a bit more than I'd like to coming from the commercial side. It's quite amazing how many decent app developers out there still fumble the ball when it comes to DB calls and coding. Arg! Good question from the standpoint that (whether good or bad) this stuff is still floating around out there.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
May 15, 2008 at 1:11 pm
One problem I see with the question is that it does not specify the requried version or database to be used. The fact that the results are nonsense and the answer does not reference this fact leads me to put this into the poor question category. Fortunately since this question is similar to one we had in the past I was able to answer it without trying to run it.
-- Mark D Powell --
May 17, 2008 at 11:59 pm
Hi there Gurus!
I thought I was a naive beyond imagination 'cos I completely missed the purpose of that query!
Looks like something framed on a particularly boring afternoon 🙂
We need better than that if we are to learn!
The naïveté
May 20, 2008 at 4:14 am
Hi There,
I agree that the query would work, but only on the premise that only one of the tables had the column customerid. To say that any of the two tables could have the customerid would require having an alias to resolve the column.
My 2 cents
🙂
Regards,
WilliamD
May 23, 2008 at 8:56 am
I don't think that the fact that the query compiles is a bug per se. It compiles because subqueries must have access to their parent in order to create correlated queries. The compiler can't assume what that correlation is going to look like so if it can resolve the name it will. Think of:
select Count(*)
FROM ClientTable
where Exists (SELECT * FROM OrderTable where PKClientID = FKClientID)
Now, while I consider it bad to have different names for the same information, I 've seen this type of design. The Compiler must be able to compile the above because it is valid. Therefore it also must be able to compile the syntactically correct, but bizarre:
select * from ClientTable, Contracts
Where clientid in (select contractclientid where overdue > 30)
As far as the compiler is concerned as long as the names contractclientid and overdue are resolvable then it is a syntactically valid statement.
--
JimFive
July 8, 2008 at 5:27 am
Query
What will happen if following query get executed:
select CategoryID, Quantity, Price
from Customer c, SalesOrderDetail sod
where sod.clientid in (select customerid where pricingplan='X')
In this Query options nothing was spoken about pricingplan column.
If pricingplan column is not part of either of the tables then the query will end up in Error. Why not the Third option is right answer for this Question in this scenario?? 🙂
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply