Can all subqueries be avoided in SQLScript?

[patricksteffens]

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?

[lbreddemann · Accepted solution]

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.

[patricksteffens · Accepted solution]

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
  WHERE NOT EXISTS
    (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.