1

In SQL (specifically Postgres):

The clause where not foo='bar' in case foo is null evaluates into some sort of null, causing the row is not included in the result.

On the other hand, the clause where (foo='bar') is not true where foo is null evaluates into true (the row is included in the result).

So is the use of (foo='bar') is not true equivalent to foo is null or not foo='bar'?

It doesn't seem that people are using this pattern for some reason, am I missing something?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
orange77
  • 933
  • 8
  • 12

2 Answers2

2

So is the use of (foo='bar') is not true equivalent to foo is null or not foo='bar'?

Yes. Or simpler:

foo IS NULL OR foo <> 'bar'

Or simpler, yet:

foo IS DISTINCT FROM 'bar'

Use the latter.
Details in the manual chapter Comparison Functions and Operators.
Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • As a side note, `is distinct from` can not use an index. However, `foo IS NULL OR foo <> 'bar'`might use an index (if suitable) –  Jul 25 '19 at 17:11
  • As a side note to the side note: A condition like `foo IS DISTINCT FROM 'bar'` rarely would use an index even if that's technically possible, as it typically qualifies for most rows, and that's where indexes typically don't help. – Erwin Brandstetter Jul 25 '19 at 17:40
0

How about this?

where coalesce(foo, 'foo') <> 'bar'

or

where not coalesce(foo, 'foo') = 'bar'

I suspect people are avoiding IS NOT TRUE because it doesn't work in Oracle and MS SQL Server. The same reason people using MS SQL Server will avoid using the ISNULL function.

Response to comment:

I don't know what you mean by an impossible 'foo'. Do you mean a value that's not possible? Not true. It just needs to not be 'bar'. This still works:

declare @foo varchar(15)
declare @bar varchar(15)
set @bar = 'bar'
--set @foo = 'bar'
--set @foo = 'asdf'

select 7

where coalesce( @foo, 'not' + @bar) <> @bar
dougp
  • 2,810
  • 1
  • 8
  • 31