6 ms·

Joins In Steps

75 points by zindlerb 7y ago

revskill 7y ago
Does SQL Join allow equality with operators like ilike, like,... or custom equality function ? select * from a inner join b on mycustomfunction(a, b);
- hobs 7y ago
  Generally any equality is fine, but you'd usually do something like on mycustomfunction(a) = mycustomfuntion(b) And if you do this, it will be incredibly slow because you cant use any indexes in this case.
  - sk1pper 7y ago
    You can create indexes on expressions, like mycustomfunction(a), in Postgres at least.
  - mhotchen 7y ago
    Good news! Many database systems support indexes on functions, including on user-defined functions. The only one I've used is Postgres[0] but apparently MySQL added support recently as well[1] [0] https://www.postgresql.org/docs/current/indexes-expressional.html https://www.postgresql.org/docs/current/indexes-expressional... [1] https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts https://dev.mysql.com/doc/refman/8.0/en/create-index.html#cr...
    - TeMPOraL 7y ago
      SQLite has it too (caveats, like always, apply): https://www.sqlite.org/expridx.html https://www.sqlite.org/expridx.html.
    - hobs 7y ago
      That's cool, but then I'd assume you have to make an index for each function. In general if a person is asking basic questions about sargability, I will steer them away from functions on a predicate (it's usually my last choice.) Often you can just think a bit harder about your set based logic and make an index that's reusable by other queries. In other SQL's you can also make a materialized view, which may be able to persist some of these things, its just like keeping n+1 copies of your data to satisfy a different set of relations/projections.
p2t2p 7y ago
I think about my self as quite SQL-savvy person, I used to optimize quite complex queries and is able to read plans for Oracle, Postgres and MySQL. And yet, I've got not idea why would anybody need right join. Have you guys ever had a case when you'd need a right join? I've been to the field for 15 years and yet to see people using right join in the wild. Like the last example in this link - why would you do that? Most probably your business logic focuses on dogs, something like "find dogs with no owner" or something, In this case it is much more readable and straight forward to go with left join or even with sub-select where you'd have something like 'select * from dogs where owner_id not in (select id from owners)'. Have you used right joins and if you have can you explain the use case?
- saulrh 7y ago
  I think that I've almost used a right join before, but not for anything like a legitimate use case. I was attempting to out-think an in-house DB's optimizer that was doing a hash join in the wrong direction. There may have been some rough spots in some of that DB's specialized SQL extensions that played into the problem. I think I ended up using a full outer join plastered with optimizer hints. Like you said, if you're using a right join, probably not unlikely that you're doing something wrong.
- gav 7y ago
  I struggle to think of time I used a right join in the 20-odd years I've used SQL. The example shows a case where you'd want foreign keys to enforce referential integrity, though FKs aren't fashionable these days. You shouldn't be able to have an entry for owner_id 8 in the Dogs table without a corresponding Owner.
- tempguy9999 7y ago
  A right join is a left join flipped so you never formally need one, like you said. I believe I used one once, exactly once ever, about a year ago. That was because I was fighting an almighty 5 page CTE charlie fox of a query and had to turn an inner join into an outer join to get data otherwise excluded. It just so happened the data missing was on the right so I made it a right join. Had it not been so awful I'd have flipped it into a left join, my usual style, but I couldn't bear rewriting it. (background: it was complex but could not be broken down or it lost efficiency. Part of the length was long explicit lists of fields 'select a, b, c, d, ...' which were unavoidable, but it was just plain complex too. The inner join suddenly became an outer join because we found data missing - the DB was a commercial one written by some clueless people with little consistency and absolutely no documentation. I hope I never see that again).
rtpg 7y ago
I really feel like we need a lower level expression language than SQL, one based on actual query plans rather than a declarative thing. Having that, and helping people learn that, would make it much harder to not “get” how joins and the like end up working
- contradictioned 7y ago
  Would you also force users to decide on the implementation of the join operator? Because that is one of the benefits of a declarative query, as well as placement of other operators and join ordering if multiple relations are involved.
  - nothrabannosir 7y ago
    Would that come with the benefit of not getting nasty surprises when the cantankerous scheduler suddenly decides to mess with a previously efficient solution? Or even protect me from myself in the future accidentally breaking an efficient query through an ostensibly innocent change? I would appreciate a way to encode execution efficiency parameters in the semantics of the query itself. Specifying which indexes to use, or the runtime complexity of certain operations. This would also help me figure out which indexes I need to create, in the first place. Today I can add a clause in an order which makes using an index impossible, rendering a previously efficient query suddenly extremely expensive. The failure mode is very obscure: it all works, the DB just works overtime. I might not have a large enough DB yet to actually notice this, until one day I see these slow queries and have to backtrack all the way to that one commit that caused it, six months ago. I have encountered this scenario a few odd times. It is not always obvious to someone changing a query that it is meant to use an index, and it is very hard for someone writing it to specify so in a unit test. Flexibility for the scheduler to choose efficient strategies cuts both ways. I'd appreciate the ability to leave more explicit annotations about expected runtime characteristics, than mere comments offer.
    - tempguy9999 7y ago
      > I would appreciate a way to encode execution efficiency parameters in the semantics of the query itself. Specifying which indexes to use, That's a mistake. You can in MSSQL but you'd likely regret it (this from experience). Using an index can be a mistake; sometimes a table scan can be more efficient. And which, full scan or index, is more efficient is down to the data in the table and the query predicate. (Edit, so which to do, scan or index, can be different for exactly the same query depending on the test: select * from people where surname = ? can be either depending on whether surname is 'smith' (vey common) or 'hepsibah' (very rare) ). > I'd appreciate the ability to leave more explicit annotations about expected runtime characteristics, than mere comments offer. Interesting. Can you elaborate?
obituary_latte 7y ago
That’s a typo in the last paragraph, no? >The left right is the table named in the outer join part of the query.
- zindlerb 7y ago
  Thanks for pointing that out. It has been updated.

75 points by zindlerb 7y ago

Does SQL Join allow equality with operators like ilike, like,... or custom equality function ? select * from a inner join b on mycustomfunction(a, b);

hobs 7y ago
Generally any equality is fine, but you'd usually do something like on mycustomfunction(a) = mycustomfuntion(b) And if you do this, it will be incredibly slow because you cant use any indexes in this case.
- sk1pper 7y ago
  You can create indexes on expressions, like mycustomfunction(a), in Postgres at least.
- mhotchen 7y ago
  Good news! Many database systems support indexes on functions, including on user-defined functions. The only one I've used is Postgres[0] but apparently MySQL added support recently as well[1] [0] https://www.postgresql.org/docs/current/indexes-expressional.html https://www.postgresql.org/docs/current/indexes-expressional... [1] https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-parts https://dev.mysql.com/doc/refman/8.0/en/create-index.html#cr...
  - TeMPOraL 7y ago
    SQLite has it too (caveats, like always, apply): https://www.sqlite.org/expridx.html https://www.sqlite.org/expridx.html.
  - hobs 7y ago
    That's cool, but then I'd assume you have to make an index for each function. In general if a person is asking basic questions about sargability, I will steer them away from functions on a predicate (it's usually my last choice.) Often you can just think a bit harder about your set based logic and make an index that's reusable by other queries. In other SQL's you can also make a materialized view, which may be able to persist some of these things, its just like keeping n+1 copies of your data to satisfy a different set of relations/projections.

p2t2p 7y ago

I think about my self as quite SQL-savvy person, I used to optimize quite complex queries and is able to read plans for Oracle, Postgres and MySQL. And yet, I've got not idea why would anybody need right join. Have you guys ever had a case when you'd need a right join? I've been to the field for 15 years and yet to see people using right join in the wild. Like the last example in this link - why would you do that? Most probably your business logic focuses on dogs, something like "find dogs with no owner" or something, In this case it is much more readable and straight forward to go with left join or even with sub-select where you'd have something like 'select * from dogs where owner_id not in (select id from owners)'. Have you used right joins and if you have can you explain the use case?

saulrh 7y ago
I think that I've almost used a right join before, but not for anything like a legitimate use case. I was attempting to out-think an in-house DB's optimizer that was doing a hash join in the wrong direction. There may have been some rough spots in some of that DB's specialized SQL extensions that played into the problem. I think I ended up using a full outer join plastered with optimizer hints. Like you said, if you're using a right join, probably not unlikely that you're doing something wrong.
gav 7y ago
I struggle to think of time I used a right join in the 20-odd years I've used SQL. The example shows a case where you'd want foreign keys to enforce referential integrity, though FKs aren't fashionable these days. You shouldn't be able to have an entry for owner_id 8 in the Dogs table without a corresponding Owner.
tempguy9999 7y ago
A right join is a left join flipped so you never formally need one, like you said. I believe I used one once, exactly once ever, about a year ago. That was because I was fighting an almighty 5 page CTE charlie fox of a query and had to turn an inner join into an outer join to get data otherwise excluded. It just so happened the data missing was on the right so I made it a right join. Had it not been so awful I'd have flipped it into a left join, my usual style, but I couldn't bear rewriting it. (background: it was complex but could not be broken down or it lost efficiency. Part of the length was long explicit lists of fields 'select a, b, c, d, ...' which were unavoidable, but it was just plain complex too. The inner join suddenly became an outer join because we found data missing - the DB was a commercial one written by some clueless people with little consistency and absolutely no documentation. I hope I never see that again).

rtpg 7y ago

I really feel like we need a lower level expression language than SQL, one based on actual query plans rather than a declarative thing. Having that, and helping people learn that, would make it much harder to not “get” how joins and the like end up working

contradictioned 7y ago
Would you also force users to decide on the implementation of the join operator? Because that is one of the benefits of a declarative query, as well as placement of other operators and join ordering if multiple relations are involved.
- nothrabannosir 7y ago
  Would that come with the benefit of not getting nasty surprises when the cantankerous scheduler suddenly decides to mess with a previously efficient solution? Or even protect me from myself in the future accidentally breaking an efficient query through an ostensibly innocent change? I would appreciate a way to encode execution efficiency parameters in the semantics of the query itself. Specifying which indexes to use, or the runtime complexity of certain operations. This would also help me figure out which indexes I need to create, in the first place. Today I can add a clause in an order which makes using an index impossible, rendering a previously efficient query suddenly extremely expensive. The failure mode is very obscure: it all works, the DB just works overtime. I might not have a large enough DB yet to actually notice this, until one day I see these slow queries and have to backtrack all the way to that one commit that caused it, six months ago. I have encountered this scenario a few odd times. It is not always obvious to someone changing a query that it is meant to use an index, and it is very hard for someone writing it to specify so in a unit test. Flexibility for the scheduler to choose efficient strategies cuts both ways. I'd appreciate the ability to leave more explicit annotations about expected runtime characteristics, than mere comments offer.
  - tempguy9999 7y ago
    > I would appreciate a way to encode execution efficiency parameters in the semantics of the query itself. Specifying which indexes to use, That's a mistake. You can in MSSQL but you'd likely regret it (this from experience). Using an index can be a mistake; sometimes a table scan can be more efficient. And which, full scan or index, is more efficient is down to the data in the table and the query predicate. (Edit, so which to do, scan or index, can be different for exactly the same query depending on the test: select * from people where surname = ? can be either depending on whether surname is 'smith' (vey common) or 'hepsibah' (very rare) ). > I'd appreciate the ability to leave more explicit annotations about expected runtime characteristics, than mere comments offer. Interesting. Can you elaborate?

obituary_latte 7y ago

That’s a typo in the last paragraph, no? >The left right is the table named in the outer join part of the query.

zindlerb 7y ago
Thanks for pointing that out. It has been updated.