SQL 将一张桌子多次加入其他桌子

joining one table multiple times to other tables

提问by umar

I have three tables:

我有三张表：

Table User( userid username)

表用户（用户名用户名）

Table Key( userid keyid)

表键（用户 ID 键 ID）

Table Laptop( userid laptopid)

台式笔记本电脑（用户 ID 笔记本电脑 ID）

i want all users who have either a key or a laptop, or both. How do i write the query so that it uses a join between table User and table Key, as well as a join between table User and table Laptop?

我想要所有拥有钥匙或笔记本电脑，或两者兼而有之的用户。我如何编写查询，以便它使用表 User 和表 Key 之间的连接，以及表 User 和表 Laptop 之间的连接？

The main problem is that in the actual scenario, there are twelve or so table joins, sth like:

主要的问题是，在实际场景中，有十二个左右的表连接，比如：

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

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

and i see that a could be joined to b, and a could also be joined to f. So assuming i can't make the tables a,b, and f appear side-by-side, how do i write the sql query?

我看到a可以连接到b，a也可以连接到f。所以假设我不能让表 a、b 和 f 并排出现，我该如何编写 sql 查询？

回答by Andomar

You can use multiple joins to combine multiple tables:

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

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

A "left join" also finds users which do not have a key or a laptop. If you replace both with "inner join", it would find only users with a laptop and a key.

“左连接”还可以查找没有钥匙或笔记本电脑的用户。如果您将两者都替换为“内部连接”，它将仅找到具有笔记本电脑和密钥的用户。

When a "left join" does not find a row, it will return NULL in its fields. So you can select all users that have either a laptop or a key like this:

当“左连接”找不到行时，它将在其字段中返回 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 is special, in that you compare it like "field is not null" instead of "field <> null".

NULL 是特殊的，因为您将其比较为“字段不为空”而不是“字段 <> 空”。

Added after your comment: say you have a table Mouse, that is related to Laptop, but not to User. You can join that like:

在您的评论后添加：假设您有一个与笔记本电脑相关的桌面鼠标，但与用户无关。你可以像这样加入：

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

If this does not answer your question, you gotta clarify it some more.

如果这不能回答你的问题，你必须再澄清一下。

回答by van

-- // 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

回答by maxbeaudoin

Solution one:

解决方案一：

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

[...]

Solution two:

解决方案二：

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 [...]

Just a guess, also you could check the execution plan for theses two to see if the UNION one is heavier or vice versa.

只是一个猜测，您也可以检查这两个的执行计划，看看 UNION 是否更重，反之亦然。

回答by SO User

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

EDIT"The main problem is that in the actual scenario, there are twelve or so table joins, sth like: " select .. From a left join b on (...), c join d on (..),e,f,g where ...", and i see that a could be joined to b, and a could also be joined to f. So assuming i can't make the tables a,b, and f appear side-by-side, how do i write the sql query?"

编辑“主要问题是在实际场景中，有十二个左右的表连接，例如：” select .. from a left join b on (...), c join d on (..),e, f,g where ...”，我看到 a 可以连接到 b，a 也可以连接到 f。所以假设我不能让表 a、b 和 f 并排出现，我如何编写sql查询？”

You can have as many left outer joins as required. Join the table with the primary key to the rest of the tables or on any other field where field values of one table should match field values of other table.

您可以根据需要拥有尽可能多的左外部联接。将带有主键的表连接到其余表或任何其他字段，其中一个表的字段值应与其他表的字段值匹配。

eg will explain better than words

例如会比文字更好地解释

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

and so on

等等

回答by waxwing

As you described the case, you only wanted to know if someone has a laptop or a key. I would write the query with a subquery rather than a join:

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

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

The reason for this is that by a join a person with multiple laptops or multiple keys will be listed several times (unless you use distinct). And even you use distinctyou end up with a less efficient query (at least on Oracle the query optimizer doesn't appear to be able to create an efficient plan).

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

[Edited to correct what Rashmi Pandit pointed out.]

[编辑以更正 Rashmi Pandit 指出的内容。]

回答by Soul_Master

 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