4 ms·

Correlated subqueries are absolutely fine and are equivalent to (possibly lateral) joins. They aren't as easy to identify and optimize as such, so I wouldn't ne

by nimish 3y ago

Correlated subqueries are absolutely fine and are equivalent to (possibly lateral) joins. They aren't as easy to identify and optimize as such, so I wouldn't necessarily recommend them, but sometimes they make sense. Not an issue with a good query planner and if they clarify a query.

da_chicken 3y ago
Yeah, correlated subqueries are fine. Unless you're on MySQL, which (historically, at least) was infamously terrible at executing them. The reason this query works is because correlated subqueries... need something to correlate with! Usually the column from the outside query is only referenced in the WHERE clause, but there isn't really a reason you can't reference it in the inside query's SELECT clause. After all, what if you needed it for a CASE expression there? There are certainly better ways to write what this query is accomplishing -- at the very least there are ways to write it that make it much more clear what your intentions are -- but just because it's weird doesn't mean it's invalid. The developers know they cannot anticipate all possible operations that might be desired, and there are already countless ways to write queries incorrectly.
HDThoreaun 3y ago
As an employee at a database company working on the plan optimizer I would like to ask you not to use correlated subqueries for my own sanity. Close to 100% of the mind blowing bugs are correlated subqueries. Kidding of course but holy shit my life would be so much easier without them.
- nimish 3y ago
  I am curious as to why. They should be represented like joins, right?
  - HDThoreaun 3y ago
    They’re slow as fuck and most of them can be rewritten to not be correlated, so we attempt to heavily rewrite them. There’s basically an entire subfield of cs research that focuses on strategies for rewriting CSQs and the papers have gotten progressively more and more complicated making them very difficult to reproduce without bugs. I’ve spent easily weeks just reading these papers let alone implementing them.
    - nimish 3y ago
      I'd love to read a deep dive on this. Database implementation internals are always very interesting.

by nimish 3y ago

da_chicken 3y ago

Yeah, correlated subqueries are fine. Unless you're on MySQL, which (historically, at least) was infamously terrible at executing them. The reason this query works is because correlated subqueries... need something to correlate with! Usually the column from the outside query is only referenced in the WHERE clause, but there isn't really a reason you can't reference it in the inside query's SELECT clause. After all, what if you needed it for a CASE expression there? There are certainly better ways to write what this query is accomplishing -- at the very least there are ways to write it that make it much more clear what your intentions are -- but just because it's weird doesn't mean it's invalid. The developers know they cannot anticipate all possible operations that might be desired, and there are already countless ways to write queries incorrectly.

HDThoreaun 3y ago

As an employee at a database company working on the plan optimizer I would like to ask you not to use correlated subqueries for my own sanity. Close to 100% of the mind blowing bugs are correlated subqueries. Kidding of course but holy shit my life would be so much easier without them.

nimish 3y ago
I am curious as to why. They should be represented like joins, right?
- HDThoreaun 3y ago
  They’re slow as fuck and most of them can be rewritten to not be correlated, so we attempt to heavily rewrite them. There’s basically an entire subfield of cs research that focuses on strategies for rewriting CSQs and the papers have gotten progressively more and more complicated making them very difficult to reproduce without bugs. I’ve spent easily weeks just reading these papers let alone implementing them.
  - nimish 3y ago
    I'd love to read a deep dive on this. Database implementation internals are always very interesting.