有没有比使用 WHERE ... IN（子查询）更好的方法来编写此 SQL？

Question

有没有比使用 WHERE ... IN（子查询）更好的方法来编写此 SQL？

  SELECT device.mac, reseller.name, agent.name
  FROM device
  LEFT JOIN global_user
    ON device.global_user_id = global_user.id
  LEFT JOIN agent
    ON global_user.id = agent.global_user_id
  LEFT JOIN reseller
    ON global_user.id = reseller.global_user_id
        OR agent.reseller_id = reseller.id
  WHERE device.global_user_id IN (
    SELECT global_user_id
        FROM reseller
        WHERE id = '200'
  ) OR device.global_user_id IN (
    SELECT global_user_id
        FROM agent
        WHERE reseller_id = '200'
  );

我试图获取特定经销商下所有设备的列表，以及一些经销商/代理商的详细信息。这包括直接分配给经销商的设备和分配给经销商旗下代理商的设备。 reseller.id 是唯一的。它将在 postgresql 数据库上执行。

设备分配给代理商和经销商。代理商被分配给经销商。

这个查询有效，但我不经常在 JOIN 中使用 OR，并且我通常会尝试避免子查询。这个查询概念会经常使用，所以我想确保我没有忽略一些东西。

感谢您的任何反馈。

Answer 1

您可以尝试一下：

 SELECT device.mac, reseller.name, agent.name
  FROM device
  JOIN
  (
      SELECT global_user_id
      FROM reseller
      WHERE id = '200'
      UNION
      SELECT global_user_id
      FROM agent
      WHERE reseller_id = '200'
  ) r ON device.global_user_id = r.global_user_id
  LEFT JOIN global_user
    ON device.global_user_id = global_user.id
  LEFT JOIN agent
    ON global_user.id = agent.global_user_id
  LEFT JOIN reseller
    ON global_user.id = reseller.global_user_id
        OR agent.reseller_id = reseller.id

说明：
尝试查询的不同变体总是一个好主意，以确保最终获得性能最佳的查询（尽管通常，不同的变体会导致查询优化器生成相同的执行计划）。从 SQL Server 的角度来看，查询处理的顺序意味着 JOIN 在 WHERE 子句之前先处理。因此从理论上讲，这种 JOIN 方法应该更早地缩小结果集。

Answer 2

这个怎么样？

SELECT d.mac, r.name, a.name
FROM device as d, global_user as g, agent as a, reseller as r
WHERE d.global_user_id = g.id
  AND g.id = a.global_user_id
  AND (g.id = r.global_user_id OR a.reseller_id = r.id)
  AND (r.id = '200' OR a.reseller_id = '200');

Answer 3

如果子查询和 IN 子句很容易替换，我会尽量避免使用它们。如果我正确理解您的数据库模型，则此查询应该产生相同的结果：

SELECT      DISTINCT
            device.mac, reseller.name, agent.name
FROM        device
LEFT JOIN   global_user
        ON  device.global_user_id = global_user.id
LEFT JOIN   agent
        ON  global_user.id = agent.global_user_id
LEFT JOIN   reseller
        ON  global_user.id = reseller.global_user_id
        OR  agent.reseller_id = reseller.id
WHERE       reseller.id = '200'
        OR  agent.reseller_id = '200'