【SQL】一个经典挑战，左连接时字段为null

下面这个查询，claims 表中 uuid 都有值，但是查询结果中 u.uuid 有时候有值，有时候是null。

SELECT q.id, u.uuid, q.created_at, q.name, q.phone_number, q.province_city, q.address, q.base64, u.role, u.avatar_base64 FROM "claims" q
LEFT JOIN "msm_users" u ON q.msm_user_uuid = u.uuid
WHERE q.status < 3

解释如下：

This is a classic behavior of a LEFT JOIN (or LEFT OUTER JOIN).

Here’s why you’re seeing NULL values for u.uuid (and all other columns from the msm_users table) sometimes:

1. LEFT JOIN Explained:

   • A LEFT JOIN returns all rows from the “left” table (which is "claims" aliased as q in your query).
   • It then tries to find matching rows in the “right” table (which is "msm_users" aliased as u) based on the ON condition (q.msm_user_uuid = u.uuid).
   • If a match is found: The columns from msm_users (like u.uuid, u.role, u.avatar_base64) will contain the data from the matching user.
   • If NO match is found: The LEFT JOIN still includes the row from the claims table, but for all columns that come from the msm_users table, it will put NULL values.

2. Why a match might not be found:

   • q.msm_user_uuid is NULL: If the msm_user_uuid column in a specific claims record is NULL, then q.msm_user_uuid = u.uuid can never be true (as NULL doesn’t equal anything, not even another NULL in standard SQL comparison), so no user will match.
   • q.msm_user_uuid has a value, but it doesn’t exist in msm_users.uuid: This indicates a data integrity issue.
     • Perhaps a user was deleted from msm_users, but their uuid still remains in some claims records.
     • Perhaps there was a typo or data entry error when populating q.msm_user_uuid.
     • Perhaps q.msm_user_uuid is a foreign key, but it’s not enforced, allowing “orphan” claims records.

In summary: When u.uuid is NULL, it means that for that particular claims record, there was no corresponding msm_users record where msm_users.uuid matched claims.msm_user_uuid.

What you can do to investigate:

1. Check for NULL foreign keys:

   SELECT COUNT(*) FROM "claims" WHERE msm_user_uuid IS NULL;

   If this count is greater than 0, these are the claims that will definitely result in NULL for u.uuid.

2. Check for non-existent foreign keys:

   SELECT DISTINCT q.msm_user_uuid
   FROM "claims" q
   LEFT JOIN "msm_users" u ON q.msm_user_uuid = u.uuid
   WHERE u.uuid IS NULL AND q.msm_user_uuid IS NOT NULL;

   This query will show you msm_user_uuid values from the claims table that are notNULL, but still don’t have a matching user in the msm_users table. These are your “orphan” foreign keys.

If you always expect a user to exist and want to exclude claims without a user:

You should use an INNER JOIN instead of a LEFT JOIN:

SELECT q.id, u.uuid, q.created_at, q.name, q.phone_number, q.province_city, q.address, q.base64, u.role, u.avatar_base64
FROM "claims" q
INNER JOIN "msm_users" u ON q.msm_user_uuid = u.uuid
WHERE q.status < 3;

This query will only return rows where there is a successful match between claims.msm_user_uuid and msm_users.uuid. Claims that don’t have a matching user will be excluded from the result entirely.