Django: What's the difference between Queryset.union() and the OR operator?

Question

When combining QuerySets, what's the difference between the QuerySet.union() method and using the OR operator between QuerySets |?

Consider the following 2 QuerySets:

qs1 = Candidate.objects.filter(id=1)
qs2 = Candidate.objects.filter(id=2)

How is qs1 | qs2 different from qs1.union(qs2)? Is there some subtlety under the hood that I'm missing?

Answer 1

From the QuerySet API reference

union(), intersection(), and difference() return model instances of the type of the first QuerySet even if the arguments are QuerySets of other models.

The .union() Method return QuerySet with schema/column Names of only the first QuerySet Parameter Passed. Where as this is not the case with OR(|) Operator.

Answer 2

From the QuerySet API reference:

The UNION operator selects only distinct values by default. To allow duplicate values, use the all=True argument.

The .union() method allows some granularity in specifying whether to keep or eliminate duplicate records returned. This choice is not available with the OR operator.

Also, QuerySets created by a .union() call cannot have .distinct() called on them.

Answer 3

This is more a SQL question than a Django question. In the example you post, the Django ORM will translate qs1 | qs2 as something along the lines of

SELECT * FROM candidate WHERE id=1 OR id=2

whereas in qs1.union(qs2) it will be something like

SELECT * FROM candidate WHERE id=1 UNION SELECT * FROM candidate WHERE id=2

In this particular example there will be no difference, however I don't believe anyone would write it with UNION.

If you have an expensive query, there will be a difference in the timing when you choose one format over the other. You can use EXPLAIN to experiment. In some tests I make UNION takes way longer to give you the first row, but finishes a bit faster.

If query optimization is not an issue, it is more common to use OR.