<!--

$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.297 2005/12/03 16:45:05 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.298 2005/12/11 10:54:27 neilc Exp $

PostgreSQL documentation

-->

<indexterm>

<primary>IS DISTINCT FROM</primary>

</indexterm>

<indexterm>

<primary>IS NOT DISTINCT FROM</primary>

</indexterm>

The ordinary comparison operators yield null (signifying <quote>unknown</>)

when either input is null. Another way to do comparisons is with the

<literal>IS DISTINCT FROM</literal> construct:

<synopsis>

<replaceable>expression</replaceable> IS DISTINCT FROM <replaceable>expression</replaceable>

<replaceable>expression</replaceable> IS NOT DISTINCT FROM <replaceable>expression</replaceable>

</synopsis>

For non-null inputs this is the same as the <literal>&lt;&gt;</> operator.

However, when both inputs are null it will return false, and when just

one input is null it will return true. Thus it effectively acts as though

null were a normal data value, rather than <quote>unknown</>.

For non-null inputs, <literal>IS DISTINCT FROM</literal> this is

the same as the <literal>&lt;&gt;</> operator. However, when both

inputs are null it will return false, and when just one input is

null it will return true. Similarly, <literal>IS NOT DISTINCT

FROM</literal> is identical to <literal>=</literal> for non-null

inputs, returns true when both inputs are null, and false

otherwise. Thus, these constructs effectively act as though null

were a normal data value, rather than <quote>unknown</>.

</para>

<para>

{

$$ = (Node *) makeSimpleA_Expr(AEXPR_DISTINCT,"=",$1,$5);

}

| a_expr IS NOT DISTINCT FROM a_expr%prec IS

{

$$ = (Node *) makeA_Expr(AEXPR_NOT, NIL,NULL,

(Node *) makeSimpleA_Expr(AEXPR_DISTINCT,

"=",$1,$6));

}

| a_expr IS OF'(' type_list')'%prec IS

{

$$ = (Node *) makeSimpleA_Expr(AEXPR_OF,"=",$1, (Node *)$5);

{

$$ = (Node *) makeSimpleA_Expr(AEXPR_DISTINCT,"=",$1,$5);

}

| b_expr IS NOT DISTINCT FROM b_expr%prec IS

{

$$ = (Node *) makeA_Expr(AEXPR_NOT, NIL,

NULL, (Node *) makeSimpleA_Expr(AEXPR_DISTINCT,"=",$1,$6));

}

| b_expr IS OF'(' type_list')'%prec IS

{

$$ = (Node *) makeSimpleA_Expr(AEXPR_OF,"=",$1, (Node *)$5);

f

(1 row)

-- ANSI SQL 2003 form

SELECT 1 IS NOT DISTINCT FROM 2 as "no";

no

----

f

(1 row)

SELECT 2 IS NOT DISTINCT FROM 2 as "yes";

yes

-----

t

(1 row)

SELECT 2 IS NOT DISTINCT FROM null as "no";

no

----

f

(1 row)

SELECT null IS NOT DISTINCT FROM null as "yes";

yes

-----

t

(1 row)

SELECT2 IS DISTINCTFROM2as"no";

SELECT2 IS DISTINCTFROMnullas"yes";

SELECTnull IS DISTINCTFROMnullas"no";

SELECT1 IS NOT DISTINCTFROM2as"no";

SELECT2 IS NOT DISTINCTFROM2as"yes";

SELECT2 IS NOT DISTINCTFROMnullas"no";

SELECTnull IS NOT DISTINCTFROMnullas"yes";