Unexpected empty query results

Ok, This is a dumb one . . . .

Select * from <table> where <col1> is null and <col2> = 1;

<col1> is varchar2(5byte) no defalut value <col2> is number default of -1

Why will my above query not return any records? I know for a fact that the query SHOULD return a result set, but I'm getting nothing.

If I do:

Select * from <table> where <col1> is null; 

I get 127531 records returned

If I do:

Select * from <tabl开发者_Python百科e> where <col2> = 1; 

I get 86342 records returned

Does Oracle deal with null values in a weird way? I'm an MSSQL kinda person.

From what you're writing I'm not convinced that the query should return a record .. seems to me that you haven't got any rows that satisfy both conditions.

Maybe you want to OR the two conditions (getting the records that satisfy any one of the two conditions)?

Your select looks correct to me, if you don't get results then it's a data problem, not Oracle/select problem.

But are you sure the data in col1 is null when there's no value ? It might as well be just an empty value ( '' ) and not actually the "null" value, depending on the process that's writing the data.

So to be on the safe side, better try :

Select * from <table> where ( <col1> is null or <col1> = '' ) and <col2> = 1;

I think your problem is that there are no rows where both col1 is NULL and col2 is 1. Do ...

SELECT COUNT(*)
FROM <table>

Are there more than 127531 + 86342 records in the table? Then it's possible no row meets your condition. Try also...

SELECT COUNT(*) 
FROM <table> 
WHERE <col1> IS NOT NULL 
   OR <col2> IS NULL OR <col2> <> 1

This will yield number of rows that do not meat your condition. Is this the same as total number of records in the table? Then it's correct that your orignal query does not return anything.

I don't know what you mean by weird handling of NULL. I think the only thing Oracle does differently is that an empty string IS NULL. When you compare strings in Oracle, you have to keep this in mind. I know you can configure MSSQL to behave this way too.

I'm pretty sure, by default (without some weird custom config), MSSQL and Oracle both handle NULL in the same way when used in a "Boolean" logical expression. But I found it weird when I first learned how it works.

IS NULL, IS NOT NULL : These work as you'd expect. They return TRUE or FALSE depending on whether the value is NULL or not.

ALL OTHER COMPARISON OPERATORS : If one or both values being compared are NULL, the result of the operation is NULL. (ex: NULL < 1 evaluates to NULL, NULL = NULL evaluates to NULL, NULL <> 1 evaluates to NULL)

ALL LOGICAL OPERATORS (NOT, AND, OR, ...) : If one or more operands are NULL, evaluate the operation as if the NULL values could be both TRUE and FALSE. If this method yields TRUE in some cases and FALSE in others, the result is NULL. (ex: NULL AND FALSE evaluates to FALSE, NULL AND TRUE evaluates to NULL, NOT NULL evaluates to NULL)

WHEN USING IN CONDITIONAL/WHERE CLAUSE : A logical expression that evaluates to NULL has the same effect as one that evaluates to FALSE. IF blocks don't execute and rows for which WHERE expression evaluates to NULL are not returned.

EXAMPLES:

DECLARE
  A NUMBER;
BEGIN
  A:=0; 
  IF NULL = NULL THEN 
    A:=1; 
  END IF;
  DBMS_OUTPUT.PUT_LINE(A);  -- this will print 0.
END;

-- without the WHERE clause, following query
-- would result in a single row of single value "hi"
SELECT 'hi'
FROM dual
WHERE 1 <> NULL OR NULL = NULL; -- this returns no rows

EDITS: corrected content and code

You have two counts for each of your filters but no indication how many records match both criteria. If you want to check that you could run the following query:

select count(*) from 
(
    Select * from <table> where <col1> is null
    intersect
    Select * from <table> where <col2> = 1)
/

My money is on it returing 0.

If I do: Select * from where is null; then I get 127531 records returned Select * from where = 1; then I get 86342 records returned

Your sintax seems to be correct however maybe there are no records that fullfills both conditions, maybe your are trying to use an OR, or maybe your DBMS is doing weird things, I have had some problems like that in SQL Navigator 6, they already accepted that problem and corrected it in Version 6.1

Good luck!