CODEWITHSUNDEEP
oracle-databasejoininner-join
user1251973
user1251973

Reputation: 331

Oracle join query

I have two tables

Sample Records from table A

1|xavi     |23
2|christine|24
3|faisal   |25
5|jude     |27

Sample Records from table B

1|xavi     |23
2|christine|22
3|faisal   |23
4|ram      |25

If id values from table A matches in table B than take records from table A only. Also take records which are present in table A only Also take records which are present in table B only

So my result should be

1|xavi     |23
2|christine|24
3|faisal   |25
4|ram      |25
5|jude     |27

Upvotes: 0

Views: 67

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271091

You have a precedence problem here. Take all the records from table A and then the extra records from table B:

select *
from A
union all
select *
from B
where B.id not in (select A.id from A);

You can also express this with a full outer join (assuming id is not duplicated in either table):

select coalesce(A.id, B.id) as id,
       coalesce(A.name, B.name) as name,
       coalesce(A.age, B.age) as age
from A full outer join
     B
     on A.id = B.id;

In this case, the coalesce() gives priority to the values in A.

Upvotes: 0

Thiago C. S Ventura
Thiago C. S Ventura

Reputation: 2602

If the tables has relation you need:

Select DISTINCT * 
  from tableA a
 Inner Join tableB b
    On a.id = b.id

If not:

You have to use UNION and after using DISTINCT. DISTINCT will not permit repeat rows.

Upvotes: 0

fabulaspb
fabulaspb

Reputation: 1263

You can simply use union operator to get unique values from both tables. Operator UNION will remove repeated values.

SELECT * FROM tableA AS t1
UNION
SELECT * FROM tableB AS t2

Upvotes: 1

Brian B.
Brian B.

Reputation: 160

select distinct * FROM 
(
select ID, NAME, AGE from TableA
UNION ALL 
select ID, NAME, AGE from TableB
) TableAB

Some things to consider --> Unless you're updating specific tables and the records are the same, it will not matter which table you're viewing the records from (because they're the same...).

If you want to see which table the records are deriving from, let me know and i'll show you how to do that as well... but the query is more complex and i don't really think it's required for the purpose described above. let me know if this helps... thanks, Brian

Upvotes: 0

Related Questions