I am on vacation till Jan.14. I have no reach to any SQL server and have very limited access to internet during my vacation. So I am sorry for the delay to the reply of the comments.

1) I don't think it's necessary to scope the column in the subquery. Actually you can only select the columns from the tables in the FROM subclause in the sub-query. It does not make sense to select columns from the tables refereneced by the main query. What result set are you expecting from a query like:

SELECT * FROM sys.server_principals b WHERE b.sid=(SELECT b.[sid] FROM sys.databases a WHERE a.name=DB_NAME())

2) Since I cannot reach SQL Server, can anyone run the following and let me what you get:

CREATE TABLE Ta (Ca1 int not null, Ca2 int not null)

GO

CREATE TABLE Tb (Cb1 int not null, Cb2 int not null)

GO

INSERT Ta (Ca1,Ca2) VALUES (1,1)

INSERT Ta (Ca1,Ca2) VALUES (2,2)

INSERT Tb (Cb1,Cb2) VALUES (2,2)

INSERT Tb (Cb1,Cb2) VALUES (3,3)

If we make a mistake in the following query

SELECT * FROM Ta WHERE Ca1 IN (SELECT Cb1 FROM Tb WHERE Cb2=2)

and write it as:

SELECT * FROM Ta WHERE Ca1 IN (SELECT Ca1 FROM Tb WHERE Cb2=2)

What result you get? Does SQL Server report error?

3) As I said in the article, it's talking about a scenario when a developer makes a mistake and SQL Server does not report the problem. If SQL server impliments the sub-query as a join (as mentioned by Hugo), it's an even bigger issue. The sub-query returns a scalar value in the example. It has nothing to do with the main query.