Not Equal does not work in SQL Query

Question

enter image description here

I am trying to use the "Not Equal" command in Sql but it does not seem to be working. I want to get the total count when all the 3 fields (status1, status2 and status3) are not equal to Yes. For some reason I am getting 0 records.

SELECT
    COUNT(ID) from [maintable]
WHERE 
    status1 <> 'YES'
    and status2 <> 'YES'
    and status3 <> 'YES'

The above query does not generate any results. What am i doing wrong here? Thanks

Can you provide some sample data? As @TimSchmelter alluded to, if those tuples are NULL, then an equality comparison with NULL is unpredictable. It needs to be handled. — Stuart Ainsworth
– Stuart Ainsworth, Commented Jan 7, 2013 at 16:50

Tim Schmelter · Accepted Answer · 2013-01-07 16:47:55Z

15

Try this:

SELECT
    COUNT(ID) from [maintable]
WHERE
    COALESCE(status1, '') <> 'YES'
    AND COALESCE(status2, '') <> 'YES'
    AND COALESCE(status3, '') <> 'YES'

null values are not <> 'YES', they are undefined.

COALESCE

answered Jan 7, 2013 at 16:47

Tim Schmelter

462k79 gold badges719 silver badges980 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1858332 Over a year ago

Tim, i am still getting no data back when i run the qry. Pls. see my post on the top i have just added a picture of how my table look like when i just run the select statement. thnx

Maksym Strukov Over a year ago

Tim's query is fine. If data sample is all data you have in your table it is OK that you get no data because there are no records which meet the where clause

Martin Smith Over a year ago

@user1858332 - What rows were you expecting to be returned from that sample data? Maybe you need OR instead of AND

Tim Schmelter Over a year ago

@MaksymStrukov: Btw, here's your sample data and yes, it doesn't return a record: SQL-Fiddle. But that makes sense since even the last row's status3 is 'YES', hence it is not returned. All conditions in a WHERE must be true to return that record if using AND.

Maksym Strukov Over a year ago

@TimSchmelter: Actually that's what I wrote to user1858332. This is not my sample data and not even my question. I'm absolutley with you that it's OK that no data is returned if the sample data is used only.

|

Juan Martí · Accepted Answer · 2016-05-26 21:41:27Z

2

Try using EXCEPT.

SELECT
    COUNT(ID) from [maintable]
EXCEPT
SELECT
    COUNT(ID) from [maintable]
WHERE 
    status1 = 'YES'
    and status2 = 'YES'
    and status3 = 'YES'

answered May 26, 2016 at 21:41

Juan Martí

3093 silver badges6 bronze badges

Comments

Jaanu · Accepted Answer · 2021-06-04 10:21:26Z

0

SELECT
    COUNT(ID) from [maintable]
WHERE
    NVL(status1, 'NO') <> 'YES'
    NVL COALESCE(status2, 'NO') <> 'YES'
    NVL COALESCE(status3, 'NO') <> 'YES'

we can use nvl for null cases

answered Jun 4, 2021 at 10:21

Jaanu

888 bronze badges

Collectives™ on Stack Overflow

Not Equal does not work in SQL Query

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related