sql - 在 SQL 中使用外连接进行查询

Question

我有一个包含地理信息的 SQL 数据库。这个数据库有三个表:

PostalCode
----------
Code  (char(10))
StateID (uniqueidentifier)

State
-----
ID (uniqueidentifier)
Name (nvarchar(max))
CountryID (uniqueidentifier)

Country
-------
ID (uniqueidentifier)
Name

关系是:一个国家有多个州。各州有邮政编码。我正在尝试创建一个查询，我可以在其中找到特定邮政编码所属国家/地区的所有州。目前，我正在尝试以下操作:

SELECT 
  s.*
FROM 
  [PostalCode] p, 
  [State] s,
  [Country] c
WHERE
  p.[Zip]='90028' AND 
  p.[StateID]=s.[ID] AND 
  s.[CountryID]=c.[ID]

不幸的是，此结果返回 1 条记录(与加利福尼亚相关的州记录)。但是，实际上，我需要它返回 50 条记录(美国每个州一条)。如何修改此查询以执行此操作？

谢谢

Answer 1

您正在使用 INNER JOIN ，您需要将语法更改为 LEFT JOIN :

SELECT  s.*
FROM [State] s
LEFT JOIN [PostalCode] p
  ON p.[StateID]=s.[ID]
  AND p.[Zip]='90028'
LEFT JOIN [Country] c
  ON s.[CountryID]=c.[ID]

你会注意到我改用 ANSI JOIN语法而不是用逗号和 WHERE 连接表条款。

一个 LEFT JOIN将返回 state 中的所有行即使其他表中没有匹配的行。

如果要返回邮政编码等于特定代码的国家/地区的所有州，则可以使用:

select s.*
from state s
inner join
(
  SELECT s.countryid
  FROM [State] s
  INNER JOIN [PostalCode] p
    ON p.[StateID]=s.[ID]
  INNER JOIN [Country] c
    ON s.[CountryID]=c.[ID]
  WHERE p.[Zip]='90028'
) c
  on s.countryid = c.countryid;

或者你可以使用:

select s1.*
from state s1
where exists (select s2.countryid
              from state s2
              inner join country c
                on s2.countryid = c.id
              inner join postalcode p
                on s2.id = p.stateid
              where p.zip = 90028
               and s1.countryid = s2.countryid)