SQL IN statement

Question

Possible Duplicate:
sql server 2008 management studio not checking the syntax of my query
T-SQL Deletes all rows from a table when subquery is malformed

Please see the query below:

select * from tablea where reference in (
select reference from tableb)

The column reference does not exist in tableb, so I expected to see a an error, however instead all the rows from table a are returned.

Why are all the rows from tablea returned?

Should be no rows returned with `NOT IN`. [Are you sure you didn't use `IN`?](http://stackoverflow.com/q/4594733/73226) — Martin Smith, Jan 23 '12 at 16:54
@Martin, sorry yes I meant 'in'. The comments appeared before I noticed and had chance to change. Surely there should be an error as 'reference' does not exist in tableb? — w0051977, Jan 23 '12 at 16:57
No. because then it just becomes a correlated sub query taking the value from the outer query. — Martin Smith, Jan 23 '12 at 16:57
@w0051977 - if you don't table qualify your columns and there's no naming conflict, you can have issues like this. Basically you are selecting the field from the outer query, which makes your condition always `TRUE` — JNK, Jan 23 '12 at 16:59
@JNK - How can it select from the outer query? 'select tablea.reference from tableb' is an invalid SQL statement. Can you explain how this is interpreted by SQL Server? — w0051977, Jan 23 '12 at 20:27
@w0051977 - it's not valid if you qualify it with a table name. If you just say `SELECT reference from tableb` it assumes you are referring to the field in the outer query if it exists there. — JNK, Jan 23 '12 at 20:32
@JNK - thanks. I realise it is referring to the outer table, but it is producing an invalid SQL statement i.e. 'select tablea.reference from tableb' (table a is the outer table). Is SQL server interpreting it differently? — w0051977, Jan 23 '12 at 20:39
It's not invalid since you are in a subquery. In order for subqueries to work, rules on references need to be relaxed to allow outer references. Otherwise there are a LOT of things you couldn't do that are very useful. The subquery is invalid on its own, but that's not relevant since it's a subquery. It's like saying "I'm only speeding if you look at the speed limit here, not on the highway!" context is everything. — JNK, Jan 23 '12 at 20:41
If you need more clarification we can do a quick chat about it. — JNK, Jan 23 '12 at 20:48
@JNK - last question. What happens to the 'from tableb' in the subquery when the SQL query is executed. Is it just chopped off? — w0051977, Jan 23 '12 at 20:48
Nope, it's used. But you should visualize there is an unwritten but assumed `...UNION SELECT * FROM OUTER QUERY` in your `FROM` clause - it can use anything you specify PLUS anything that's in the outer query. — JNK, Jan 23 '12 at 20:50

score 5 · Answer 1 · answered Jan 23 '12 at 16:58

5

Inside subquery select reference from tableb you see all columns from upper-level query so your condition in fact works like "where 1 = 1".

That is one reason for a good advise "give an alias to every table if you select from more than one table". For example, in your case:

select a.* from tablea a where a.reference in (
select b.reference from tableb b)

This way you'll get your expected compilation error.

answered Jan 23 '12 at 16:58

Andrei LED

2,560
17
21

Yes, change the query to `select * from tablea where reference in ( select tableb.reference from tableb)` – Olivier Jacot-Descombes Jan 23 '12 at 17:01
The second query says: select reference from tableb. However, reference does not exist in tableb. Surely there should be an error? I am obviously missing something. Thanks. – w0051977 Jan 23 '12 at 17:01
Olivier, your comment works as you described. However, why does not this produce a syntax error as "select tableb.reference from tableb" (on its own does). As soon as you add the outer query, the inner query works. – w0051977 Jan 23 '12 at 17:07
@w0051977 it's like what he said, that second (inner) query has access to the tables in the first (outer) query. – aF. Jan 23 '12 at 17:07
@w0051977 - What will this return? `select * from tablea where reference in (select reference)` – Martin Smith Jan 23 '12 at 17:08
Martin, select * from tablea where reference in (select reference) returns all rows from tablea. @af, I understand that the subquery is using the table from the superquery, however my question now is this: why does the subquery run when it is in a sub query, but not when it is on its own i.e. not in a subquery. – w0051977 Jan 23 '12 at 17:33

SQL IN statement

1 Answers1