如何从多表中不存在的表中获取不同的值

I am trying to get a unique list of values from a table which is not present in the corresponding columns in other 2 tables( multiple tables)

我试图从表中获取一个唯一的值列表,该列表不存在于其他2个表(多个表)的相应列中

Here is how my Table looks :

以下是我的表格:

-----------         -----------         -----------
Table1              Table2              Table3
---|-------         ---|-------         ---|-------
id | Value          id | Value          id | Value
---|-------         ---|-------         ---|-------
1  | A              1  | A              1  | A
2  | B              2  | C              2  | D
3  | C              3  | D              3  | E
4  | D              4  | G              4  | F

Now, the unique value of Table1 is "B" ( This value is not present in Table2 and Tabl3).

现在,Table1的唯一值是“B”(Table2和Tabl3中不存在该值)。

Similarly unique value of Table2 is "G". Similarly unique value of Table3 is "E, F".

类似地,表2的唯一值是“G”。类似地,表3的唯一值是“E,F”。

I am using the following query :

我使用以下查询:

select Value from Table1 where Table1.Value NOT IN (select Value from Table2);

Any idea how to extend into 2 tables ( or more)?

知道如何扩展到2个表(或更多)吗?

Thanks

2 个解决方案

#1

Use and:

select Value
from Table1
where Table1.Value not in (select Value from Table2) and
      Table1.Value not in (select Value from Table3) ;

I discourage the using NOT IN with subqueries. It doesn't behave as you would expect with NULL values. If any value in the subquery is NULL, then all rows are filtered out.

我不鼓励在子查询中使用NOT IN。它的行为与您对NULL值的预期不同。如果子查询中的任何值为NULL,则过滤掉所有行。

Instead, use NOT EXISTS:

相反,使用NOT EXISTS:

select t1.Value
from Table1 t1
where not exists (select 1 from table2 t2 where t2.value = t1.value) and
      not exists (select 1 from table3 t3 where t3.value = t1.value);

#2

You may also use left joins here:

你也可以在这里使用左连接:

SELECT t1.Value
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t1.Value = t2.Value
LEFT JOIN Table3 t3
    ON t1.Value = t3.Value
WHERE
    t2.Value IS NULL AND t2.Value IS NULL;

#1