The WHERE clause cannot be used to filter data that contains null values. To filter data that contains null values, install the LNNVL function in a PolarDB for PostgreSQL(Compatible with Oracle) cluster.
Install the LNNVL function
The LNNVL function is supported by PolarDB for PostgreSQL(Compatible with Oracle) as a plug-in. Before you use the LNNVL function, run the following commands to install the plug-in:
SET client_min_messages TO 'ERROR';
CREATE EXTENSION if not exists polar_lnnvl;
RESET client_min_messages;
Syntax
lnnvl(condition)
Parameter | Description |
---|---|
condition | The query condition for the LNNVL function. |
Description
- If some data in the table matches the condition, all data that does not match the condition is returned. Null values can be returned.
- If no data in the table matches the condition, all data is returned. Null values can be returned.
- If only null values in the table match the condition, all data except null values is returned.
For example, a table contains the following two rows of data:
name | id
------------
a | 2
b | null
The following table lists the results that are returned by the LNNVL function with different conditions.
Condition | Whether a non-null or null value matches the condition | LNNVL return value |
---|---|---|
lnnvl(id=1) | No | Rows a and b |
lnnvl(id=2) | Yes | Row b |
lnnvl(id>2) | No | Rows a and b |
lnnvl(id<2) | No | Rows a and b |
lnnvl(id is null) | Yes | Row a |
Examples
Run the following commands to create a table named account
and insert test data into the table:
create table account(name varchar2(20),year number);
insert into account values('peter2001',2001);
insert into account values('peter2002',2002);
insert into account values('peter2003',2003);
insert into account values('peter2004',2004);
insert into account values('peter2005',2005);
insert into account values('peter2006',2006);
insert into account values('peter2007',null);
- If the condition is
year<2003
, the LNNVL function returns the rows where the value in the year column is greater than or equal to 2003 or is null.Execute the following statement:
select * from account where lnnvl(year<2003);
The following result is returned:
name | year -----------+------ peter2003 | 2003 peter2004 | 2004 peter2005 | 2005 peter2006 | 2006 peter2007 | null
- If the condition is
year is not null
, the LNNVL function returns the rows where the value in the year column is null.Execute the following statement:
select * from account where lnnvl(year is not null);
The following result is returned:
name | year -----------+------ peter2007 | null
- If the condition is
year is null
, the LNNVL function returns the rows where the value in the year column is not null.Execute the following statement:
select * from account where lnnvl(year is null);
The following result is returned:
name | year -----------+------ peter2001 | 2001 peter2002 | 2002 peter2003 | 2003 peter2004 | 2004 peter2005 | 2005 peter2006 | 2006
- If the condition is
year=2008
, the LNNVL function returns the rows where the value in the year column is not 2008.Execute the following statement:
select * from account where lnnvl(year=2008);
The following result is returned:
name | year -----------+------ peter2001 | 2001 peter2002 | 2002 peter2003 | 2003 peter2004 | 2004 peter2005 | 2005 peter2006 | 2006 peter2007 | null
- If the condition is
year!=2008
, the LNNVL function returns the rows where the value in the year column is 2008 or null.Execute the following statement:
select * from account where lnnvl(year! =2008);
The following result is returned:
name | year -----------+------ peter2007 | null