CODEWITHSUNDEEP
hadoophive
user3504024
user3504024

Reputation: 149

distinct values from 2 tables

assume I have 2 tables as below.

ID, NAME, NO
1, PK,101
2,AK,102
3,OK,103

ID,CITY,COUNTRY
1,NY,US
2,NJ,US

I need to get output as this, only the ID value

3

any help here.?

Upvotes: 2

Views: 105

Answers (3)

Anil
Anil

Reputation: 420

As @DRAGO replied, an OUTER JOIN should be sufficient & efficient in this case !

Upvotes: 0

Ankit Agrahari
Ankit Agrahari

Reputation: 379

select a.ID from table1 a left outer join table2 b on b.ID=a.ID where b.ID is null;

Upvotes: 1

Amar
Amar

Reputation: 3845

You can basically do a FULL JOIN on both the tables and filter rows where ID is NULL for any of the tables. Assuming you have two tables named temp_table1 and temp_table2, following query should work:

select * from (select (CASE WHEN temp_table1.id is NULL THEN temp_table2.id WHEN temp_table2.id is NULL THEN temp_table1.id ELSE NULL END) as id from temp_table1 FULL JOIN temp_table2 on temp_table1.id = temp_table2.id)q1 where id is not null;

Explanation: First I am doing a full join on both the tables to get all the rows corresponding to both the tables.

Then in case condition I am checking that if id is NULL in table 1 then we will return the id from table 2.

Similarly if id is NULL in table2 we will return the id from table1. And if it is not NULL in any of the tables then we will return NULL.

Finally we are putting this in a sub query and returning rows where returned id is not null. Hope this helps.

Upvotes: 0

Related Questions