3 ms·
SQL makes it very hard to express real world requirements. 1. No easy way to limit child records count in joins - find all orders with orderproduct.amount is g
by chuliomartinez 1y ago
SQL makes it very hard to express real world requirements.
1. No easy way to limit child records count in joins - find all orders with orderproduct.amount is greater than X. Obviously this will genereate duplicates for orders that have more than one such orderproduct. So you slap a distinct on it… but what if you need an aggregation?
The possible fixes are highly non-trivial: subqueries, window functions, or vendor specific: outer apply.
2. Or queries, that is when you group where conditions with OR are very hard (impossible) to optimize.
Apart from the trivial case where the conditions are all on the same column, you are better of leaving the declarative world and imperatively tell sql to do a union.
I wrote a bit about it here: https://www.inuko.net/blog/platform_sql_or_conditions_on_joins/ https://www.inuko.net/blog/platform_sql_or_conditions_on_joi...
- dfe 1y agoYour own article points out that exists handles the first case. Exists is not actually implemented as a subquery, it is merely syntactically a subquery.
- chuliomartinez 1y agoSure exists make sense if you dont need columns from the child table. Exists is also far from basic sql.
- procaryote 1y agoI don't really understand the problem in 1 in 2, looking at your article, from your first query it looks like person_relationship contains both (A,B) and (B,A) for all related people A and B; otherwise the left join won't work. If you also make people related to themselves and store (A,A) and (B,B) there your query becomes much simpler: SELECT other.id, other.name FROM person p JOIN person_relationship r ON r.from_person_id = p.id JOIN person other ON r.to_person_id = other.id WHERE p.family_id = @familyId;
- chuliomartinez 1y agoCreative solution! Of course this creates more maintenance to ensure such records exist. I guess you kinda proved my point, you have to work around it.