数据库join教程
在第一次学习SQL时，通常使用单个表中的数据。在实际应用中，数据通常存储在数据库的多个表中。如果我们希望能够处理这些数据，就必须在一个查询中组合多张表。在本SQL join教程中，我们将学习如何使用连接从多个表中查询数据。
Factbook数据库
我们将使用CIA World Factbook (Factbook)数据库的一个版本，它有两张表。第一张表称为facts，每一行代表Factbook中的一个国家。以下是facts表的前5行:
除了facts表之外，还有第二张表，名为cities包含了Factbook中各国主要城市地区的信息(在本教程的其余部分，我们将使用“cities”一词来表示“主要城市地区”)。让我们来看看这个表的前几行，以及每一列的描述:
- id-每个城市的唯一ID。
- name-城市名字。
- population-城市人口。
- capital-城市是否是首都：1代表是，0代表否。
-
facts_id-国家ID，来源于facts表。
我们对最后一列特别感兴趣，因为facts表中也存在的这列数据。表之间的链接非常重要，因为它用于在查询中组合数据。下面是一个模式图，它显示了我们数据库中的两个表，其中的列以及这两个表是如何链接的。
模式图中的这一行清楚地显示了facts表中的id列和cities表中的facts_id列之间的链接。
第一个SQL连接(join)
使用SQL连接数据最常见的方法是使用内部连接。内连接的语法是:
SELECT [column_names] FROM [table_name_one]
INNER JOIN [table_name_two] ON [join_constraint];
内连接查询包含两部分：
- INNER JOIN：告诉SQL引擎查询中希望连接的表，而且是内连接。
- ON：告诉SQL引擎两个表使用哪些列来连接。
在查询语句中join通常都跟随from语句，我们来看一个简单的内连接合并来自两个表的数据：
SELECT * FROM facts
INNER JOIN cities ON cities.facts_id = facts.id
LIMIT 5;
让我们看一下包含join的查询:
- INNER JOIN cities：告诉SQL引擎希望使用内连接来连接cities表到我们的查询中。
-
ON cities.facts_id = facts.id：告诉SQL引擎在连接数据时使用哪列，语法格式：table_name.column_name。
你可能认为设SELECT * FROM facts意味着查询只返回facts表中的列，然而*通配符在使用了join时，将返回两个表中的所有列。以下是查询结果：
这个查询返回了两个表中的所有列，以及fact中的id列和cities中的facts_id匹配的每一行，仅限于前5行。
理解SQL内连接
现在，我们将这两个表连接在一起，以提供关于城市中每一行的额外信息。让我们仔细看看这个内连接是如何工作的。
内连接的工作原理是，只包含每个表中使用ON子句指定的匹配行。让我们看一下前面的join是如何工作的。我们已经包含了最能说明连接的行:
内连接将包含：
- 来自cities表中的行且cities.facts_id的列匹配facts表中的facts.id列
内连接不包含：
- cities表中cities.facts_id和facts表中facts.id不匹配的行
-
facts表中facts.id和cities表中cities.facts_id不匹配的行
你可以用维恩图来表示:
a INNER JOIN b
Left join
正如我们前面提到的，内连接将不包括两个表中没有相互匹配的任何行。这意味着我们可能在查询中看不到不匹配行的信息。
我们可以使用SQL查询来探索这个问题:
SELECT COUNT(DISTINCT(name)) FROM facts;
image.png
SELECT COUNT(DISTINCT(facts_id)) FROM cities;
通过运行这两个查询，我们可以看到facts表中有一些国家在cities表中没有相应的城市，这表明我们可能有一些不完整的数据。
让我们看看如何使用一种新类型的连接(左连接)创建查询来探索缺失的数据。
左连接包括内连接选择的所有行，以及第一个(或左)表中与第二个表不匹配的任何行。我们可以用维恩图来表示。
a LEFT JOIN b
让我们来看一个例子，在我们编写的第一个查询中，将INNER JOIN替换为LEFT JOIN，并查看前面图表中相同的行选择:
SELECT * FROM facts
LEFT JOIN cities ON cities.facts_id = facts.id
这里我们可以看到facts.id和cities.Facts_id(237, 238, 240, 244)不匹配的行也出现在查询结果中。当发生这种情况时，cities表中的所有列都将填充空值。
我们可以使用这些null值来过滤那些国家在cities表中不存在的行。当在SQL中比较null时，我们使用IS关键字，而不是=符号。如果我们想要选择列为空的行，我们可以这样写:
WHERE column_name IS NULL
如果我们想要选择列名不为空的行，我们使用:
WHERE column_name IS NOT NULL
我们使用左连接来查看城市表中不存在的国家。
SELECT
f.name country,
f.population
FROM facts f
LEFT JOIN cities c ON c.facts_id = f.id
WHERE c.name IS NULL;
显示部分
通过前面编写的查询结果，我们可以看到国家在cities表中没有相应值的许多不同原因:
- 人口少或没有主要城市地区的国家(定义为人口超过75万)，如圣马力诺、科索沃和瑙鲁。
- 非国家领土，如香港、直布罗陀和库克群岛。
- 非国家的区域和海洋，如欧盟和太平洋。
在使用内连接时，一定要注意可能会排除重要数据，特别是在基于数据库模式中没有链接的列进行连接时。
Right join和outer join
需要注意的是有两种不太常见的连接类型SQLite是不支持的。第一个是右连接。顾名思义，右连接与左连接完全相反。左连接包括join子句之前表中的所有行，而右连接则包括join子句表中的所有行。我们可以在下面的维恩图中看右连接:
a RIGHT JOIN b
下面两个查询，一个使用左连接，一个使用右连接，产生相同的结果。
SELECT f.name country, c.name city
FROM facts f
LEFT JOIN cities c ON c.facts_id = f.id
LIMIT 5;
SELECT f.name country, c.name city
FROM cities c
RIGHT JOIN facts f ON f.id = c.facts_id
LIMIT 5;
使用右连接的主要原因是要连接两个以上的表。在这些情况下，最好使用右连接，因为它可以避免为了连接一个表而重新构造整个查询。除此之外，右连接很少被使用，所以对于简单的连接，最好使用左连接而不是右连接，因为这样会更容易让其他人阅读和理解您的查询。
SQLite不支持的另一种连接类型是外连接。一个完整的外连接将包括连接两端的表中的所有行。我们可以在下面的维恩图中看到一个完整的外部连接:
a FULL OUTER JOIN b
与右连接一样，外连接相当少见。完整外连接的标准SQL语法是:
SELECT f.name country, c.name city
FROM cities c
FULL OUTER JOIN facts f ON f.id = c.facts_id
LIMIT 5;
当用外连接连接cities和facts表时，结果将与上面我们的左右连接相同，因为没有cities.Facts_id在facts.id中不存在。
让我们回顾一下每种连接类型的维恩图，这将帮助您对比目前为止所讨论的四种连接的不同之处。
