RE: Selecte nd NULLs

Now, there are two or three reasonable options for fetching back all the rows except some particular value (or some particular list of values).  

The simplest, which gets you a full table scan since you have no indexes on this table (and a full table scan is often the most reasonable access plan for a single table query.)

select distinct dbname from mytable where cluster != 'orrac1'; becomes  

select distinct dbname from mytable where cluster is null or cluster != ‘orrac1’;  

If you did have an index on cluster you could use a subquery to fetch up the rowids to omit (this works either way, but is only likely faster if cluster is indexed):  

select distinct dbname from mytable where rowid not in (select rowid from mytable where cluster = ‘orrac1’);  

(notice you’re select the rowids you want to leave out from the index if it exists and using not in to exclude them. so = rather than != in the subquery is on purpose).  

The third obvious way is to wrap an nvl around cluster mapping NULLs to any value you want to keep.  

select distinct dbname from mytable where nvl(cluster,’keepme’) != ‘orrac1’;  

The downside to that is running the function on each row and picking a value you’re not going to match accidentally if, say, ‘orrac1’ is actually a variable (bind variable.)  

good luck. just remember that the database management system cannot attribute any value to a NULL.  

mwf    

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of MARK BRINSMEAD Sent: Wednesday, March 25, 2015 11:35 AM To: James.Clarence.Allen_at_census.gov
Cc: Oracle Chat freelist
Subject: Re: Selecte nd NULLs  

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