SQL show value即使WHERE子句不为真

Question

我有一个总共输出48行的SQL查询，然后我添加了一些表来连接I get one row作为响应，

下面是我的第一个查询，总共输出48行

      SELECT
        users.uid,
        users_roles.rid,
        users.mail,
        users.name,
        dependant.field_dependent_type_tid,
        player_name.field_member_name_value
      FROM
        users
        JOIN users_roles
        JOIN field_data_field_dependent_type AS dependant
        JOIN field_data_field_member_name AS player_name
      WHERE
        users.uid = users_roles.uid
        AND users_roles.rid = 13
        AND dependant.field_dependent_type_tid != 374
        AND dependant.entity_id = users.uid
        AND player_name.entity_id = users.uid

然后当我添加一个手机号码字段时，输出变成了1行

下面是我第二个查询，总共输出1行

    SELECT
      users.uid,
      users_roles.rid,
      users.mail,
      users.name,
      dependant.field_dependent_type_tid,
      player_name.field_member_name_value,
      mobile_number.field_mobile_number_value
    FROM
      users
      JOIN users_roles
      JOIN field_data_field_dependent_type AS dependant
      JOIN field_data_field_member_name AS player_name
      JOIN field_data_field_mobile_number AS mobile_number
    WHERE
      users.uid = users_roles.uid
      AND users_roles.rid = 13
      AND dependant.field_dependent_type_tid != 374
      AND dependant.entity_id = users.uid
      AND player_name.entity_id = users.uid
      AND mobile_number.entity_id = users.uid

正如您在我的查询中看到的，我添加了一个手机号码字段来显示uid等于mobile_number字段的entitiy_id的用户的手机号码，但是48个用户中只有1个注册了他们的手机号码。

我想要做的是显示所有这48行，其中包括具有手机号码的一行用户，

因此，输出将是47个用户的手机号码为空，一个用户的手机号码为空，我该如何在给定的查询示例中做到这一点？

Answer 1

将语法更改为使用左连接，并将连接条件与连接放在一起，而不是放在where子句中：

 SELECT
      users.uid,
      users_roles.rid,
      users.mail,
      users.name,
      dependant.field_dependent_type_tid,
      player_name.field_member_name_value,
      mobile_number.field_mobile_number_value
    FROM
      users
      left JOIN users_roles on users.uid = users_roles.uid AND users_roles.rid = 13
      left JOIN field_data_field_dependent_type AS dependant
   on dependant.field_dependent_type_tid != 374
     left  JOIN field_data_field_member_name AS player_name  on dependant.entity_id = users.uid
      left JOIN field_data_field_mobile_number AS mobile_number
 on player_name.entity_id = users.uid and mobile_number.entity_id = users.uid