SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query


Query

Author
Message
Julie Breutzmann
Julie Breutzmann
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1643 Visits: 1949
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.
Carla Wilson-484785
Carla Wilson-484785
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2270 Visits: 1951
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.
Carla Wilson-484785
Carla Wilson-484785
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2270 Visits: 1951
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.
Mohan Kumar
Mohan  Kumar
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2145 Visits: 596
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.

--www.sqlvillage.com
Mohan Kumar
Mohan  Kumar
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2145 Visits: 596
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.

--www.sqlvillage.com
Carla Wilson-484785
Carla Wilson-484785
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2270 Visits: 1951
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.
[/url]


But at the bottom of that page is a link to a more complete syntax of the SELECT statement.
[url=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/dc85caea-54d1-49af-b166-f3aa2f3a93d0.htm]
(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".
Manie Verster
Manie Verster
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2255 Visits: 1030
Jason Miller (5/14/2008)
Syntactically correct, but it doesn't produce meaningful results. HeheI 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?!HeheHeheHeheHeheHehe

:-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)
StarNamer
StarNamer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2696 Visits: 1992
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 otherwise
SELECT getdate()

or (slightly more usefully)
;with number as (
select 1 as n union all
select 3)
select n from number

would 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 Smile).

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!" Smile

Derek
Timothy Ford-473880
Timothy Ford-473880
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1623 Visits: 446
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 MVP
http://www.sqlcruise.com
http://www.thesqlagentman.com
http://www.linkedin.com/in/timothyford
Mark D Powell
Mark D Powell
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2117 Visits: 466
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 --
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search