3 ms·

Please add to this otherwise excellent list: LATERAL JOINs (PGSQL). In SQL Server & Oracle they are CROSS APPLY & OUTER APPLY. This will change your SQL habit

by ak39 6y ago

Please add to this otherwise excellent list: LATERAL JOINs (PGSQL). In SQL Server & Oracle they are CROSS APPLY & OUTER APPLY. This will change your SQL habits forever. Lateral joins give you the power of iteration in set-based operations that are mind-numbingly easy to implement and understand.

rrrrrrrrrrrryan 6y ago
I think this is really only helpful in terms of readability if you come from a world where iteration is the norm, and really hurts readability for most database folks where set-based operations are the norm.
- sbuttgereit 6y ago
  Long time database person here (~20yrs), probably very much akin to the "Application DBA" in the article. The solve some classes of performance related issues, especially if you're joining to certain kinds of group-by sub-queries or need to join on the output of a function. If your surrounding query is defining the constraints, you can push that into otherwise unconstrained sub-queries. There was a minor learning curve, it took me a couple hours for it to be worked into my default mindset. In the end, wonderful technique that can save dropping into procedural code in any number of cases. The biggest danger is that it is a kind of iteration and where the performance can be greatly enhanced in say OLTP style queries, but it may not be a net gain in larger unconstrained queries that will cause that sub-query or function to get hit many times... I think getting that into your head is the larger part and the declarative nature of SQL will cause that to be a little outside of your vision no matter your background.
- ravoori 6y ago
  Not really - this fine SO answer has many examples of CROSS/OUTER APPLY elegantly solving problems that would otherwise have been inconvenient to deal with - https://stackoverflow.com/a/9275865/753731 https://stackoverflow.com/a/9275865/753731
_bohm 6y ago
This is life-changing. Thank you so much.
bradleyankrom 6y ago
LATERAL JOINs are also in Snowflake, agree that they are the jam.

by ak39 6y ago

rrrrrrrrrrrryan 6y ago

I think this is really only helpful in terms of readability if you come from a world where iteration is the norm, and really hurts readability for most database folks where set-based operations are the norm.

sbuttgereit 6y ago
Long time database person here (~20yrs), probably very much akin to the "Application DBA" in the article. The solve some classes of performance related issues, especially if you're joining to certain kinds of group-by sub-queries or need to join on the output of a function. If your surrounding query is defining the constraints, you can push that into otherwise unconstrained sub-queries. There was a minor learning curve, it took me a couple hours for it to be worked into my default mindset. In the end, wonderful technique that can save dropping into procedural code in any number of cases. The biggest danger is that it is a kind of iteration and where the performance can be greatly enhanced in say OLTP style queries, but it may not be a net gain in larger unconstrained queries that will cause that sub-query or function to get hit many times... I think getting that into your head is the larger part and the declarative nature of SQL will cause that to be a little outside of your vision no matter your background.
ravoori 6y ago
Not really - this fine SO answer has many examples of CROSS/OUTER APPLY elegantly solving problems that would otherwise have been inconvenient to deal with - https://stackoverflow.com/a/9275865/753731 https://stackoverflow.com/a/9275865/753731

_bohm 6y ago

This is life-changing. Thank you so much.

bradleyankrom 6y ago

LATERAL JOINs are also in Snowflake, agree that they are the jam.