Search code examples
sqlselectinner-join

Get values from one table that are not in another

The first query works to get a value from t1 that is not in t2. 

select t1.*
from table1 t1
where t1.id = '1'
and t1.id2 not in (select t2.id2 from table2 t2 where t2.id='1')

But how do how can I do the same thing using this format instead of an inner select?

select t1.*
from table1 t1, table2 t2
where t1.id = '1'
and t2.id = t1.id
and t1.id2 != t2.id2
Solution

  • You can use a LEFT OUTER JOIN:

    select t1.*
from table1 t1
   LEFT OUTER JOIN table2 t2
      ON t1.id = t2.id
WHERE t2.id IS NULL

    LEFT OUTER JOIN says to take all results from the LEFT-most table (table1 here) and only those results from your joined table (table2 here) that match the ON condition. By filtering in your WHERE clause to drop any results where table2.id IS NULL you effectively only leave T1 results that are unmatched to T2.

    Also try to stay away from the FROM table1, table2 type of implicit join. It's very old school. INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN are better options for nearly any situation.