在SQL查询中联接4个表

Question

在我的数据库中有4个关系表，想加入它们以获取总价值…例如，我有朋友，家人，关注者和熟人，并希望加入这些以获得“ ALL”值。

表格格式如下：

朋友们

id follower following
---------------------
1     2      3
2     4      5

家庭

id follower following
---------------------
1     5      6
2     7      8

下列的

id follower following
---------------------
1     9      10
2     11     12

熟人

id follower following
---------------------
1     13     14
2     15     16

是联接所有4个表的正确查询…

SELECT following
FROM   friends
       INNER JOIN family ON friends.following=family.following
       INNER JOIN following ON friends.following=following.following
       INNER JOIN acquaintances ON friends.following=acquaintances.following
WHERE  follower='id'

基本上，我想从其中id =我的id的所有四个表中检索“ following”（跟随）值

Answer 1

如果 所有表 都具有与Friends表的链接，则当前查询仅列出结果。我相信您正在寻找类似这样的东西

SELECT following
FROM   friends
WHERE  follower='id'
UNION ALL 
SELECT following
FROM   family 
WHERE  follower='id'
UNION ALL 
SELECT following
FROM   following
WHERE  follower='id'
UNION ALL 
SELECT following
FROM   acquaintances 
WHERE  follower='id'

或更好地阅读并且更容易以一些性能为代价进行调整

SELECT following 
FROM   (
        SELECT following, follower FROM friends
        UNION ALL SELECT following, follower FROM family 
        UNION ALL SELECT following, follower FROM following
        UNION ALL SELECT following, follower FROM acquaintances 
       ) AS f
WHERE  follower='id'

联盟

UNION用于将来自多个SELECT语句的结果合并为一个结果集。