title: SQL Server where clause with null column
body: <p>I have an <code>Employee</code> table and how it works is, when a new employee is added, the column <code>[DOR]</code> will be null and <code>[Status]</code> will be 1. When the employee is being relieved from the company, the <code>[DOR]</code> column value will be the date which he/she left the company and <code>[Status]</code> is set to 0.</p>
  
  <p>I need to fetch the details of all the employees who were available in a given date. The employees with <code>Status</code> as 1 and those who are not yet relieved till that date have to be fetched.</p>
  
  <p>But I am not able to do the same as when equating with <code>DOR</code>, its null value and not returning any of the rows.</p>
  
  <p><img src="https://i.stack.imgur.com/tsNSf.png" alt="Data in Employee table"> </p>
  
  <p>If I give the input as <code>2015-02-10</code>, it should fetch the two records and when I give <code>2015-02-15</code>, it should fetch only first record.</p>
  
  <pre><code>CREATE TABLE [Employee]
  (
      [EmployeeId] [int] IDENTITY(1000,1) NOT NULL,
      [Name] [varchar](50) NOT NULL,
      [RoleId] [int] NOT NULL,
      [Email] [varchar](50) NULL,
      [Contact] [varchar](50) NULL,
      [DOJ] [date] NOT NULL,
      [DOR] [date] NULL,
      [Status] [bit] NOT NULL,
      [Salary] [decimal](18, 2) NULL
  )
  
  INSERT [dbo].[Employee] ([EmployeeId], [Name], [RoleId], [Email], [Contact], [DOJ], [DOR], [Status], [Salary])  
  VALUES (1001, N'Employee 1', 3, N'', N'', CAST(0x8D390B00 AS Date), NULL, 1, CAST(6000.00 AS Decimal(18, 2)))
  
  INSERT [dbo].[Employee] ([EmployeeId], [Name], [RoleId], [Email], [Contact], [DOJ], [DOR], [Status], [Salary]) 
  VALUES (1002, N'Employee 2', 7, N'', N'', CAST(0x8D390B00 AS Date), CAST(0x9A390B00 AS Date), 0, CAST(4000.00 AS Decimal(18, 2)))
  </code></pre>