4 ms·

I teach SQL to financial people and, for the most part, it isn't too bad. When I get to joins, I teach a subset of SQL where all joins are done through INNER J

I teach SQL to financial people and, for the most part, it isn't too bad. When I get to joins, I teach a subset of SQL where all joins are done through INNER JOIN / LEFT JOIN syntax and that helps a lot. Joins are the part that everyone has trouble with.

setr 8y ago
Tbh I usually see teachers worried about joins but its the group by/having clauses that I always watched people struggle with (at least, writing it). Ofc that might just be the emphasis on joins makes group by the next lowest hanging fruit
- deleted 8y ago
  [deleted]
- ranit 8y ago
  What do your abbreviations mean?
  - grzm 8y ago
    tbh = to be honest, ofc = of course Urban Dictionary (https://www.urbandictionary.com https://www.urbandictionary.com) or even just typing something like "what does tbh mean?" into your preferred search engine will often help you find likely interpretations of unknown abbreviations, in my experience.
    - justinator 8y ago
      > or even just typing something like "what does tbh mean?" into your preferred search engine will often help you find likely interpretations I mean, that's what they did.
Exuma 8y ago
Can you explain when to really use a lateral join?
- garyclarke27 8y ago
  In Postgresql, Lateral Joins enable function chaining for set returning functions. ie you can feed the results of earlier srf function to the arguments of following function. This gets around one of Postgresql most glaring omissions - table valued parameters. Is incredibly useful once you get used to them.
  - andreareina 8y ago
    I might be misunderstanding your point, but Postgresql functions can take (and return) tables: CREATE FUNCTION fullname(person) RETURNS TEXT AS $$ SELECT $1.firstname || ' ' || $1.lastname $$ LANGUAGE SQL Could you go into a more detail wrt using lateral joins with set returning functions? I'm used to using them as a way to "extend" the row with later terms depending on prior terms as you say, but I'm not comprehending the srf case so well (or tbh the results that pop up when I search for "posrgresql set returning function").
- netcraft 8y ago
  In my experience it’s relatively rare that you need them. I think of them as the for-each of joins. It evaluates row by row, not a single time. Another analogy would be a correlated subquery.
asavinov 8y ago
> Joins are the part that everyone has trouble with. There is significant problem-solution mismatch in joins and some other SQL constructs which are therefore semantically quite controversial in many use cases: https://github.com/asavinov/bistro#why-column-orientation https://github.com/asavinov/bistro#why-column-orientation
HeavyStorm 8y ago
The syntax isn't the problem though. Join relational operation is though on itself.

setr 8y ago

Tbh I usually see teachers worried about joins but its the group by/having clauses that I always watched people struggle with (at least, writing it). Ofc that might just be the emphasis on joins makes group by the next lowest hanging fruit

deleted 8y ago
[deleted]
ranit 8y ago
What do your abbreviations mean?
- grzm 8y ago
  tbh = to be honest, ofc = of course Urban Dictionary (https://www.urbandictionary.com https://www.urbandictionary.com) or even just typing something like "what does tbh mean?" into your preferred search engine will often help you find likely interpretations of unknown abbreviations, in my experience.
  - justinator 8y ago
    > or even just typing something like "what does tbh mean?" into your preferred search engine will often help you find likely interpretations I mean, that's what they did.

Exuma 8y ago

Can you explain when to really use a lateral join?

garyclarke27 8y ago
In Postgresql, Lateral Joins enable function chaining for set returning functions. ie you can feed the results of earlier srf function to the arguments of following function. This gets around one of Postgresql most glaring omissions - table valued parameters. Is incredibly useful once you get used to them.
- andreareina 8y ago
  I might be misunderstanding your point, but Postgresql functions can take (and return) tables: CREATE FUNCTION fullname(person) RETURNS TEXT AS $$ SELECT $1.firstname || ' ' || $1.lastname $$ LANGUAGE SQL Could you go into a more detail wrt using lateral joins with set returning functions? I'm used to using them as a way to "extend" the row with later terms depending on prior terms as you say, but I'm not comprehending the srf case so well (or tbh the results that pop up when I search for "posrgresql set returning function").
netcraft 8y ago
In my experience it’s relatively rare that you need them. I think of them as the for-each of joins. It evaluates row by row, not a single time. Another analogy would be a correlated subquery.

asavinov 8y ago

> Joins are the part that everyone has trouble with. There is significant problem-solution mismatch in joins and some other SQL constructs which are therefore semantically quite controversial in many use cases: https://github.com/asavinov/bistro#why-column-orientation https://github.com/asavinov/bistro#why-column-orientation

HeavyStorm 8y ago

The syntax isn't the problem though. Join relational operation is though on itself.