代码之家 › 专栏 › 技术社区 › Dylan

SQL即使WHERE子句不为true也显示值

oracle database sql mysql

Dylan · 技术社区 · 5 年前

我有一个SQL查询,它总共输出48行,然后添加一些表来连接“我只得到一行”作为响应,

这是我的第一个查询,共输出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

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

我要做的是显示所有48行,其中包括拥有移动电话号码的单行用户,

因此,输出将是一个空移动电话号码的47个用户和一个有移动电话号码的用户,我如何在给定的查询示例中做到这一点?

0 回复 | 直到 5 年前

Programnik 5 年前

将语法更改为使用左联接,并将联接条件与联接放在一起,而不是放在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