1

Given this query,

SELECT a.*
FROM a
LEFT JOIN b ON b.a_id = a.id
LEFT JOIN c ON c.a_id = a.id
HAVING COUNT(DISTINCT b.id) < COUNT(DISTINCT c.id)

if b is a significantly larger table than c, will there be performance implications to the order I do the joins? I read somewhere that the rule of thumb is to join smallest to largest, but that might've been referring to INNER JOINs.

I realize that the results will depend on the order of outer joins in many scenarios, but in one like the above where the order doesn't matter for veracity, will it likely affect performance?

1
  • 1
    Which database product and version are you using? Please tag your question accordingly.
    – Paul White
    Jul 31, 2015 at 19:42

1 Answer 1

3

SQL is declarative language, query is not executed the way you write it . Most modern RDMS have decent optimizer which is capable of choosing correct join order when relatively small number of tables is involved , statistics is up to date, data not skewed, appropriate indexes exist, etc.

Query optimization is a very broad topic, and it's quite specific to particular vendor. It may involve adding hints, creating materialized views, using fixed execution plans, caching tables in memory, and many other things. To some extent you may control join order with the way you write query (for instance, subquery inside WITH will be more likely executed first compared to the same subquery inside inline view, but again, it's very specific to RDMS you're using.

As I rule of thumb, I'd recommend starting with a query that gives you expected results, and try optimizing it only if it's slow - the optimizer will do the rest.

Side note: COUNT can appear in HAVING clause, not in WHERE ; the query will fail on the most RDMS.

1
  • I mistakenly used WHERE in my simplified example, but the real-life query uses HAVING. I've edited the question; thanks for pointing it out.
    – ivan
    Aug 1, 2015 at 0:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.