How can I find the unique phone numbers (and collapse them into a single column) from table_1 (while keeping the IDs and date fields), and remove phone numbers that appear in table_2?

table_1

table_2

regardless of which phone column the phone appears in, I want to remove it from the end results. so ID 1 with phone of 1111111111 would be removed because it is in phone2 on Table 2

Desired output

What I have so far, which seems to work, is this. I feel like there has to be a more efficient way to do this though.

select * from (
    select id, phone1 as phone_num, date from table_1
    union all
    select id, phone2 as phone_num, date from table_1
) tmp

where phone_num not in (
    select phone1 as phone_num from table_2
    union all
    select phone2 as phone_num from table_2
)

order by id desc;

CodePudding user response：

This is a bit of overkill but it shows 2 things:

1. We can reduce the volume of records being evaluated by eliminating those without a value in phone2 to begin with which should help with performance. (assuming values are NULL and not empty set)
2. We use multiple CTE's to break out what where doing and why and use the EXCEPT set operator to exclude records from the "ExclusionSET" from the "baseSET". This in my opinion improves ability to maintain/read and may have a slight performance gain as "set" based operations tend to perform better; but not always so testing would have to be performed to "know",

.

WITH baseSET as (
    select id, phone1 as phone_num, date from table_1
    union all
    select id, phone2 as phone_num, date from table_1 Where Phone2 is not null
),

ExclusionSET as (
    select phone1 as phone_num from table_2
    union all
    select phone2 as phone_num from table_2
),

ResultSET as (
SELECT *
FROM baseSET
EXCEPT
SELECT *
FROM ExclusionSET)

SELECT * 
FROM ResultSET
ORDER BY id desc;

CodePudding user response：

This is a little "wordy" but breaks it down into a couple of stages using CTEs to define a single list of numbers to exclude then a valid list of numbers:

with exclude as (
    select phone1 as p from t2 union
    select phone2 from t2
), nos as (
    select case when phone1=p then null else phone1 end Phone1,
        case when phone2=p then null else phone2 end Phone2,
        id, date
    from t1
    left join exclude x on x.p=t1.phone1 or x.p=t1.phone2
)
select id, phone1 PhoneNum, date
from nos
where phone1 is not null
union 
select id, phone2, date
from nos
where phone2 is not null;

CodePudding user response：

cte1 is the union of phone numbers from table_1

and

cte2 is the union of phone numbers from table_2

final result is a table of numbers from left join cte1 to cte2

with cte1 as (
    select id,phone1 phone_num,cr_date from table_1
        union
    select id,phone2 phone_num,cr_date from table_1 where phone2 is not NULL),
    cte2 as (select phone1 phone_num from table_2
         union 
    select phone2 from table_2 phone_num where phone2 is not NULL)
    select id,cte1.phone_num, cr_date from cte1 left join cte2 on (cte1.phone_num=cte2.phone_num)
           where cte2.phone_num is NULL order by id;

results

id | phone_num  |  cr_date
---- ------------ ------------
  2 | 2222222222 | 2020-11-08
  2 | 3333333333 | 2020-11-08
  3 | 5555555555 | 2021-03-15
  4 | 7777777777 | 2016-10-20