CODEWITHSUNDEEP
sqlsql-servert-sql
Jignesh Thakker
Jignesh Thakker

Reputation: 3698

'<>' in SQL server not returns NULL value rows

I have following table (Table_1) in SQL server:

Table 1

And has following values in table.

enter image description here

I am using following query to fetch records which does not have 'Thakker' as LastName.

SELECT [id]
      ,[FirstName]
      ,[LastName]
  FROM [dbo].[Table_1] WHERE LastName <> 'Thakker'

But it's return me only third row. I am expecting that it should return me second & third rows.

Why it's not returning second row. ?

Upvotes: 2

Views: 82

Answers (2)

Sachin
Sachin

Reputation: 40980

You can use this query.

SELECT [id],
       [FirstName],
       [LastName],
  FROM [dbo].[Table_1] WHERE LastName <> 'Thakker' OR LastName IS NULL

As per your question <> operator not returns NULL value rows because <> operator does comparison with the two values so for comparison there should be two values but

NULL is not a value. It just a placeholder which shows that there is no value present. Even NULL is also not equal to NULL. That's why SQL has IS NULL and IS NOT NULL for such scenario.

Upvotes: 3

TechDo
TechDo

Reputation: 18659

Please add ISNULL checking for column LastName to include NULL values in the select.

SELECT [id]
      ,[FirstName]
      ,[LastName]
FROM [dbo].[Table_1] 
WHERE ISNULL(LastName,'') <> 'Thakker'

OR using a CASE

SELECT [id]
      ,[FirstName]
      ,[LastName]
FROM [dbo].[Table_1] 
WHERE 1=CASE WHEN LastName='Thakker' THEN 0 ELSE 1 END

Upvotes: 4

Related Questions