将一张表多次与其他表连接

Question

我有三个表：

用户表（userid username）

密钥表（userid keyid）

笔记本电脑表（userid laptopid）

我想获取所有拥有密钥或笔记本电脑，或者两者都有的用户。如何编写查询语句，使其在用户表和密钥表之间使用 join，在用户表和笔记本电脑表之间使用 join？

主要问题是，在实际场景中，有十二个左连接等类似的表连接，例如：

" select .. From a left join b on (...), c join d on (..),e,f,g where ..."，

我发现a可以与b连接，也可以与f连接。假设我无法让表a、b和f并排出现，那么我该如何编写SQL查询语句呢？

- umar

---

每个用户都必须有钥匙或笔记本电脑。对吧？ - user94893

你可能是对的，也可能是错的，但我完全不在意！不过还是挺有趣的 :) - umar

Q1：你只想知道用户（用户ID）还是他们具体拥有什么？ Q2：一个用户可以拥有多个类型的物品吗（例如2台笔记本电脑或3把钥匙）？ - van

Answer 1

您可以使用多个连接来组合多个表：

select *
from user u
left join key k on u.userid = k.userid
left join laptop l on l.userid = u.userid

“左连接”还会找到没有钥匙或笔记本电脑的用户。如果将两者都替换为“内连接”，它只会找到拥有钥匙和笔记本电脑的用户。

当“左连接”没有找到一行时，它会在其字段中返回NULL。因此，您可以像这样选择所有具有笔记本电脑或钥匙的用户：

select *
from user u
left join key k on u.userid = k.userid
left join laptop l on l.userid = u.userid
where k.userid is not null or l.userid is not null

NULL是特殊的，您需要像“字段不为null”而不是“字段<> null”这样进行比较。

在您的评论之后添加：假设您有一个与笔记本电脑相关但与用户无关的鼠标表。您可以这样连接：

select *
from user u
left join laptop l on l.userid = u.userid
left join mouse m on m.laptopid = l.laptopid

如果这个答案没有解决你的问题，你需要更明确地说明问题。

- Andomar

---

我认为最后的u.userid 应该是 k.userid。 - Matthew Flaschen

抱歉之前没有澄清问题：我已经添加了更多细节到问题中，所以你的答案似乎不能解决我的问题。 - umar

Answer 2

select distinct u.userid, u.username
from User u 
    left outer join Key     /* k on u.userid = k.userid */
    left outer join Laptop  /* l on u.userid = l.userid */
where k.userid is not null or l.userid is not null

编辑 "主要问题在于实际情况中，有12个或更多的表连接，例如：“select .. From a left join b on (...), c join d on (..),e,f,g where ...”，我看到a可以与b连接，也可以与f连接。那么假设我不能使表a、b和f并排出现，我该如何编写SQL查询语句？"

您可以根据需要进行多个左外连接。将具有主键的表与其他表连接，或者在任何其他字段上连接，其中一个表的字段值应该与另一个表的字段值匹配。

例如会比文字更好地解释。

select * 
from a
 left outer join b on a.pk = b.fk -- a pk should match b fk
 left outer join c on a.pk = c.fk -- a pk should match c fk
 left outer join d on c.pk = d.fk -- c pk should match d fk

等等其他

- Rashmi Pandit

---

我完成了这个问题，以展示我的主要困难在于无法使三个表并排显示，就像全表连接（我的意思是：“from c,d”等）混合出现在它们之间。 - umar

你在第一个回答中仍然缺少一个像“where k.userid is not null or l.userid is not null”这样的where子句来过滤掉没有任何内容的用户。 - Not Matt

Answer 3

-- // Assuming that a user can have at max 1 items of each type
SELECT      u.*
-- // Assuming that a user can have more then 1 items of each type, just add DISTINCT:
-- // SELECT      DISTINCT u.*
FROM        "User" u
LEFT JOIN   "Key"    u1 ON u.UserID = u1.UserID
LEFT JOIN   "Laptop" u2 ON u.UserID = u2.UserID
LEFT JOIN   "Server" u3 ON u.UserID = u3.UserID
-- // ...
WHERE       COALESCE(u1.UserID, u2.UserID, u3.UserID /*,...*/) IS NOT NULL

- van

Answer 4

根据您所描述的情况，您只想知道某人是否有笔记本电脑或钥匙。我会使用子查询而不是连接来编写查询：

select * 
from user 
where userid in (select userid from key union select userid from laptop)

这是因为通过连接，一个人拥有多台笔记本电脑或多个密钥将被列出多次（除非您使用distinct）。即使您使用distinct，您最终也会得到一个效率较低的查询（至少在Oracle上，查询优化器似乎无法创建有效的计划）。

【编辑以更正Rashmi Pandit指出的内容。】

- waxwing

---

如果您指定左外连接，它将不会是一个交叉连接。左外连接比子查询更快。 - Rashmi Pandit

我在Oracle数据库中快速尝试了一下：你是对的，它不会导致交叉连接。但它也不比子查询方法更快 - 实际上慢了两倍以上。 - waxwing

我想对查询做非常少的修改，除了添加更多的JOIN和ON子句之外，因此现在我正在寻找一种不使用子查询的方法来实现它。另外，我有一个MySQL数据库，其行为可能与Oracle在嵌套查询上的行为不同。 - umar

我不确定Oracle，但我通常听说连接比子查询更快...也许适用于大量数据。 - Rashmi Pandit

这非常有帮助，作为一个 SQL 新手，它完全满足了我的需求，并且易于阅读。 - Nick

Answer 5

解决方案一：

SELECT * FROM [User] u
INNER JOIN [Key] k
ON u.userid = k.userid

UNION

SELECT * FROM [User] u
INNER JOIN Laptop l
ON u.userid = l.userid

[...]

解决方案二：

SELECT * FROM [User] u
LEFT JOIN [Key] k
ON u.userid = k.userid
LEFT JOIN Laptop l
ON u.userid = l.userid
LEFT JOIN [...]
WHERE k.userid IS NOT NULL
OR l.userid IS NOT NULL
OR [...]

只是一个猜测，你也可以检查这两个的执行计划，看看UNION的比另一个更重还是反之。

- maxbeaudoin

Answer 6

 SELECT * 
 FROM User
 LEFT JOIN Key ON User.id = Key.user_id
 LEFT JOIN Laptop ON User.id = Laptop.user_id
 WHERE Key.id IS NOT NULL OR Laptop.id IS NOT NULL

- user94893