On Tue, Oct 21, 2014 at 2:58 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Laurent Martelli <laurent.martelli@xxxxxxxxxxxx> writes:
> Do we agree that both queries are identical ?
No, they *aren't* identical. Go consult any SQL reference. Left join
conditions don't work the way you seem to be thinking: after the join,
the RHS column might be null, rather than equal to the LHS column.
For what it's worth I'd say they are identical, at least, if you discount deferring foreign key constraints or also executing the query from within a volatile function which was called by a query which just updated the user_info table to break referential integrity.
The presence of the foreign key on contract_contract.user_info which references user_user_info.id means that any non-null contract_contract.user_info record must reference a valid user_user_info record, therefore the join is not required to prove that a non nulled user_info contract records match a user info record, therefore the join to check it exists is pretty much pointless in just about all cases that you're likely to care about.
Although, saying that I'm still a bit confused about the question. Are you asking if there's some way to get PostgreSQL to run the 1st query faster? Or are you asking if both queries are equivalent?
Regards
David Rowley
