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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-07-06 : 12:06:50
|
| rad writes "I have two tables. Table1's field structure is PID_pk, Name, Address.Table2 has PID_fk, Acct_no, DeptWhat should be the output of "Select * from table1 where PID_pk in (select PID_pk from table2)" Should it generate an error or not?How is this query parsed in SQL?Thanks" |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-07-06 : 12:13:35
|
quote:
Select * from table1 where PID_pk in (select PID_pk from table2)
error... the column doesn't exits in table2tryquote:
Select * from table1 where PID_pk in (select PID_fk from table2)
Corey |
 |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2004-07-06 : 12:18:51
|
| It shouldn't generate an error, but it'd be nice if SQL Server gave a warning!The PID_pk in the correlated subquery is that in Table1. So, if Table2 contains any rows, it will return all rows of Table1 where PID_pk is non-NULL. If Table2 is empty it will return no rows.You can see from the execution plan that something's a bit strange: you'll get a Row Count Spool from Table2 and then a Semi Join because it only needs to check that there are some rows in Table2. |
|
|
|
|
|
|
parsing of "Where in " clause in select Query