CODEWITHSUNDEEP
sqlsql-server
J.Doe
J.Doe

Reputation: 13

How to get rows having uncommon values in two tables in SQL?

Suppose I have a table a with columns A1,A2,A3,A4 and table b with columns B1,B2,B3,B4. I want to find a records which are having different values in column A1,A2 and B1,B2 Ex.

A1 A2 A3 A4             B1 B2 B3 B4
12 10 10 12             12 10 10 12
14 14 10 12             15 10 10 12
15 10 10 10             15 10 10 10 

IT SHOULD RETURN
14 14 10 10

I tried:

SELECT A1,A2
FROM A
EXCEPT
SELECT B1,B2
FROM B;

However, it returned on A1,A2 columns instead of all columns

Upvotes: 1

Views: 1690

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I would use not exists:

select a.*
from a
where not exists (select 1
                  from b
                  where a.a1 = b.b1 and a.a2 = b.b2
                 );

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

You can try below using left join

select * from tableA
left join tableB on A1=B1 and A2=B2 
where B1 is null and B2 is null

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use left join

 select a.* from tableA a 
       left join tbaleB b 
       on a.A1=b.B1 and a.A2=b.B2 and a.A3=b.B3 and a.A4=b.B4
       where b.B1 is null

Upvotes: 1

Related Questions