Behaviour of Not Like with Null Values

Behaviour of NOT LIKE with NULL values

About NULL

'anything' NOT LIKE NULL yields NULL, not TRUE.

And only TRUE qualifies for filter expressions in a WHERE clause.

Most functions return NULL on NULL input (there are exceptions). That's the nature of NULL in any proper RDBMS.

If you desire a single expression, you could use:

AND   (column_default LIKE 'nextval%')  IS NOT TRUE;

That's hardly shorter or faster, though. Details in the manual.

Proper query

Your query is still unreliable. A table name alone is not unique in a Postgres database, you need to specify the schema name in addition or rely on the current search_path to find the first match in it:

Related:

• How does the search_path influence identifier resolution and the "current schema"

SELECT column_name
FROM   information_schema.columns
WHERE  table_name = 'hstore1'
AND    table_schema = 'public'   -- your schema!
AND   (column_default IS NULL OR
       column_default NOT LIKE 'nextval%');

Better, but still not bullet-proof. A column default starting with 'nextval' does not make a serial, yet. See:

• Auto increment table column

To be sure, check whether the sequence in use is "owned" by the column with pg_get_serial_sequence(table_name, column_name).

I rarely use the information schema myself. Those slow, bloated views guarantee portability across major versions - and aim at portability to other standard-compliant RDBMS. But too much is incompatible anyway. Oracle does not even implement the information schema (as of 2015).

Also, useful Postgres-specific columns are missing in the information schema. For this case I might query the the system catalogs like this:

SELECT *
FROM   pg_catalog.pg_attribute a
WHERE  attrelid = 'table1'::regclass
AND    NOT attisdropped   -- no dropped (dead) columns
AND    attnum > 0         -- no system columns
AND    NOT EXISTS (
   SELECT FROM pg_catalog.pg_attrdef d
   WHERE  (d.adrelid, d.adnum) = (a.attrelid, a.attnum)
   AND    d.adsrc LIKE 'nextval%'
   AND    pg_get_serial_sequence(a.attrelid::regclass::text, a.attname) <> ''
   );

Faster and more reliable, but less portable.

The manual:

The catalog pg_attrdef stores column default values. The main
information about columns is stored in pg_attribute (see below). Only
columns that explicitly specify a default value (when the table is
created or the column is added) will have an entry here.

'table1'::regclass uses the search_path to resolve the name, which avoids ambiguity. You can schema-qualify the name to overrule: 'myschema.table1'::regclass.

Related:

• Find the referenced table name using table, field and schema name
• Get the default values of table columns in Postgres?

Does Oracle 'NOT LIKE' expression do not return NULL?

NULL is not matched with LIKE. You have to explicitly ask for it with OR firstname IS NULL

Not equal != operator on NULL

<> is Standard SQL-92; != is its equivalent. Both evaluate for values, which NULL is not -- NULL is a placeholder to say there is the absence of a value.

Which is why you can only use IS NULL/IS NOT NULL as predicates for such situations.

This behavior is not specific to SQL Server. All standards-compliant SQL dialects work the same way.

Note: To compare if your value is not null, you use IS NOT NULL, while to compare with not null value, you use <> 'YOUR_VALUE'. I can't say if my value equals or not equals to NULL, but I can say if my value is NULL or NOT NULL. I can compare if my value is something other than NULL.

Is SQL LIKE NULL valid syntax for all database?

Yes, LIKE NULL is valid in all RDBMS. LIKE is an operator followed by a string and a string can be null; so no problem.

Comparing a value to NULL, no matter what operator (<, <=, =, <>, LIKE, etc. - except for IS which is especially made to compare with NULL), results in UNKNOWN. UNKNOWN is not TRUE, so the condition is not met in case of NULL. And anyway, in case @firstName contains NULL, @firstName IS NULL evaluates to TRUE, so it doesn't even matter what LIKE @firstName results in then (because @firstName IS NULL OR firstName LIKE @firstName is TRUE when at least one of the two conditions is TRUE).

NOT IN with Nulls - unclear behaviour

First query returns no data because of comparing null values.
When you compare 2 values result could be

• TRUE if they equals
• FALSE if they're not
• UNKNOWN if one or two of the values is null

So when you use not in sql should compare your value glnfact with all values of GLN in the subquery and if all compares return FALSE, then it returns TRUE for the whole not in clause. If one of the value of GLN is null, comparing it with glnfact returns UNKNOWN so not in clause is UNKNOWN.

NOT Like or in an NVARCHAR field is also filtering out Null - SQL Server 2008

Then use IS NULL + OR <> N'Complete':

SELECT TOP (1) dbo.woo.woo_statuscode, 
               dbo.woo.woo_workcenterid 
FROM   dbo.woo 
       LEFT OUTER JOIN dbo.wke 
                    ON dbo.woo.woo_recordid = dbo.wke.wke_woo_recordid 
       LEFT OUTER JOIN dbo.wko 
                    ON dbo.woo.woo_workorderid = dbo.wko.wko_workorderid 
WHERE  ( dbo.wko.wko_workorderid = @WorkOrder ) 
AND (dbo.woo.woo_statuscode IS NULL OR dbo.woo.woo_statuscode <> N'Complete')
ORDER  BY dbo.wke.wke_labstoptime DESC 

Why you can't select null values with = or <>:

<> is Standard SQL-92; != is its equivalent. Both evaluate for
values, which NULL is not -- NULL is a placeholder to say there is
the absence of a value.

Which is why you can only use IS NULL/IS NOT NULL as predicates
for such situations.

This behavior is not specific to SQL Server. All standards-compliant
SQL dialects work the same way.

https://stackoverflow.com/a/5658472/284240

You can change this behaviour via SET ANSI NULLS OFF.

Strange behaviour SQL on comparing null values

demo: db<>fiddle

SELECT *
FROM br
LEFT JOIN af
ON 
    br.col1 = af.col1 
AND br.col2 = af.col2 
AND COALESCE(br.col3, af.col3, br.col4, af.col4) IS NULL

The COALESCE() IS NULL ensures that all elements in this function (all columns) are NULL.

NULL values inside NOT IN clause

Query A is the same as:

select 'true' where 3 = 1 or 3 = 2 or 3 = 3 or 3 = null

Since 3 = 3 is true, you get a result.

Query B is the same as:

select 'true' where 3 <> 1 and 3 <> 2 and 3 <> null

When ansi_nulls is on, 3 <> null is UNKNOWN, so the predicate evaluates to UNKNOWN, and you don't get any rows.

When ansi_nulls is off, 3 <> null is true, so the predicate evaluates to true, and you get a row.

---

Related Topics