SQL minus operation using join

Question

I am trying to construct a SQL query. I have two tables tblA(data INT) and tblB(data INT) where tblA contains rows 1,2,3,4 and tblB contains 3,4,5,6. What I want to achieve is I want take a join of tblA and tblB and in output I want contents of tblA which are not in tblB.

i.e. minus operation.

How can I achieve this using join in SQL Server 2012

Answer 1

Okay, use EXCEPT.

SELECT data 
FROM   tblA

EXCEPT 

SELECT data 
FROM   tblB 

---

Using your teminology, UNION is add, EXCEPT is minus.

Answer 2

You do not need join. I think you're searching for this:

select data from tblA
where data not in (select data from tblB)

You can also use EXCEPT

select data from tblA
EXCEPT
select data from tblB