Search code examples
sqlquery-optimizationanalysis

Finding unused join in a SQL query

I am currently maintaining a significant number of SQL queries. Some of them are created by copy/paste operations, then removing unnecessary fields and sometimes forgetting to remove the tables where these fields come from.

I am looking for a tool (or anything apart from eyes+brain) that, given a SQL query, would analyze which of the joined tables have no field selected in the SELECT part.

Do you know of such a tool?

Thank you

Solution

  • Hypothetically a tool could exist but it would only be guaranteed to be correct if all the following criteria where met for said join

    • Its A LEFT or OUTER JOIN or an INNER JOIN where the cardinality where known to be 1-1 And...
    • Its Not Referenced in a SELECT, HAVING, GROUP BY or WHERE and...
    • It is not a JOIN to function that has a side effect...

    Probably why there's no deterministic warnings in SQL parsers the way there is for let's say an unused variable in C#. But it might be worth while to create a SQL checker that looks for some of these conditions and lets the user know that there's a possibility for optimization here.