Skip to Content
0

Can all subqueries be avoided in SQLScript?

Jan 17 at 12:03 PM

45

avatar image

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?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Lars Breddemann
Jan 17 at 12:46 PM
0

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.

Show 1 Share
10 |10000 characters needed characters left 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
  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.

0