Oracle SQL UNION ALL where T1.ID <> T2.ID -

i have 2 tables test_table , test_table2. want union all or union of 2 want rows test_table2 not have id in test_table. ids unique either table or exist in both tables populated 2 different systems.

i have been running statement , handling duplicate ids down road there not duplicate ids.

select id, date_value, text_value, tablesource test_table union select id, other_date_value date_value, other_text_value text_value, tablesource test_table2; 

here tables.

http://sqlfiddle.com/#!2/2db484/2

i using "oracle database 11g enterprise edition release 11.2.0.3.0 - 64bit production"

i have not been able sql this. how can these rows.

you can left join test_table testtable2 find rows don't match:

select    id, date_value, text_value, tablesource  test_table union -- or union all, might faster select    t2.id, t2.other_date_value date_value, t2.other_text_value text_value, t2.tablesource     test_table2 t2   left join test_table t on t.id = t2.id   t.id null 

http://sqlfiddle.com/#!2/2db484/6

alternatively, can use where not exists ... return rows t2 don't have matching row in t:

select    id, date_value, text_value, tablesource  test_table union -- or union all, might faster select    id,    other_date_value, -- note, don't need alias in second query of union.   other_text_value text_value, -- although can leave 'self-documentation'.   tablesource     test_table2 t2   not exists      (select 'x' test_table t t.id = t2.id) 

also, have @ minus , intersect, can used union, create dataset without matching record in another, or dataset of on records match other dataset. see: http://docs.oracle.com/cd/b19306_01/server.102/b14200/queries004.htm

i don't think either minus or intersect make specific case easier, may useful if have similar issue.