Skip to Content

Can all subqueries be avoided in SQLScript?

As stated in the SQLScript best practices, nested queries makes code complex and hardly readable. They also state, that they should be split up into separate queries using variables instead without performance loss.

Now I'm wondering: are there cases where such a splitting up is not possible? E.g. due to some weird dependencies I can't imagine at the moment?

Or can one state that all nested queries can be resolved?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jan 17, 2018 at 12:46 PM

    co-related subqueries can typically not resolved into table variables.

    Just because a specific technique is mentioned as a “best practice” doesn’t mean that it’s the only option or always the best thing to do.

    Add comment
    10|10000 characters needed characters exceeded

    • Thanks. I had the feeling that there exist simple nested queries, that are not resolvable but no example came to my mind. The term "correlated" led me to such an example and I realized, that it is an obvious counter example ;-)

      SELECT employee_id, manager_id, first_name, last_name
        FROM employees a
          (SELECT employee_id
           FROM employees b
           WHERE b.manager_id = a.employee_id);

      These queries are obviously not resolvable that simple, since the subquery depends on an attribute of its superquery.