Help in SQL Query

[Sagar Rohankar]

Hi Ranchers,

There are two tables tableA and tableB and I need a complement of tableA with tableB , I mean, I want to return all the rows which are absents in tableB, like

tableA

eid pid                
1     3
2     5
3     2
4     9
5     1

tableB

eid pid
1     3
2     5
3     2

Here "tableA COMPLEMENT tableB" results into following rows,

eid       pid
4           9
5           1

My first try,

SELECT eid, pid 
FROM tableA 
WHERE CONCAT(eid, pid) NOT IN (SELECT CONCAT(eid, pid) FROM tableB);  

Its working , but I'm not sure whether its good or not !
So any alternatives??

[Jan Cumps]

You are close. Better not concatenate. That is messy.

There are several options:

select.... from A where not exists (select 1 from B where a.eid = b.eid and a.pid = b.pid);

or, in oracle:

select eid, pid  from A
minus
select eid, pid from B;

[Sagar Rohankar]

Ok, thanks, we are concentrating more on first option as we are using MS SQL SERVER.

[Rahul Babbar]

I think operations like "Union", "Minus", "Intersect" are standard Relational Operations that all databases should adhere to.
not sure why SQL Server doesn't support this.

[Sagar Rohankar]

Rahul Babbar wrote:I think operations like "Union", "Minus", "Intersect" are standard Relational Operations that all databases should adhere to.
not sure why SQL Server doesn't support this.

Yes, we checked out this basic set operation first, But SQL SERVER dosen't supports MINUS (Yeh, Its support UNION and INTERSECTION). Actually there in no keyword like, minus. And interestingly doesn't have CONCAT operator (Query is constructed on MySQL first) too.

[Jeanne Boyarsky]

Some database use an alternate keyword for "minus." For example, db2 uses "except." I did check online and SQL Server doesn't have one. Which means this post isn't so much for you as it is for the db2 user who stumbles across this thread.