On Thu, Jul 25, 2013 at 4:42 PM, Carsten Fuchs <carsten.fu...@cafu.de> wrote: > Hi Tom, > > thank you very much for your reply! > > Am 25.07.2013 12:17, schrieb Tom Evans: > >> Isn't this to be expected? You've asked Django to OR the querysets. >> This means that you are looking for tuples from STAFF where either the >> join+conditions to ERFASST match or the join+conditions to >> STAFF_BEREICHE match. If these joins were performed with an INNER >> JOIN, then the query would only include tuples from STAFF that match >> both joins and conditions. > > > Well, I must foreclose that I'm by no means an SQL expert, and rather > consider myself as a beginner -- but won't the joins augment the STAFF table > independently of each other? > > That is, if I understand things correctly, the join types INNER vs. LEFT > OUTER are mostly about the handling of NULL values on the "other" side of > the relation, but as only non-NULL values are involved here, I'd expect a > query to provide the same augmented table no matter if INNER or LEFT OUTER > joins are used.
This query is all about joining tuples from the STAFF table to the other two tables. If the joins to both ERFASST and STAFF_BEREICHE are inner joins, then the only tuples from STAFF under consideration are ones which have valid references to both tables. This is not the same as OR'ing two independent queries. Now, if your data obeys that criteria, then there would be no difference between the two results, but the meaning of the queries are very different. With inner joins, the query means "Find me staff that have department 1, AND have logs* in the specified date range from department 1", but with outer joins the query means "Find me staff that have either department 1, or have logs from the specified date range from department 1". Put another way, if you AND'ed the Q objects instead, your query would be the fast one you are looking for with inner joins throughout, but you would not see users who do not have log entries, nor users with log entries not in departments - but those conditions sound like an impossible situation given your models. As an example you can play around with, pick one user who has log entries, and remove all their departments from STAFF_BEREICHE. This user should then be found with the slow query with LEFT OUTER joins, but not with the fast query with INNER joins - they are excluded due to the INNER join to STAFF_BEREICHE fails for that tuple in STAFF, and so it is not considered from that point onwards. Cheers Tom * Erfasst is meaning logging here, right? Reaching the limits of my German :) -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To post to this group, send email to django-users@googlegroups.com. Visit this group at http://groups.google.com/group/django-users. For more options, visit https://groups.google.com/groups/opt_out.
