Left join - Behavior when using the where (null <> 'p')

Question

In this select is presented 4 records, being three nulls for not having correlation and only one with correlation.

Result:

When I add a constraint in WHERE pc.contype <> 'p', all records only and not just the 'p'.

Result:

I can not make sense of what's happening, is it some rule I let go?

Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Make your post self-contained. — philipxy, May 24 '19 at 19:43
Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS So look at the output without the pc WHERE clause to see what that clause is doing. — philipxy, May 24 '19 at 19:44
Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, May 24 '19 at 19:47
Learn about how NULL is used. PS Do you understand that SQL `x <> y` means x is not NULL & y is not NULL & x is not equal to y (in the mathematics sense, not SQL `=` sense)? Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, May 24 '19 at 20:08
Possible duplicate of [Not equal <> != operator on NULL](https://stackoverflow.com/questions/5658457/not-equal-operator-on-null) — philipxy, May 24 '19 at 20:20
I do not believe they are the same. I'm doing a comparison of (different) and in my view should present anything but 'p'. The link you passed would be equal, in the case = 'p'. In my case he did not return anything, neither 'p' nor null. — Gabriel, May 24 '19 at 20:28
Not always the doubts are generated by common point of view, unfortunately I did not find articles that answered my question exactly. — Gabriel, May 24 '19 at 20:44
It's the p.X that is null that is then not <> to anything including 'p'. — philipxy, May 24 '19 at 20:49
Possible duplicate of [query “not equal” doesn't work](https://stackoverflow.com/questions/8994408/query-not-equal-doesnt-work) — philipxy, May 24 '19 at 20:52

score 1 · Answer 1 · answered May 24 '19 at 19:33

So what is happening here is that pg_indexes has 4 rows matching your WHERE conditions. When you LEFT JOIN, you keep all rows even if there is no join. In your case, only one row joins pg_constraint.

When a LEFT JOIN fails to connect a row, it fills all of the would-be columns with NULL. This means that in your case, the 3 rows that did not join pg_constraint now have NULL for all columns specified by pg_constraint.

Lastly, when it comes to your comparison of ____ <> 'p', you are missing a rule for how SQL treats NULL values. NULL is treated as the absence of value rather than something you can compare to. Any attempt to use a scalar comparison will fail.

You can correct this by changing to WHERE (pc.contype IS NULL OR pc.contype <> 'p')

@Gabriel & BradS What does "will fail" mean? "Fail" is not a helpful term, use enough words, phrases & sentences to be clear. "NULL is treated as the absence of value rather than something you can compare to" No, you certainly can compare to null. The comparison result is defined to be "unknown" or "null". Those are two SQL technical terms. Then WHERE filters out FROM result rows where the predicate evaluates to non-true. — philipxy, May 24 '19 at 23:20

Salman A · Accepted Answer · 2019-05-25T11:06:13.047

1

NULL does not equal (or not-equal) anything, including NULL itself. Ignoring that you're using LEFT JOIN and assuming that contype is nullable: the condition WHERE contype <> 'p' will not match NULL simply because NULL <> 'p' is unknown, not true!

You would write the following to get the rows where there is no match in the right table:

WHERE contype <> 'p' OR contype IS NULL

edited May 25 '19 at 11:06

answered May 24 '19 at 19:51

Salman A

262,204
82
430
521

Thanks for the contribution. That way you showed it worked out. Now I understand that adding a constraint (where) on a left join causes something different. – Gabriel May 24 '19 at 20:19
1

@Gabriel & SalmanA "NULL <> 'p' is undefined" No, it is defined to be "unknown" or "null". Those are two SQL technical terms. – philipxy May 24 '19 at 23:09

score 1 · Answer 3 · answered May 24 '19 at 20:20

1

What you are looking for is a NULL-safe comparison. Postgres supports the ANSI/ISO standard function for this, so I would recommend:

WHERE contype IS DISTINCT FROM 'p'

This is explained in the documentation.

answered May 24 '19 at 20:20

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 4 · answered May 24 '19 at 19:31

You are using left join, so pg_indexes data would always return, even when pg_constraints does not have any corresponding data for same key. That is what is happening in first query. In second query you added a check to only return data where pg_constraints value is not p, this time NULL values would also be checked against it and would not show up as the check fails. If this check was in join clause instead, it would have still shown the NULL values, but it is in where clause and that filters whole result set. Left join is outer join which would always return all data from table left of the join, even if nothing exists for same values in right of the join, you need to keep this in mind.

score 0 · Answer 5 · answered May 24 '19 at 19:33

The extra condition pc.contype <> 'p' in the WHERE clause effectively defeats the outer join and converts it into an inner join. Place this predicate in the ON clause instead.

You probably want something like:

select
  pc.contype
from pg_indexes pi
left join pg_constraint pc on pi.indexname = pc.conname
                          and pc.contype <> 'p'
where pi.tablename = 'mytable'

Left join - Behavior when using the where (null <> 'p')

5 Answers5