How to compare the values ​​of two tables in Oracle where there are no fields with unique values ​​and which can be repeated?

Question

Oracle

The tables have the same fields number, date, value1(float), value2(float).
The number and date in a pair can be duplicated, but have different meanings.
The number of rows in the tables may also vary.
The goal is to compare the values ​​of the tables as accurately as possible and display the differing ones, but if the number, date and some one value match, but the second does not, they should be displayed in one line.
I went through various variations of the joins, but there was no sensible solution.
Any ideas?

Answer 1

-- Создали таблицы
CREATE TABLE table1 (Numer INT,Data DATETIME , value1 FLOAT,value2 FLOAT)
CREATE TABLE table2 (Numer INT,Data DATETIME , value1 FLOAT,value2 FLOAT)

go
-- Очищаем таблицы
TRUNCATE TABLE table1
TRUNCATE TABLE table2

-- Заполняем данным
INSERT INTO dbo.table1 ( Numer, Data, value1, value2 ) VALUES  ( 1, '01.01.2015', 1.1, 1.2  ) -- только в первой таблице
INSERT INTO dbo.table1 ( Numer, Data, value1, value2 ) VALUES  ( 1, '01.01.2015', 1.2, 1.2  )
INSERT INTO dbo.table1 ( Numer, Data, value1, value2 ) VALUES  ( 2, '01.01.2014', 2.1, 2.2  )
INSERT INTO dbo.table1 ( Numer, Data, value1, value2 ) VALUES  ( 5, '01.01.2014', 3.1, 1.2  )
INSERT INTO dbo.table1 ( Numer, Data, value1, value2 ) VALUES  ( 4, '01.01.2015', 1.1, 4.2  )

-- Заполняем данным
INSERT INTO dbo.table2 ( Numer, Data, value1, value2 ) VALUES  ( 1, '01.01.2015', 1.2, 1.2  )
INSERT INTO dbo.table2 ( Numer, Data, value1, value2 ) VALUES  ( 2, '01.01.2014', 2.1, 2.2  )
INSERT INTO dbo.table2 ( Numer, Data, value1, value2 ) VALUES  ( 3, '01.01.2014', 5.1, 1.2  ) -- Искажение в первом значении
INSERT INTO dbo.table2 ( Numer, Data, value1, value2 ) VALUES  ( 4, '01.01.2015', 1.1, 4.3  ) -- Искажение в втором значении

go
SELECT 'Нет совпадения по номеру и дате'
,
* FROM dbo.table1 t1
FULL JOIN dbo.table2 t2
ON t2.Data = t1.Data AND t2.Numer = t1.Numer 
WHERE t2.Numer IS NULL OR t1. Numer IS NULL

UNION ALL

SELECT 'Нет совпадения по первому значению'
, * FROM dbo.table1 t1
JOIN dbo.table2 t2
ON t2.Data = t1.Data AND t2.Numer = t1.Numer 
AND t2.value2 = t1.value2
where t2.value1 <> t1.value1

UNION ALL

SELECT 'Нет совпадения по второму значению'
, * FROM dbo.table1 t1
 JOIN dbo.table2 t2
ON t2.Data = t1.Data AND t2.Numer = t1.Numer 
AND t2.value1 = t1.value1
where t2.value2 <> t1.value2