mysql - 什么样的 Join 可以看到 Join 表中不存在的值？

Question

我有表 users、locations 和一个连接表。连接表还具有该位置每个个体的等级值，因为这些个体在某些位置的等级高于其他位置。

users
=========
id | name
=========
 1 | john
 2 | bob
 3 | alex

locations
===============
id | name
===============
 1 | san diego
 2 | dallas
 3 | new york
 4 | denver

join_users_locations
==============================
user_id | location_id | rank
==============================
      2 |           1 | 4
      2 |           2 | 3
      2 |           4 | 2
      3 |           1 | 2
      3 |           2 | 4

您可以看到，在连接表中，仅当用户在该位置具有排名时才会列出该用户。如果他们的排名为零/空，他们甚至不在表中。

我想得到这样的查询结果:

name |  location | rank 
=======================
 bob | san diego | 4
 bob |    dallas | 3
 bob |  new york | 0
 bob |    denver | 2

如您所见，我希望所有位置都在列表中，即使是用户未加入的位置(只需将这些字段的排名值为零)。

很容易得到位置列表和他/她加入的位置的排名:

SELECT
    u.name,
FROM users u
LEFT JOIN join_users_locations jul ON jul.user_id = u.id
LEFT JOIN locations l ON l.id = jul.location_id
WHERE u.id = 2

但这只列出了用户加入的位置。我也想查看未连接的位置。

我知道这是可能的，但我不确定解决方案是什么。我尝试了一些不同类型的 JOIN，结果各不相同。

SQL FIDDLE HERE

Answer 1

您可以使用 CROSS JOIN 来做到这一点从用户到位置。

SELECT
    u.name,
    l.name AS location,
    IFNULL(jul.rank, 0) AS rank
FROM
    users u CROSS JOIN
    locations l LEFT JOIN
    join_users_locations jul 
        ON jul.user_id = u.ID 
        AND jul.location_id = l.id
WHERE u.id = 2