Is there a better way to write this SQL than using WHERE ... IN (subquery)?

Question

is there a better way to write this SQL than using WHERE ... IN (subquery)?

  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'
  );

im trying to get a list of all of the devices, with some reseller/agent details, under a particular reseller. this would include devices assigned directly to the reseller and devices assigned to agents under the reseller. reseller.id is unique. it will be executed on postgresql database.

devices are assigned to both agents and resellers. agents are assigned to resellers.

this query works, but i haven't often used an OR in a JOIN and i usually try to avoid subqueries. this query concept will be used often, so i'd like to make sure i haven't overlooked something.

thank you for any feedback.

Answer 1

You could give this a whirl:

 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

Clarification: Always a good idea to try out different variations of a query to make sure you end up with the best performing query (although often, different variations result in the same execution plan being generated by the query optimiser). Speaking from SQL Server point of view, the order in which the query is processed in means the JOINs are processed first before the WHERE clause. So in theory, this JOIN approach should scale the resultset down earlier.

Answer 2

How about this?

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

I try to avoid sub-queries and IN clause if it is easy to replace them. If I understood you DB model correctly, this query should produce the same result:

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'