Strange postgresql behavior

Question

Hi

can someone explain me why it works like this?

=> select client_id from clients_to_delete;
ERROR:  column "client_id" does not exist at character 8

but, when putting this inside an IN()...

=> select * from orders where client_id in(select client_id from clients_to_delete);

it works! and select all rows in the orders table. Same when running delete/update. Why it doesn't produce an error like before?

Thank you!

Answer 1

In this query

SELECT  *
FROM    orders
WHERE   client_id IN
        (
        SELECT  client_id
        FROM    clients_to_delete
        )

client_id is taken from the outer table (orders), since there is no field with such name in the inner table (clients_to_delete):

SELECT  *
FROM    orders
WHERE   orders.client_id IN 
        (
        SELECT  orders.client_id
        FROM    clients_to_delete
        )