title: WHERE clause - record with a NULL column is not displayed
body: <p>[Corporate confidential information or graphics removed]</p>
  <p><strong>Conception</strong></p>
  <p>In the <strong>Org_Structure</strong> there are unique combinations of departments and offices. In the <strong>Emp_Positions</strong> table each employee is assigned to the certain combination of department/office or <strong>&quot;Chain&quot;</strong> in the organizational structure. But some employees should not belong to offices, so they are assigned to a chain where <strong>office_id</strong> column is NULL.</p>
  <p><strong>The Bug</strong></p>
  <p>The provided stored procedure can find only employees of those chains where both dep_id and office_id are NOT NULL. If in some record office_id is NULL, or dep_id is NULL, this record will not be displayed. See the second picture, the record in red color is never displayed in the results. Once I replace NULL with a value, it is displayed. If I replace any column of any row with NULL, this whole row will not display.</p>
  <p><strong>This is the stored procedure:</strong></p>
  <pre><code>set ANSI_NULLS ON
  set QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE FilterEmpList
      @empName nvarchar(250) = null,
      @empDepID int = null,
      @empOfficeID int = null,
      @empPosID int = null    
  AS 
  BEGIN
  SELECT 
      E.emp_id,
      E.emp_name,      
      P.pos_name,
      D.dep_name,
      O.office_name
  FROM dbo.Org_Structure OS 
      JOIN dbo.Emp_Positions EP ON OS.chain_id=EP.chain_id  
      JOIN dbo.Employees E ON EP.emp_id=E.emp_id 
      JOIN dbo.Positions P ON P.pos_id=EP.pos_id
      JOIN dbo.Departments D ON D.dep_id=OS.dep_id
      LEFT JOIN dbo.Offices O ON O.office_id=OS.office_id  
  WHERE (E.emp_name LIKE '%'+@empName+'%' OR @empName IS NULL)
      AND OS.dep_id = ISNULL(@empDepID, OS.dep_id)
      AND OS.office_id = ISNULL(@empOfficeID, OS.office_id)
      AND EP.pos_id = ISNULL(@empPosID, EP.pos_id)
  END
  </code></pre>
  <p>*with <strong>LEFT JOIN</strong> added it still doesn't work</p>