Where is my banana? (SQL Query NULL issue)

Question

I have this table:

CREATE TABLE [dbo].[Fruit](
    [RecId] [int] IDENTITY(1,1) NOT NULL,
    [Banana] [int] NULL,
    [TextValue] [varchar](50) NULL
)

And the following piece of code:

DECLARE @FruitInput INT = NULL
SELECT * FROM Fruit WHERE Banana = @FruitInput 

This record is in that table:

1|NULL|Hello 

Why wont my query return this record?

Answer 1

It is because Null is undefined. Null neither equal nor not equal to anything. Please read more on MSDN.

Null values can be checked with is null (or is not null), or isnull() or coalesce() functions depending on the requirement.

Try this:

SELECT * FROM Fruit WHERE Banana is null

And following query to select all the records in case if @FruitInput is null:

SELECT * FROM Fruit WHERE @FruitInput is null or Banana = @FruitInput