sql - Postgresql IN statement -

why query deleting users when user_id column not exist @ profile table?

the initial query:

delete users     user_id in (         select user_id profile         )     , user_id != 35     , user_id != 6;  

to more specific.

the in statement query returns:

select user_id profile;  error:  column "user_id" not exist line 1: select user_id profile; 

running initial query returns

delete 100

why happening?

shouldn't have stopped execution upon error or return false or null?

running postgresql server 9.0

this behavior correct per ansi standards.

if unqualified column name doesn't resolve in inner scope outer scope considered. doing unintentional correlated sub query.

as long table profile contains @ least 1 row then

 users  user_id in (         select user_id profile         ) 

will end matching rows in users (except users.user_id null where null in (null) not evaluate true). avoid possible issue can use 2 part names.

delete users  user_id in (select p.user_id                      profile p)  

would give error

column p.user_id not exist: