sqljoinleft-joinnot-exists

LEFT JOIN in SQL. How to exclude all the rows from the T1 in T2?

T1 - table one with only one column

Bart
Philip
Beth

T2 - table two with only one column

Robert
Bart
Philip
Ann
Jack
Helen
Beth

The expected result after JOIN is:

Robert
Ann
Jack
Helen
Solution

  • What you are after is a simple outer join

    select t2.col 
from t2
left join t1 on t2.col=t1.col
where t1.col is null

    You can also express it as a not exists

    select * 
from t2
where not exists(select 1 from t1 where t2.col=t1.col)

    You can also express it using except

    select * 
from t2
except
select * 
from t1