SQL query results not add up when using a WHERE clause

Question

What could be causing my second SQL query to return less than the expected count? I have an address table with 1000 entries, and my first query returns 100 when filtering for 'New York' city and 'Mainstreet' street. The second query should return 900, but it's not. Here are my queries:

select count(*)
from address_data
where city = 'New York' and street = 'Mainstreet'

select count(*)
from address_data
where not (city = 'New York' and street = 'Mainstreet')

Answer 1

--  S a m p l e   d a t a :
WITH
    tbl (ID, CITY, STREET) AS 
        (
            Select 1, 'New York', 'Main Street' From Dual Union All
            Select 2, 'New York', 'NOT Main Street' From Dual Union All
            Select 3, 'NOT New York', 'Main Street' From Dual Union All
            Select 4, 'New York', Null From Dual 
        )

-- Your 2nd query returns 2 instead of 3 as count
Select Count(*) "CNT"
From tbl
Where CITY = 'New York' and STREET = 'Main Street'

-- Options for your 2nd query that all return 3 as count

-- Posssible Null values replaced with '-' for comparison
-- using NOT (condition 1 And condition 2)
Select Count(*) "CNT"
From tbl
Where Not (Nvl(CITY, '-') = 'New York' And Nvl(STREET, '-') = 'Main Street')

-- Posssible Null values replaced with '-' for comparison
-- using negative Condition 1 OR negative Condition 2
Select Count(*) "CNT"
From tbl
Where Nvl(CITY, '-') != 'New York' OR Nvl(STREET, '-') != 'Main Street'

-- using negative Condition 1 OR negative Condition 2
-- additional testing for null values separately
Select Count(*) "CNT"
From tbl
Where CITY != 'New York' OR CITY Is Null OR STREET != 'Main Street' OR STREET Is Null