I have two tables. And I want to avoid duplicate values where one column value will get from any table. table a

id  | value | name  | pid
---- ------- ------- -----
1   |  55   |  a    |  27
2   |  56   |  b    |  23
3   |  57   |  c    |  22

table b

id  | value | name  | pid
---- ------- ------- -----
1   |  55   |  a    |  29
5   |  58   |  d    |  23
6   |  59   |  e    |  22

expected result

id  | value | name  | pid
---- ------- ------- -----
1   |  55   |  a    |  27
2   |  56   |  b    |  23
3   |  57   |  c    |  22
5   |  58   |  d    |  23
6   |  59   |  e    |  22

here

1   |  55   |  a    |  29

the row will be removed.

CodePudding user response：

You can use the UNION operator with SELECT DISTINCT to achieve the desired result:

SELECT DISTINCT id, value, name, pid
FROM (
  SELECT id, value, name, pid
  FROM table_a
  UNION
  SELECT id, value, name, pid
  FROM table_b
) result;

CodePudding user response：

Use UNION ALL to concatenate the two tables with an additional priority column and then use the ROW_NUMBER analytic function to find each row with the highest priority:

SELECT id,
       value,
       name,
       pid
FROM   (
  SELECT id,
         value,
         name,
         pid,
         ROW_NUMBER() OVER (PARTITION BY id, value, name ORDER BY priority) AS rn
  FROM   (
    SELECT id, value, name, pid, 1 AS priority FROM a UNION ALL
    SELECT id, value, name, pid, 2 AS priority FROM b
  )
)
WHERE  rn = 1;

Which, for the sample data:

CREATE TABLE a (id, name, value, pid) AS
SELECT 1, 'a', 55, 27 FROM DUAL UNION ALL
SELECT 2, 'b', 56, 23 FROM DUAL UNION ALL
SELECT 3, 'c', 57, 22 FROM DUAL;

CREATE TABLE b (id, name, value, pid) AS
SELECT 1, 'a', 55, 29 FROM DUAL UNION ALL
SELECT 5, 'd', 58, 23 FROM DUAL UNION ALL
SELECT 6, 'e', 59, 22 FROM DUAL;

Outputs:

fiddle