Extract the different recored in two table

Question

I have two tables - table1 and table2. Both tables have the same columns.

I want to extract the record differences - meaning extract records in table1 not in table2, and extract records in table2 not in table1.

How I can do that by using SQL in Oracle Sys?

RomanKonz · Answer 1 · 2012-09-25T09:07:30.930

3

to get all differences in both tables in one result set you can use

select columnlist
from tableA
minus
select columnlist
from tableB
union all
select columnlist
from tableB
minus
select columnlist
from tableA

edited Sep 25 '12 at 09:07

answered Sep 25 '12 at 08:56

RomanKonz

1,027
1
8
15

It's `minus` in Oracle instead of `except` – Sep 25 '12 at 09:05

score 0 · Answer 2 · answered Sep 25 '12 at 08:54

0

MINUS Takes the result set of one SELECT statement, and removes those rows that are also returned by a second SELECT statement.

This query will return all rows that are in table1, but not in table2:

SELECT * FROM table1
MINUS
SELECT * FROM table2;

answered Sep 25 '12 at 08:54

CloudyMarble

36,908
70
97
130

score 0 · Answer 3 · answered Sep 25 '12 at 08:55

0

select * from table1 where pk_col not in(select pk_col from table2)

select * from table2 where pk_col not in(select pk_col from table1)

answered Sep 25 '12 at 08:55

AnandPhadke

13,160
5
26
33

This is an English-only site; please write in English. – BoltClock Sep 25 '12 at 11:15

score 0 · Answer 4 · answered Sep 25 '12 at 08:57

0

select t1.column1,t1.column2 from table1 t1 except select t2.column1,t2.column2 from table 2 t2 UNION all select t2.column1,t2.column2 from table 2 t2 except select t1.column1,t1.column2 from table1 t1

answered Sep 25 '12 at 08:57

Mukesh Kumar

945
4
11
26

score 0 · Answer 5 · answered Sep 25 '12 at 08:58

0

select t1.Id,t1.name,t2.Id,t2.name from table1 t1 ,table2 t2 where t1.Id and t1.Name not in(select * from  table2) and t2.Id,t2.name not in (select * from table 2)

answered Sep 25 '12 at 08:58

Freelancer

9,008
7
42
81

score 0 · Answer 6 · answered Sep 25 '12 at 10:17

RomanKonz's solution, with additional information which table actually contains the rows (I usually use WITH to simplify the actual UNION ALL / MINUS part):

with 
  v_a as (
    select *
    from tableA), 
  v_b as (
    select *
    from tableB)
select * from 
(
 (select 'A only' src, v.* from v_a v
  minus 
  select 'A only' src, v.* from v_b v
 ) 
 union all
 (select 'B only' src, v.* from v_b v
  minus
  select 'B only' src, v.* from v_b v
 )
) order by primarykeycolumn, src

Extract the different recored in two table

6 Answers6