Please explain why this Select doesn't fail
DWalker
essentially doing an invalid select. Translated to the Pubs table, here's
what I was doing:
select title_id from titles
where title_id not in (select title_id from authors)
This returns 0 records. I would expect an error instead, since authors
doesn't have a title_id field.
Why doesn't this fail when it's parsed?
Thanks for any enlightenement.
David Walker
Adam Machanic
select title_id from titles
where title_id not in (select 1 from authors)
... which can be loosely translated to English:
Where the title's id is not in the set produced by returning 1 for every row
in authors.
If you use title_id instead of 1, the same happens, but you get:
Where the title's id is not in the set produced by returning the title's id
for every row in authors.
So in other words, that query can reduce to:
where title_id <> title_id
Does that make more sense?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"DWalker" <None> wrote in message
news:efjhgCW4...@TK2MSFTNGP14.phx.gbl...
Andrew J. Kelly
of that table it looks to the parent select and if it finds it there it
thinks all is well. I don't agree with it either but that is per the spec.
The way to avoid this is to always alias your tables and then it knows
exactly which table you are referencing.
--
Andrew J. Kelly SQL MVP
"DWalker" <None> wrote in message
news:efjhgCW4...@TK2MSFTNGP14.phx.gbl...
Hugo Kornelis
(snip)
> I don't agree with it either
(snip)
Hi Andrew,
Do you mean that you don't agree with the possibility to refer to tables
in the outer query from a subquery? In other words: don't you agree with
correlated subqueries?
Or do you mean that references to tables in the outer query should always
be qualified with table name or alias or else throw an error?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Andrew J. Kelly
> be qualified with table name or alias or else throw an error?
Pretty much yes. Actually I would prefer that if the column in the subquery
does not exist in the table directly referenced in the from clause of the
sub-query it should throw an error.
--
Andrew J. Kelly SQL MVP
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:72asr0ds5vulbuko0...@4ax.com...
Adam Machanic
news:%23z9drVX...@TK2MSFTNGP09.phx.gbl...
> > Or do you mean that references to tables in the outer query should
always
> > be qualified with table name or alias or else throw an error?
>
> Pretty much yes. Actually I would prefer that if the column in the
subquery
> does not exist in the table directly referenced in the from clause of the
> sub-query it should throw an error.
>
But then this wouldn't be valid:
SELECT *
FROM TblA
WHERE TblA.Col =
(SELECT TblA.ColB - TblB.ColC
FROM TblB)
Or do you mean that if the column was qualified the check wouldn't be done?
Steve Kass
Andrew J. Kelly wrote:
>>Or do you mean that references to tables in the outer query should always
>>be qualified with table name or alias or else throw an error?
>>
>>
>
>Pretty much yes. Actually I would prefer that if the column in the subquery
>does not exist in the table directly referenced in the from clause of the
>sub-query it should throw an error.
>
>
>
So are you saying you would prefer that correlated subqueries go away?
You have to refer to "outer query" columns in a subquery for it to be
correlated.
Steve Kass
Drew University
Andrew J. Kelly
>
>
> SELECT *
> FROM TblA
> WHERE TblA.Col =
> (SELECT TblA.ColB - TblB.ColC
> FROM TblB)
Since the TblB column is qualified there is no mistaking and if it was a
typo it would error. All I am saying is that the columns from the outer
table should be required to be qualified with the table name or alias. And
any unqualified columns (only in the subquery) should only be directly
attributed to only tables that were addressed in the From clause of the
subquery. That way if there was a typo it would error instead of assuming
the outer table is the owner.
--
Andrew J. Kelly SQL MVP
"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:u3r$1AY4EH...@TK2MSFTNGP12.phx.gbl...
Andrew J. Kelly
> You have to refer to "outer query" columns in a subquery for it to be
> correlated.
No, no, no. I am not saying that at all. How did this all go so
wrong<g>...
Like I said to Adam. All I am saying is that the columns from the outer
table should be required to be qualified with the table name or alias. And
any unqualified columns (only in the subquery) should only be directly
attributed to only tables that were addressed in the From clause of the
subquery. That way if there was a typo it would error instead of assuming
the outer table is the owner. This has nothing to do with Subqueries per say
just that this bites many people all the time and requiring the table
qualification would still allow subqueries but error if the coder goofed.
--
Andrew J. Kelly SQL MVP
"Steve Kass" <sk...@drew.edu> wrote in message
news:OscbjbY4...@TK2MSFTNGP09.phx.gbl...
Louis Davidson
warnings for. It is pretty obvious that if there is a column in the output
of the correlated query that does not exist in any of the tables that it is
"probably" a problem. I agree that it cannot be considered an error for the
reasons given (I believe I started this conversation the last time it came
around, unless I just missed it :)
--
----------------------------------------------------------------------------
Louis Davidson - dr...@hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Steve Kass" <sk...@drew.edu> wrote in message
news:OscbjbY4...@TK2MSFTNGP09.phx.gbl...
>
>
Steve Kass
it! I agree that this bites a lot of people. Short of requiring the
alias for possibly
ambiguous column names, which would break tons of existing code, a code
checker like Unix "lint" that would identify these situations would be
useful. I've
made this mistake myself and it's not very funny to find out why things
have gone
awry.
SK
Hugo Kornelis
>> Or do you mean that references to tables in the outer query should always
>> be qualified with table name or alias or else throw an error?
>
>Pretty much yes. Actually I would prefer that if the column in the subquery
>does not exist in the table directly referenced in the from clause of the
>sub-query it should throw an error.
Hi Andrew,
Yeah, I think that this requirement would prevent lots of foolish
mistakes. Though I also have to agree with Steve's point, that this would
break too much existing code (not mine though - I always prefix ALL
columns as soon as more than one table is used in a query)
On the other hand - this change would fix one potential source of errors,
while leaving others open. Maybe we'll just have to accept the simple fact
that SQL is NOT a simple language that one can master after a one-week
crash course. The same power and flexibility that allows one to work true
miracles with SQL also allows one to goof up big time.
Andrew J. Kelly
wouldn't be an issue now. But I do think Steve is right in that the
compiler should at least warn of a potential problem.
--
Andrew J. Kelly SQL MVP
"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:2oatr0pis8dle29u8...@4ax.com...
Hugo Kornelis
>Yes, this is a change that should have been made a long time ago so this
>wouldn't be an issue now. But I do think Steve is right in that the
>compiler should at least warn of a potential problem.
Hi Andrew,
I guess we're in full agreement, then.
DWalker
Ah, it thinks I'm trying to do a correlated subquery.
Thanks for all the responses, that certainly clears things up. I'll
have to be more careful. And yes, you probably should be required to
qualify the outer table name in a correlated subquery, but that can't be
changed now.....
Thanks again. This newsgroup is VERY helpful.
David Walker