Comment by chuliomartinez | Hacker News Reader

Comment by chuliomartinez | Hacker News Reader

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_joi...

I 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;

Creative 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.

Your own article points out that exists handles the first case. Exists is not actually implemented as a subquery, it is merely syntactically a subquery.

Sure exists make sense if you dont need columns from the child table. Exists is also far from basic sql.

Reply on news.ycombinator.com