Is there a better way to write this SQL than using WHERE ... IN (subquery)?
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 usual开发者_Python百科ly 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.
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.
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');
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'
精彩评论