title: SQL Intelligent Joins
body: <pre><code>;WITH tblEmployee AS (
  SELECT * FROM (VALUES
  (1, 'Tom', 'Male', 4000, 1),
  (2, 'Pam', 'Female', 3000, 3),
  (3, 'John', 'Male', 3500, 1),
  (4, 'Sam', 'Male', 4500, 2),
  (5, 'Todd', 'Male', 2800, 2),
  (6, 'Ben', 'Male', 7000, 1),
  (7, 'Sara', 'Female', 4800, 3),
  (8, 'Valarie', 'Female', 5500, 1),
  (9, 'James', 'Male', 6500, NULL),
  (10, 'Russell', 'Male', 8800, NULL)) AS E(ID, Name, Gender, Salary, DepartmentId)
  ), 
  tblDepartment AS (
  SELECT * FROM (VALUES
  (1, 'IT', 'London', 'Rick'),
  (2, 'Payroll', 'Delhi', 'Ron'),
  (3, 'HR', 'New York', 'Christie'),
  (4, 'Other Department', 'Sydney', 'Cindrella')) AS D(Id, DepartmentName, Location, DepartmentHead)
  )
  </code></pre>
  
  <p>Left table is employee table and right tables department table.</p>
  
  <p>I am following a tutorial online and am confused by the explanation the instructor provided:</p>
  
  <p>If I run this query on the two tables above:</p>
  
  <pre><code>Select E.Name, E.Gender, E.Salary, D.DepartmentName
  from tblEmployee as E
  Left Join tblDepartment as D
  On tblEmployee.departmentID = tblDepartment.Id
  Where tblEmployee.departmentID IS Null;
  </code></pre>
  
  <p>I am a little confused on what the last line in the SQL code above does. When you say:</p>
  
  <pre><code>Where tblEmployee.departmentID IS Null
  </code></pre>
  
  <p>Is the above line of code being executed on the new table that is formed after these lines of code are run:</p>
  
  <pre><code>Select E.Name, E.Gender, E.Salary, D.DepartmentName
  from tblEmployee as E
  Left Join tblDepartment as D
  On tblEmployee.departmentID = tblDepartment.Id
  </code></pre>
  
  <p>So basically it checks on the new table formed: only where you have the department ID as NULL show me those rows. I just wanted to clarify that this line: <code>Where tblEmployee.departmentID IS Null</code> is being run after the new table is formed correct?</p>