Compare two columns and replace duplicates with nulls

Question

I have a table like this:

id  | t1 | t2
---------------
 1  |  a |  b
 2  |  c |  a
 3  |  a |  e
 4  |  f |  g
 5  |  c |  c

I want to compare columns t1 and t2 with each other to remove matching values and get unique values in each columns, like this:

   t1 | t2
  -----------
    a |  b
    c |  null
 null |  e 
    f |  g
  null| null

It doesn't matter much which row to choose to show the value and which other ones would show nulls. For instance, as value a is also found in row 3 of the original dataset, the following output would also be valid:

   t1 | t2
  -----------
 null |  b
    c |  null
    a |  e 
    f |  g
  null| null

The end goal is to show all distinct values and no identical values across the two columns.

Andriy M · Accepted Answer · 2017-12-05 14:08:55Z

Without window functions this is pretty hard in MySQL. Since the two columns - t1 and t2 - store similar content and you only want disctinct values from both, it would be much easier to get the values in a single column:

select t1 as tx from t
union distinct
select t2 from t ;

I don't see any reason to have the original convoluted result, except if you want to keep info from other columns in the table and just remove duplicates from these 2 columns. And in that case, an UPDATE would make more sense.

Here is a method to get this result, anyway. It assumes that (id) has a UNIQUE constraint. Tested at dbfiddle.uk:

select t.id, gt1.tx as t1, gt2.tx as t2
from t
  left join
  ( select ut.tx, min(ut.id) as id
    from
      ( select id, t1 as tx from t
        union all
        select id, t2 from t
      ) ut 
    group by ut.tx
  ) as gt1
  on t.t1 = gt1.tx and t.id = gt1.id
  left join
  ( select ut.tx, min(ut.id) as id
    from
      ( select id, t1 as tx from t
        union all
        select id, t2 from t
      ) ut 
    group by ut.tx
  ) as gt2
  on t.t2 = gt2.tx and t.id = gt2.id and gt1.tx <> gt2.tx
 ;

Notice that in MariaDB (MySQL's first cousin), that has CTEs, the same query can be rewritten more compactly and more clearly:

with gt as 
  ( select ut.tx, min(ut.id) as id
    from
      ( select id, t1 as tx from t
        union all
        select id, t2 from t
      ) ut 
    group by ut.tx
  ) 
select t.id, gt1.tx as t1, gt2.tx as t2
from t
  left join gt as gt1
    on t.t1 = gt1.tx and t.id = gt1.id
  left join gt as gt2
    on t.t2 = gt2.tx and t.id = gt2.id and gt1.tx <> gt2.tx
 ;

Logically, both variations work the same way, though. This subselect:

  ( select ut.tx, min(ut.id) as id
    from
      ( select id, t1 as tx from t
        union all
        select id, t2 from t
      ) ut 
    group by ut.tx
  )

returns all distinct t1 and t2 values along with the ID of the first¹ row where each is encountered. For your example, it produces this output:

tx   id
---  ---
a    1
b    1
c    2
e    3
f    4
g    4

The above set is joined against the original table twice, on t1 and on t2. In each case, where the original row's ID matches the subselect row's ID, the value is returned intact, because the match indicates that the row is the value's first occurrence; otherwise the value is replaced with a null.

_{¹“First" in the order of ID.}

Andriy M · Accepted Answer · 2017-12-06 10:55:41Z

This problem can also be solved in a very MySQL-specific way, using a variable. The variable will store a unique CSV list of values found in t1 and t2. The value of each column will first be tested against the list (if found, a null will be returned, otherwise the value), then added to the list (if absent from it).

This is my implementation of the above logic:

SELECT
  id,
  IF(FIND_IN_SET(t1, @list), NULL, t1) AS t1,
  @list := CONCAT_WS(',', @list, IF(FIND_IN_SET(t1, @list), NULL, t1)),
  IF(FIND_IN_SET(t2, @list), NULL, t2) AS t2,
  @list := CONCAT_WS(',', @list, IF(FIND_IN_SET(t2, @list), NULL, t2))
FROM
  (SELECT @list := '') AS x,
  YourTable AS t
;

Updating the list is implemented in the form of additional computed columns without explicit aliases, namely these:

  ...
  @list := CONCAT_WS(',', @list, IF(FIND_IN_SET(t1, @list), NULL, t1)),
  ...
  @list := CONCAT_WS(',', @list, IF(FIND_IN_SET(t2, @list), NULL, t2))
  ...

Those columns are not needed in the output, they only serve as steps of the solution. In order to avoid returning them, use the query as a derived table, so that only the columns you want can be pulled from it:

SELECT
  id,
  t1,
  t2
FROM
  (
    SELECT
      id,
      IF(FIND_IN_SET(t1, @list), NULL, t1) AS t1,
      @list := CONCAT_WS(',', @list, IF(FIND_IN_SET(t1, @list), NULL, t1)),
      IF(FIND_IN_SET(t2, @list), NULL, t2) AS t2,
      @list := CONCAT_WS(',', @list, IF(FIND_IN_SET(t2, @list), NULL, t2))
    FROM
      (SELECT @list := '') AS x,
      YourTable AS t
  ) AS derived
;

Note that the query does not specify an order in which the rows are read or returned. The solution will still work regardless of the row order but absence of an explicit ORDER BY ultimately means that the output might be different between different runs of the query. The issue is actually twofold. Because the rows are read without an explicit order, the query might produce different rows for the output, and the other problem is that they may also be returned in different order with each run of the query.

To resolve this and make the output completely repeatable, you probably need to supply an ORDER BY both at the nested level and at the outer level, something like this:

SELECT
  id,
  t1,
  t2
FROM
  (
    SELECT
      id,
      IF(FIND_IN_SET(t1, @list), NULL, t1) AS t1,
      @list := CONCAT_WS(',', @list, IF(FIND_IN_SET(t1, @list), NULL, t1)),
      IF(FIND_IN_SET(t2, @list), NULL, t2) AS t2,
      @list := CONCAT_WS(',', @list, IF(FIND_IN_SET(t2, @list), NULL, t2))
    FROM
      (SELECT @list := '') AS x,
      YourTable AS t
    ORDER BY
      id ASC
  ) AS derived
ORDER BY
  id ASC
;

An online demo of this solution can be found at Rextester or dbfiddle.uk. (The test setup was borrowed from ypercubeᵀᴹ's demo.)

Stack Exchange Network

Compare two columns and replace duplicates with nulls

2 Answers 2

Your Answer

Hot Network Questions

Compare two columns and replace duplicates with nulls

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions