I have two tables A & B. I want to extract column 1 from table A and make sure that I only extract data that does not exist in column 1 & 2 in table B. How can I achieve this?

Additionally, column 2 in table B contains aggregated data, which means that the data looks like［a,b,c,d］.

Example:

Table A

Table B

In this example, I want to extract only 'e' from table A as it is not in either column_1 or column_2 from table B.

CodePudding user response：

One way is to concatenate to columns of Table B and unnest the resulting array and then join or use not in to filter values in Table A:

-- sample data
WITH dataset(column_1) AS (
    values ('a'),
        ('b'),
        ('c'),
        ('d'),
        ('e')
),
dataset2(column_1, column_2) as (
    values ('a', array['a', 'b' , 'c', 'd']),
        ('z', array['c', 'b' , 's', 'f']),
        ('x', array['g', 'h' , 'i', 'j']),
        ('y', array['k', 'l' , 'm', 'n']),
        ('z', array['o', 'p' , 'q', 'r'])
)

-- query
select *
from dataset
where column_1 not in (select distinct col
from dataset2,
     unnest(column_1 || column_2) as t(col) );

Output: