查询使用设置运算符显示特定列

Question

我试图使用设定的操作员在没有城市的情况下显示国家名称（表A col。）和没有国家（a）的城市（b）（b）。我还尝试使用左连接来编写此查询，我在下面显示，并且包括表C（区域），因为我不确定是否在左联接中使用该主键。

表A（国家）：

Column_name   |   Column_id
              |
COUNTRY_ID    |     1
COUNTRY_NAME  |     2
REGION_ID     |     3

表B（位置）：

Column_name   |   Column_id
              |
LOCATION_ID   |     1
   CITY       |     4
COUNTRY_ID    |     6

表C（区域）：

Column_name   |   Column_id
              |
 REGION_ID    |     1

我尝试了以下内容：

SELECT c.country_name, l.city
FROM Countries c
LEFT JOIN Locations l ON c.country_id = l.country_id

UNION

SELECT c2.country_name, l2.city
FROM Countries c2
LEFT JOIN Locations l2 ON c2.country_id = l2.country_id;

上面的SQL语句返回了所有表A值，表A值不包含表B值（不包含表B值的国家）有城市）。

我还尝试了下面的此说明，并得到了完全相同的结果：

SELECT c.country_name, l.city
FROM Countries c
LEFT JOIN Locations l ON c.country_id = l.country_id
LEFT JOIN Regions r ON r.region_id = c.region_id;

缺少的一件事是表B中未在表B中找到的值（城市中未找到的国家）。

Answer 1

有很多选择可以取得理想的结果。一种方法是使用左JOIN以获取没有城市的国家，右JOIN以无国界获取城市：

SELECT c.country_name, l.city
FROM countries c
LEFT JOIN locations l ON c.country_id = l.country_id
WHERE l.city IS NULL
UNION ALL
SELECT c.country_name, l.city
FROM countries c
RIGHT JOIN locations l ON c.country_id = l.country_id
WHERE c.country_name IS NULL;

另一种可能性是使用两个左JOIN加入，但是从相反的表开始，这样：

SELECT c.country_name, l.city
FROM countries c
LEFT JOIN locations l ON c.country_id = l.country_id
WHERE l.city IS NULL
UNION ALL
SELECT c.country_name, l.city
FROM locations l
LEFT JOIN countries c ON c.country_id = l.country_id
WHERE c.country_name IS NULL;

如果您不喜欢使用JOIN，则可以使用中的进行此操作：

SELECT c.country_name, NULL city
FROM countries c
WHERE country_id NOT IN (SELECT country_id FROM locations)
UNION ALL
SELECT NULL country_name, l.city
FROM locations l
WHERE country_id NOT IN (SELECT country_id FROM countries);

或者，如果您喜欢不存在，这也将起作用：

SELECT c.country_name, NULL city
FROM countries c
WHERE NOT EXISTS (SELECT 1 FROM locations WHERE country_id = c.country_id)
UNION ALL
SELECT NULL country_name, l.city
FROM locations l
WHERE NOT EXISTS (SELECT 1 FROM countries WHERE country_id = l.country_id);

我创建了一个示例，显示所有这些查询将产生身份结果： db＆lt

;您希望将结果设置为它们。

最后一个但重要的说明：如您所见，我使用了Union all而不是Union，因为我看不出为什么使用Union 在您的用例中。 联合所有的速度要快得多，因此我建议使用它，除非有真正令人信服的理由不使用它。 Union的唯一优点是它没有显示重复的行，但我认为它们在您的情况下不太可能，因此不需要。

Answer 2

最简单的说明是使用集合操作员在没有城市的情况下找到国家 /地区的最简单说明是：

select country_id from countries
minus
select country_id from locations

COUNTRY_ID
----------
         1
         4

由于您需要的国家名称，您只需要查找它：

select country_name from countries
minus
select c.country_name from locations l
       join countries c on c.country_id = l.country_id;

COUNTRY_NAME
-----------------
England
Italy

没有国家（或使用无效的国家代码）的城市，因为左JOIN和过滤器：

select l.city, l.country_id
from   locations l
       left join countries c on c.country_id = l.country_id
where  c.country_id is null

CITY             
-----------------
Berlin           
Tokyo            

如果确实需要使用集合操作员来执行此操作，那么您会（从概念上）寻找country_id在（位置国家 /地区国家国家 /地区）中的城市：

select l.location_id, l.city, l.country_id
from   locations l
where  l.country_id in
       ( select country_id from locations
         minus
         select country_id from countries )

但是，这不会给您带来country_id的位置。