title: SQL Join limiting left table by right
body: <p>I need to do a SQL Query where I pull users that are in Table 1 and either not in table 2 at all, or not in a specific subset of table 2. </p>
  
  <p>Here is what I have:</p>
  
  <p>Table ClientUsers<br>
  <strong>ID    UserName</strong><br>
  1&nbsp;&nbsp;     User1<br>
  2&nbsp;&nbsp;     User2<br>
  3&nbsp;&nbsp;     User3<br>
  4&nbsp;&nbsp;     User4<br>
  5&nbsp;&nbsp;     User5<br></p>
  
  <p>Table UserRoles<br>
  <strong>ClientUsersID    ClientRolesID</strong><br>
  1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;101<br>
  2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;166<br>
  5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;103<br></p>
  
  <p>I need to select all users that are either in UsersRoles with any ClientRoleID that is not 166 OR not in User Roles at all. </p>
  
  <p>So in this case, I would select users 1, and 3-5. 1 and 5 because they are in table UserRoles with a role other than 166, and 3 and 4 because they are in table ClientUsers but NOT table UserRoles at all. </p>
  
  <p>I haven't been able to use a null in the WHERE statement because then it's looking for a null value in a column, as opposed to a row that doesn't exist. </p>
  
  <p>I think that I have to use a full outer join, then narrow the results, but I haven't found a query that works yet. </p>
  
  <pre><code>SELECT * FROM dbo.ClientUsers 
   FULL OUTER JOIN dbo.UserRoles on ClientUsers.ID = UserRoles.ClientUsersID  
   WHERE UserRoles.ClientRolesID != 166 OR [value not in right table]
  </code></pre>
  
  <p>I don't know how to replace that bracket with something that will work (or redo the query entirely). </p>