4 ms·

Curious, what is the difference here?

deleted 7y ago
[deleted]
gtsteve 7y ago
I believe they may have intended to write this query with INNER JOINs instead of LEFT JOINs. If so, the result of the query would be identical but the second one would likely have performance problems given the category_id filter is not being applied at the point where bar is joined. A clever database engine might find an optimisation but I wouldn't count on it. This illustrates the subtle problem that ORMs can introduce - all logical tests pass but issues emerge over time. I found that typically every ORM based problem was resolved by rewriting it using SQL. I used to use ORMs extensively, but my philosophy now is that you can make simple queries by using a query generator (i.e. connection.GetByID("MyTable", 100)) and complex queries have to be written by hand. I would not choose to use an ORM again.
- btilly 7y ago
  No, left joins were intended here. They mean different things. With inner joins the semantics are the same. If the performance is different, the database query optimizer is broken.
  - gtsteve 7y ago
    I see, apologies. However, I'm reasonably certain we'd see poor performance with that query on MySQL 5.7, but I don't think it's got as good an optimiser as Postgres for example.
    - btilly 7y ago
      I can't say that MySQL doesn't perform poorly. But if so, then their optimizer is more broken than I though. Even SQLite figures this one out and does it right.
btilly 7y ago
The difference is whether the condition on bar is a filter is on the join from bar or baz.
saltcured 7y ago
The extra "AND expr" in the join clause makes it more strict, which means the outer join can produce more nulls in the joined tuple. In a sequence like A left join B left join C, moving the extra join filter between the two joins is the difference between getting tuples like (a..., null..., null...) vs (a..., b..., null...).
magicalhippo 7y ago
In the first case, if for a given row bar.category_id <> 5 then there's no match and hence null will be returned for any value from bar. Thus there will be no match in baz either (assuming the id columns are non-null). In the second case, the first join will always return a row from bar for a given foo_id if it exists, but the second join does not return values from baz for rows where bar.category_id <> 5.

deleted 7y ago

[deleted]

I believe they may have intended to write this query with INNER JOINs instead of LEFT JOINs. If so, the result of the query would be identical but the second one would likely have performance problems given the category_id filter is not being applied at the point where bar is joined. A clever database engine might find an optimisation but I wouldn't count on it. This illustrates the subtle problem that ORMs can introduce - all logical tests pass but issues emerge over time. I found that typically every ORM based problem was resolved by rewriting it using SQL. I used to use ORMs extensively, but my philosophy now is that you can make simple queries by using a query generator (i.e. connection.GetByID("MyTable", 100)) and complex queries have to be written by hand. I would not choose to use an ORM again.

btilly 7y ago
No, left joins were intended here. They mean different things. With inner joins the semantics are the same. If the performance is different, the database query optimizer is broken.
- gtsteve 7y ago
  I see, apologies. However, I'm reasonably certain we'd see poor performance with that query on MySQL 5.7, but I don't think it's got as good an optimiser as Postgres for example.
  - btilly 7y ago
    I can't say that MySQL doesn't perform poorly. But if so, then their optimizer is more broken than I though. Even SQLite figures this one out and does it right.

btilly 7y ago

The difference is whether the condition on bar is a filter is on the join from bar or baz.

saltcured 7y ago

The extra "AND expr" in the join clause makes it more strict, which means the outer join can produce more nulls in the joined tuple. In a sequence like A left join B left join C, moving the extra join filter between the two joins is the difference between getting tuples like (a..., null..., null...) vs (a..., b..., null...).

magicalhippo 7y ago

In the first case, if for a given row bar.category_id <> 5 then there's no match and hence null will be returned for any value from bar. Thus there will be no match in baz either (assuming the id columns are non-null). In the second case, the first join will always return a row from bar for a given foo_id if it exists, but the second join does not return values from baz for rows where bar.category_id <> 5.