RE: Selecte nd NULLs
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <mark.brinsmead@xxxxxxxxx>, <James.Clarence.Allen@xxxxxxxxxx>
- Date: Wed, 25 Mar 2015 18:36:38 -0400
Nicely written Mark.
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of MARK BRINSMEAD
Sent: Wednesday, March 25, 2015 11:35 AM
To: James.Clarence.Allen@xxxxxxxxxx
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@xxxxxxxxxx> 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@xxxxxxxxxx
Internal Website: http://epd.econ.census.gov/offices/massdb/
Other related posts:
