SubQuery with IN - NO Field Still NO Error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SubQuery with IN - NO Field Still NO Error

Author

Topic

Deepak1983
Starting Member

23 Posts

Posted - 2010-10-01 : 07:04:52

Please check the below query and let me know when there is no such field into the table of subquery which this doesnt gives me error.

Create table #Temp (KeySubAccount int primary key, Name varchar(max))
Create table #Temp2 (KeySub int primary key, Name varchar(max))

SELECT * FROM #Temp2 where keysub in
(SELECT keysub FROM #Temp)

drop table #Temp
drop table #Temp2

There is No KeySub in #Temp then why the query dosent gives me error... LET ME KNOW PLZ

Deepak Arora

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-01 : 07:20:56

Because if there is no data that isn't an ERROR.
The statement is ok but it is not retrieving any data.

No, you're never too old to Yak'n'Roll if you're too young to die.

Deepak1983
Starting Member

23 Posts

Posted - 2010-10-01 : 07:24:31

The problem is not that DATA is not there.. problem is .. Even when Field is not there in Subquery table... Still it doesnt give me any Error. Why ??

if you run SubQuery alone !!!
SELECT keysub FROM #Temp - you will get Error
but if you run it making it subquery like given below
SELECT * FROM #Temp2 where keysub in
(SELECT keysub FROM #Temp) -- This won't give me any error.

Deepak Arora

Sachin.Nand

2937 Posts

Posted - 2010-10-01 : 07:25:54

It is a bit different when it somes to subqueries.SQL always implicitly qualifies to a existing column in the outer query if the column does not exists in the inner query without throwing any error.
It happens to all the subquery clauses not just IN like 'Not IN','Exists','Any','All' etc..

PBUH

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-01 : 07:27:58

quote:
Originally posted by webfred

Because if there is no data that isn't an ERROR.
The statement is ok but it is not retrieving any data.

No, you're never too old to Yak'n'Roll if you're too young to die.

no even with the data the sql doesn't error.



Create table #Temp (KeySubAccount int primary key, Name varchar(max))
Create table #Temp2 (KeySub int primary key, Name varchar(max))

INSERT #temp2 SELECT 1, 'a' UNION SELECT 5, '45'
INSERT #temp SELECT 2, 'b' UNION SELECT 1, 'a'

SELECT * FROM #Temp2 where keysub in 
(SELECT keysub FROM #Temp)

drop table #Temp
drop table #Temp2

I thought it was a data thing at first as well but it's just weird.

It's also not a temp table thing as this still works OK



Create table Tempa (KeySubAccount int primary key, Name varchar(max))
Create table Tempa2 (KeySub int primary key, Name varchar(max))

INSERT tempa2 SELECT 1, 'a' UNION SELECT 5, '45'
INSERT tempa SELECT 2, 'b' UNION SELECT 1, 'a'

SELECT * FROM Tempa2 where keysub in 
(SELECT keysub FROM Tempa)

drop table Tempa
drop table Tempa2

I didn't know the IN statement did this.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Deepak1983
Starting Member

23 Posts

Posted - 2010-10-01 : 07:29:33

It will be good if you can throw little more light to the subject.. I didnt get those two lines.. It will be helpfull if you provide me with some link to the website from where i could get little more explanation to this topic..

Deepak Arora

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-01 : 07:32:32

Now I understand the problem.
What a mess!

No, you're never too old to Yak'n'Roll if you're too young to die.

Sachin.Nand

2937 Posts

Posted - 2010-10-01 : 07:33:06

quote:
Originally posted by Deepak1983

It will be good if you can throw little more light to the subject.. I didnt get those two lines.. It will be helpfull if you provide me with some link to the website from where i could get little more explanation to this topic..

Deepak Arora

Did you read my reply?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=150951#593146

PBUH

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-01 : 07:35:01

quote:
Originally posted by Sachin.Nand

It is a bit different when it somes to subqueries.SQL always implicitly qualifies to a existing column in the outer query if the column does not exists in the inner query without throwing any error.
It happens to all the subquery clauses not just IN like 'Not IN','Exists','Any','All' etc..

PBUH

Not sure I understand 100% what you are saying but with a little testing I guess what you are saying is:

If the column referenced in the inner query (in this case KeySub) IS in the table referenced in the outer query (Temp2) then the code will run (but will give you meaningless results)

If you reference a column that doesn't exist in either of the tables a syntax error is generated.


Create table Tempa (KeySubAccount int primary key, Name varchar(max))
Create table Tempa2 (KeySub int primary key, Name varchar(max))

INSERT tempa2 SELECT 1, 'a' UNION SELECT 5, '45'
INSERT tempa SELECT 2, 'b' UNION SELECT 1, 'a'

SELECT * FROM Tempa2 where EXISTS 
(SELECT foo FROM Tempa)

drop table Tempa
drop table Tempa2

Produced the expected:


Msg 207, Level 16, State 1, Line 7
Invalid column name 'foo'.

Whether this is a bug or a feature I'm not sure.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sachin.Nand

2937 Posts

Posted - 2010-10-01 : 07:43:44

Yes that's what I am saying.
If the column does not exists in inner query & the inner table but exists in the outer query table & the select statement of the outer query the query


SELECT * FROM #Temp2 where keysub in 
(SELECT keysub FROM #Temp)

qualifies to a simple


SELECT * FROM #Temp2

PBUH

Deepak1983
Starting Member

23 Posts

Posted - 2010-10-01 : 08:30:03

Whatever we Search in subquery should be the part of the outerquery ...

FOR Example
Create table #Temp (KeySubAccount int primary key, First varchar(max))
Create table #Temp2 (KeySub int primary key, Name varchar(max))

--Instead of KeySub if I use "First" which is not part of #Temp,
--which is actually part of #Temp2 .. The query will not give
--anyerror
Create table #Temp (KeySubAccount int primary key, Name varchar(max))
Create table #Temp2 (KeySub int primary key, First varchar(max))

SELECT * FROM #Temp2 where First in
(SELECT KeysubAccount FROM #Temp)

drop table #Temp
drop table #Temp2

One of my friend expalined me that this query doesnt give any error but it give some kind of "warning".. which he figured out checking Execution plan. Can anybody find out using Execution plan what exactly the error is.. or bug.. ???

Deepak Arora

Sachin.Nand

2937 Posts

Posted - 2010-10-01 : 08:35:33

OMG...
Seems all the above discussion has gone in vain.Please see what Microsoft has to say

http://msdn.microsoft.com/en-us/library/ms178050.aspx

PBUH

Deepak1983
Starting Member

23 Posts

Posted - 2010-10-01 : 08:45:35

Thanks Sachin... Thanks for providing me the link... !!!!

Link :- http://msdn.microsoft.com/en-us/library/ms178050.aspx

Satisfied..

Deepak Arora

Deepak1983
Starting Member

23 Posts

Posted - 2010-10-01 : 08:49:12

Imp:- This problem can be sloved using Alias...

Create table #Temp (KeySubAccount int primary key, Name varchar(max))
Create table #Temp2 (KeySub int primary key, First varchar(max))

SELECT * FROM #Temp2 T where T.Keysub in
(SELECT T2.Keysub FROM #Temp T2)

drop table #Temp
drop table #Temp2

This will give Error..
Check out.. !!!!!!

Deepak Arora

Sachin.Nand

2937 Posts

Posted - 2010-10-01 : 08:58:10

quote:
Originally posted by Deepak1983

Imp:- This problem can be sloved using Alias...

Create table #Temp (KeySubAccount int primary key, Name varchar(max))
Create table #Temp2 (KeySub int primary key, First varchar(max))

SELECT * FROM #Temp2 T where T.Keysub in
(SELECT T2.Keysub FROM #Temp T2)

drop table #Temp
drop table #Temp2

This will give Error..
Check out.. !!!!!!

Deepak Arora

The reason is you are forcing the parser to go & look for column Keysub in table #Temp which does not exists in #Temp.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources