Re: IS NOT NULL and LEFT JOIN

On Tue, Oct 21, 2014 at 2:58 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Laurent Martelli <laurent(dot)martelli(at)enercoop(dot)org> 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