CODEWITHSUNDEEP
postgresql
Sergey
Sergey

Reputation: 11

Strange postgresql behavior

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!

Upvotes: 1

Views: 108

Answers (1)

Quassnoi
Quassnoi

Reputation: 425573

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
        )

Upvotes: 4

Related Questions