SQL Query: Without using Subquery

Question

how can i get all the records from 1st table not present in the 2nd table without using subquery? i would like to use Join...

Answer 1

SELECT A.someColumn
FROM A LEFT JOIN B
ON A.ID = B.ID
WHERE B.ID IS NULL

Answer 2

You can even use

a) EXCEPT

b) Where Not in

e.g. Sample Data

declare @t1 table(id1 int, recordsA varchar(20))
insert into @t1 
select 1,'record1' union all 
select 2,'record2' union all
select 3,'record3' union all
select 4,'record4' union all
select 5,'record5'

declare @t2 table(id2 int, recordsB varchar(20))
insert into @t2 
select 1,'record1' union all 
select 2,'record2' union all
select 3,'record3' 

Query:1

select t1.id1,t1.recordsA from @t1  t1
except
select t2.id2,t2.recordsB from @t2 t2

Query 2:

select t1.id1,t1.recordsA from @t1  t1 
where  t1.recordsA not in(select t2.recordsB from @t2 t2)

Output:

id1 recordsA
4   record4
5   record5