title: SQL Using to_char(null), to_number(null), etc in set operators with Oracle
body: <p>I'm a student entering the world of SQL for the first time and I cannot find any clear and understandable documentation on how to use to_somedatatype(null) to substitute for missing columns in a UNION, INTERSECT, etc.  I'm using the HR schema that comes build in with Oracle Database for my first few projects and the following query works (I get a list of the employee_id numbers for only those employees with dependents):</p>
  
  <pre><code>SELECT employee_id "Employees with Dependents"
  FROM employees
  INTERSECT
  SELECT relative_id
  FROM dependents;
  </code></pre>
  
  <p>However, as soon as I want to include the names of the employees and dependent birth dates, I'm stuck.  This variation perform an error-free search but yields no results (outputs "no rows selected"):</p>
  
  <pre><code>SELECT e.employee_id "Employees with Dependents",
    e.first_name,
    e.last_name,
    to_date(NULL) "Dependent Birthdate"
  FROM employees e
  INTERSECT
  SELECT d.relative_id,
    TO_CHAR(NULL),
    TO_CHAR(NULL),
    d.birthdate
  FROM dependents d;
  </code></pre>
  
  <p>Several variations replacing to_date with to_number or just plain null and to_char with to_varchar2 or null fail to produce any rows.</p>
  
  <p>What I'd like to produce is a single row for each dependent which displays the guardian's employee number, first name, last name, and the birth date of the dependent.  I'm confident that my problem is the use of these null placeholders, combined with limited understanding of set operators in general. (My understanding is that, when I used a null placeholder like to_date(null), it will not be included in the comparison since there's nothing there to be compared to.)</p>
  
  <p>Can someone please explain to me how to properly use these placeholders?</p>