Re: Selecte nd NULLs

From: MARK BRINSMEAD <mark.brinsmead_at_gmail.com>
Date: Wed, 25 Mar 2015 11:35:18 -0400
Message-ID: <CAAaXtLDR+3gHNYtxSHq-v+KHkwXC+zx4_vb+=-RW53bTXKji-A_at_mail.gmail.com>

SQL uses a three-value logic system, where any "boolean" expression can return *TRUE*, *FALSE*, or *NULL*.

Any comparison against a NULL value (including NULL=NULL or NULL!=NULL) will return NULL, never TRUE nor FALSE.

Your SQL "select" statement will only return rows for which the predicates generate the value *TRUE*.

This is a fundamental property of SQL (that is, it is expected/required to be so in *any* implementation of SQL) and has nothing whatsoever to do with Oracle, or SQLserver, or MySQL, or DB2, or ...

On Wed, Mar 25, 2015 at 10:03 AM, James Clarence Allen (CENSUS/EPD FED) < James.Clarence.Allen_at_census.gov> wrote:

> Someone,
>
>
> I am doing a simple select.
>
>
> SQL>select distinct dbname from mytable where cluster != 'orrac1';
>
>
> I have a three column table "mytable" (dbname,sid,cluster). The table
> contains 206 total rows/136 rows with distinct dbname. No indices are on
> the table.
>
>
> Some dbnames are not in a cluster (single server) so the cluster column
> is null.
>
>
> When I run the select above the rows with the null in cluster column
> are not returned.
>
>
> Shouldn't all rows be turned except what I am filtering out?
>
>
> Sincerely,
>
> *Jim Allen*
> Database Support Lead, MASSDB Staff
>
> Schedule: Mon-Fri, 7:00am-3:30pm
> Tel: 1-301-763-7501
>
> Cell: 1-202-604-7286
> Database Help Desk: X34944
> Support Email: James.Clarence.Allen_at_census.gov
> Internal Website: http://epd.econ.census.gov/offices/massdb/
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Mar 25 2015 - 16:35:18 CET