Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

TSQL question - Expand / Collapse
Author
Message
Posted Thursday, December 13, 2012 12:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 4:38 AM
Points: 2, Visits: 72
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??
Post #1396015
Posted Thursday, December 13, 2012 1:02 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1396028
Posted Thursday, December 13, 2012 1:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 952, Visits: 2,625
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
Post #1396040
Posted Thursday, December 13, 2012 1:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1396043
Posted Thursday, December 13, 2012 1:27 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 952, Visits: 2,625
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
Post #1396046
Posted Thursday, December 13, 2012 4:02 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 3:54 AM
Points: 616, Visits: 3,580
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.
Post #1396101
Posted Thursday, December 13, 2012 4:28 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:00 AM
Points: 40,610, Visits: 37,074
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 2008, MVP
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

Post #1396110
Posted Thursday, December 13, 2012 4:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 21, 2014 4:38 AM
Points: 2, Visits: 72
Thank you ..
Post #1396117
Posted Thursday, December 13, 2012 6:00 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 952, Visits: 2,625
Thanks for the explanation Gail, I can sort of understand that now.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1396143
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse