title: Combining IN and NOT IN in SQL as single result
body: <p>I apologize for the vague title. I am attempting to write a query that returns an alias column with matching values (resulting from an IN) as well as an alias column with values that do not match (using NOT IN). I want the result set to have: userId | matches | nonmatches. I currently have the following query which returns the matches as expected. I am having trouble getting the nonmatches in the result set -- that is, from a NOT IN statement</p>
  
  <pre><code>SET @userId = 9;
  SELECT ug.user_id, COUNT(DISTINCT goal_id) as matches
  FROM user_goal ug
  WHERE ug.user_id!=@userId
  AND goal_id IN (SELECT iug.goal_id FROM user_goal iug WHERE user_id=@userId)
  GROUP BY user_id ORDER BY matches DESC LIMIT 4
  </code></pre>
  
  <p>So, the NOT IN would look something like this:</p>
  
  <pre><code>goal_id NOT IN(SELECT uggg.goal_id FROM user_goal uggg WHERE user_id=@userId) AS nonmatches
  </code></pre>
  
  <p>I am just not sure how to incorporate the NOT IN statement in my query so I get all the results</p>