December 13, 2012 at 12:33 am
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??
December 13, 2012 at 1:02 am
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).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 13, 2012 at 1:18 am
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
December 13, 2012 at 1:22 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 13, 2012 at 1:27 am
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
December 13, 2012 at 4:02 am
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.
December 13, 2012 at 4:28 am
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
December 13, 2012 at 4:34 am
Thank you ..
December 13, 2012 at 6:00 am
Thanks for the explanation Gail, I can sort of understand that now.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply