3

Consider the following tables. Mind the differing names of the id-columns.

CREATE TABLE A (id int)
CREATE TABLE B (bid int)

A contains values 1 to 10. B contains values 1 to 5.

Now, I wrote a query to find all rows that are present in both tables, by comparing their ids. By mistake, I wrote an incorrect query like this:

select id from A
 where id in (select id from B)

See how I forgot that the id column of B is named bid

What I don't understand is why this query executes and return all rows from A. If anything, I expected it to return 1 to 5

Having a look at the execution plan doesn't help me. See how the table scan of table B actually return 10 rows in the image below. enter image description here

So, is this really the expected behaviour of the query I wrote? I would expect an error message like Invalid column name 'id', which is what I get when I run select id from B in isolation.

Mattias Nordqvist
  • 898
  • 6
  • 14
  • Inserting a few aliases I realized that this query is equivalent to the one in the question: `select a.id from A a where a.id in (select a.id from B b)`. So the "scope" of the outer select is somehow used in the sub-select. Still, this feels very sketchy... – Mattias Nordqvist Jul 03 '15 at 08:18
  • If you do `..in (select B.id from B)` I guess it would throw an error? Leaving Sql to figure out the bindings is never a good idea :) – Mackan Jul 03 '15 at 08:20
  • yep, you are both right! Thank you! – Mattias Nordqvist Jul 03 '15 at 08:22

0 Answers0