Filter value from many columns in table

Question

I trying to create SQL question to my PostgreSQL database. I have table looks like as:

id | column1 | column2| column3 | column4
1  |   1     |   2    |    3    |   1
2  |   21    |   22   |    23   |   5
3  |   31    |   22   |    32   |   6
4  |   54    |   43   |    45   |   4
5  |   43    |   23   |    34   |   4
6  |   43    |   54   |    43   |   2
7  |   54    |   52   |    53   |   8
8  |   21    |   2211 |    43   |   4
9  |   43    |   33   |    45   |   9
10 |   87    |   62   |    11   |   3

I try to write question for return only unique value for chosen column, for example column1:

id | column1 | column2| column3 | column4
1  |  1      |   2    |    3    |   1
2  |  21     |   22   |    23   |   5
3  |  31     |   22   |    32   |   6
8  |  21     |   2211 |    43   |   4
10 |  87     |   62   |    11   |   3

My request:

SELECT id, column1, column2, column3, column4
FROM test AS T
WHERE T.column1 != T.column2
   OR T.column1 != T.column3

In response, I get the entire table, not unique records.

Can someone tell me why this is not working and explain how to write a query that will work.

Answer 1

If I understand correctly, you want rows where their value in column1 does not appear in any other row's column2, column3, or column4.

For this you can do a subquery to check each row against every other row.

select *
from test t1
where not exists (
  select id
  from test t2
  where t2.id <> t1.id and (
     t2.column2 = t1.column1 or
     t2.column3 = t1.column1 or
     t2.column4 = t1.column1
  )
)

Demonstration

Note that the need to check different columns for uniqueness suggests that column1, column2, column3, and column4 are actually a list of the same type of value. It would be better to store the values a join table.

create table test (
  id bigserial primary key
);

create table test_stuff (
  test_id bigint not null references test,
  value integer not null
);

insert into test values (1);
insert into test_stuff values (1, 1), (1,2), (1,3), (1,1);

Answer 2

It would be something like this:

SELECT *
FROM TEST
WHERE
    COLUMN1 NOT IN (
        SELECT COLUMN2
        FROM TEST
    )
    AND COLUMN1 NOT IN (
        SELECT COLUMN3
        FROM TEST
    )

You can try here: https://www.db-fiddle.com/f/mmMr3vz2JHvfBabu62Si79/0