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

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

CodePudding user response：

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