获取不匹配的记录Sql Server表

One is Demand table and another table is VarientCountry i want only thoes varient from demand table which has no country mapping in varientcountry table

一个是需求表，另一个是变量国家表，我只需要它与需求表的变量，它在变量国家表中没有国家映射

Demand Table

需求表

Id Varient Country
1     v1       India
2     v2       NULL
3     v3       Nepal
4     v4       Japan

VarientCountry Table

VarientCountry表

Id Varient Country
1    v1       India
2    v1       Uk
3    v2       China
4    v1       Indonisia
5    v3       Nepal
6    v4       Egland
7    v4       Null

I want Excepted Result like as

我想要的结果是

Id Varient Country
1     v1       UK
3     v2       China
6     v4       England

2 个解决方案

#1

You have to use except one.

你只能用一个。

You have to select the record from demand table except record from varientCountry table. This is the right one.

您必须从需求表中选择记录，但是从varientCountry表中选择记录。这是正确的。

   select varient, Country
    from demand
    except
    select varient, country
    from varientCountry;

You will get the below output

您将得到以下输出

Id Varient Country
1     v2       Null
2     v4       Japan

#2

You can use except if you can live without the ids:

你可以使用，除非你可以生活没有身份证:

select Varient, Country
from VarientCountry
except
select varient, country
from demand;

If you need the ids, I'd go for not exists:

如果您需要id，我将不存在:

select vc.*
from varientcountry vc
where not exists (select 1
                  from demand d
                  where d.varient = vc.varient and
                        (d.country = vc.country or d.country is null and vc.country is null)
                 );

#1