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


TSQL question -


TSQL question -

Author
Message
Jayasimha Manchenahally
Jayasimha Manchenahally
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 96
I have a question for which I want to know how it works internally

Lets consider below query

Select * from Student where student_id in(Select student_id from emp)


1. When we run "Select student_id from emp" it will display an error msg "invalid column"
2 when we run complete query it will populate all the records from Student.

How it works internally??
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28271 Visits: 13268
If you look at the actual execution plan, you'll see that the nested loops (left semi join) has a warning sign. It says: "no join predicate". What basically means the execution plan is performing a join without predicate, resulting in a crossjoin or in other words, returning all the data.

Not sure why the query doesn't error out or doesn't return no rows (which would make more sense).


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2165 Visits: 3232
I'm curious as well it seems that if the column is in the outer Table it allows the IN (SELECT) to work

eg


CREATE TABLE #Tmp (
MyColumn Int Identity(1,1),
MyDescription varchar(100)
)

Create Table #Tmp2 (
ACol int
)

Insert into #Tmp
Values ('Hello'),('World'),('Mom')

Insert into #Tmp2
Values (1),(2),(3),(4)


Select MyColumn
from #Tmp
Where MyColumn In (Select MyColumn from #Tmp2)

Select MyColumn
from #Tmp
Where MyColumn In (Select xyz from #Tmp2)



The first select runs the second Errors as xyz isnt in either table, Can anyone explain if this is working as intended.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28271 Visits: 13268
Maybe SQL Server thinks you're trying to write a correlated subquery. Since there's no WHERE clause, you'll get the "no join predicate" warning.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2165 Visits: 3232
Correct you do get a No predicate join warning on the join in the Execution plan, and the predicate on the #tmp table shows as tempdb.dbo.#tmp.myColumn=tempdb.dbo.#tmp.myColumn.

It seems SQL is trying to be clever, though I'd probably want it to throw an error if the column didnt exist in the sub query, but thats me.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
davidandrews13
davidandrews13
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1078 Visits: 4542
isn't it just looking for the student_id column, not finding it in the emp table, so its taking it from the student table?

using table alias's would ensure the correct column is being used/not used.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90921 Visits: 45284
davidandrews13 (12/13/2012)
isn't it just looking for the student_id column, not finding it in the emp table, so its taking it from the student table?


Exactly what it is doing.

It is perfectly, completely valid for a subquery to reference a table in the outer query. The resolution is first to the tables within the subquery, if no table within the subquery has that column, the tables in the outer query are then checked. Since one of those columns matches the name, the query is completely valid.

You cannot have SQL throw errors if the column is not in the tables in the subquery, if that happened every single correlated subquery (eg EXISTS subqueries) would be invalid.

This is perfectly legal and must be perfectly legal


CREATE TABLE Table1 (Col1 INT);
CREATE TABLE Table2 (Col2 INT);

SELECT Col1 FROM Table1 WHERE EXISTS (select 1 FROM Table2 where col2 = col1) -- col1 in Table 1, Col2 in table 2



If you don't like unexpected surprises when you make a mistake with an IN, then follow recommended practices and always qualify your column names.

This would return the expected error

Select * from Student where Student.student_id in (Select emp.student_id from emp)



Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jayasimha Manchenahally
Jayasimha Manchenahally
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 96
Thank you ..
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2165 Visits: 3232
Thanks for the explanation Gail, I can sort of understand that now.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
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