What is going on here?

Hey, I just wrote an article on this! I'll paste the relevant snippet so you don't need to read the whole thing though.

The subquery, in fact, cannot resolve and find the column ID. So what happens is that when the query cannot find the column ID in the inner scope, it moves to the outer scope of the query to find the column that it was referencing and it finds [A].[ID]. Effectively changing your query to:

SELECT * FROM A

WHERE ID IN (SELECT ID FROM A)

In my example, it was basically the same thing. Except for your table B, the real column name was FakeID, but I put the value ID instead.

/r/SQL Thread